資料庫的基本資訊，都在這幾張表裡了

小白碼上飛發表於2022-03-16
資料庫

話說生產環境的資料庫是不能本地直連的，所以公司一般都會提供一個比較簡陋的資料庫查詢頁面，在可控的範圍內，支援你提交一些查詢、變更SQL，滿足你的查庫功能。但是因為不能直接使用Navicat、DataGrip這些好用的資料庫客戶端，一些資料庫的基本資訊獲取起來就比較麻煩了。比如你想看線上業務表都有哪些索引？是不是和測試環境不一致？這種情況應該怎麼辦呢？

其實好辦！因為資料庫的各種基礎資訊，都儲存在INFORMATION_SCHEMA這個schema下了。INFORMATION_SCHEMA是MySQL自帶的資料庫，儲存了MySQL中各個資料庫的後設資料。所以，只需要從INFORMATION_SCHEMA下的各個表裡取資料，就可以獲取到資料庫的基本資訊了

1、庫資訊

庫資訊存放在SCHEMATA表中，使用以下語句，即可查詢全部的庫資訊。

select *
from INFORMATION_SCHEMA.SCHEMATA;

當然，你也可以用對應的show databases命令來獲取庫資訊，但是顯示的內容會更簡潔些，只會展示資料庫的名稱。

2、表資訊

表資訊存放在TABLES表中，使用以下語句，即可查詢全部的表資訊。包括表名、資料量、自增值、行數等資訊。

select *
from INFORMATION_SCHEMA.TABLES;

但是這麼查詢，會查出所有schema下的表資訊，所以加上查詢條件TABLE_SCHEMA = 'yourSchemaName'就可以了。

當然，這個表的查詢也有對應的語句show tables from yourSchemaName。只不過這個語句也只會展示當前庫下所有的表名，而且遠沒有直接從TABLES表裡查詢的資料詳細。

3、列資訊

列資訊存放在COLUMNS表中，使用以下語句，即可查詢指定表的列資訊。包括列名、資料型別、長度、是否為空等你建立表時的基本資訊。

select *
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'yourTableName';

對應的語句是show columns from yourTableName;

4、索引資訊

索引資訊存放在STATISTICS表中（很奇怪，為啥這個表不叫INDEX），使用以下語句，即可查詢指定表的索引資訊。包含了索引名、索引的欄位等。

select *
from INFORMATION_SCHEMA.STATISTICS
where table_name = 'yourTableName';

對應的命令是show index from yourTableName;這個命令還是比較給力的，相比於前幾個命令，它列出了很詳細的索引資訊。

優化後的SQL，建議收藏

好啦，其實INFORMATION_SCHEMA下的表還有很多，但是我認為平時最常用到的就是這四張表了。大家應該都已經知道了這四張表存放的資訊和查詢方式，這裡我優化了查詢SQL，讓大家可以在查詢的時候更直觀~

-- 查表
select TABLE_NAME                 as 表名,
       ENGINE                     as 儲存引擎,
       TABLE_ROWS                 as 行數,
       AVG_ROW_LENGTH             as 平均行大小,
       DATA_LENGTH / 1024 / 1024  as 表資料大小（MB）,
       INDEX_LENGTH / 1024 / 1024 as 索引大小（MB）,
       AUTO_INCREMENT             as 當前主鍵自增值,
       TABLE_COMMENT              as 表描述
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'yourSchemaName';

-- 查列
SELECT COLUMN_NAME              列名,
       COLUMN_TYPE              資料型別,
       DATA_TYPE                欄位型別,
       CHARACTER_MAXIMUM_LENGTH 長度,
       IS_NULLABLE              是否為空,
       COLUMN_DEFAULT           預設值,
       COLUMN_COMMENT           備註
FROM INFORMATION_SCHEMA.COLUMNS
where table_name = 'yourTableName';

-- 查索引
select INDEX_NAME   as 索引名,
       COLUMN_NAME  as 索引中的欄位名,
       SEQ_IN_INDEX as 索引中的順序,
       INDEX_TYPE   as 索引型別
from INFORMATION_SCHEMA.STATISTICS
where table_name = 'yourTableName';

相關文章