information_schema
是 MySQL 中的一個系統資料庫,它提供了關於資料庫後設資料的資訊。後設資料是指描述資料庫結構和內容的資料,例如表、列、索引、使用者許可權等。information_schema
資料庫是隻讀的,使用者不能對其進行修改。
位置
information_schema
資料庫存在於每個 MySQL 例項中,不需要單獨建立。當你連線到 MySQL 伺服器時,可以透過以下方式訪問 information_schema
資料庫:
USE information_schema;
或者在查詢中直接指定 information_schema
資料庫:
SELECT * FROM information_schema.tables;
主要表
information_schema
資料庫包含多個表,每個表都提供了不同型別的資訊。以下是一些常用的表及其描述:
-
TABLES
- 描述:包含所有表的資訊。
- 常用欄位:
TABLE_CATALOG
:表所屬的目錄(通常是def
)。TABLE_SCHEMA
:表所屬的資料庫名稱。TABLE_NAME
:表的名稱。TABLE_TYPE
:表的型別(如BASE TABLE
或VIEW
)。ENGINE
:表的儲存引擎(如InnoDB
、MyISAM
等)。TABLE_ROWS
:表中的行數(近似值)。AUTO_INCREMENT
:自動遞增欄位的下一個值。
-
COLUMNS
- 描述:包含所有列的資訊。
- 常用欄位:
TABLE_CATALOG
:列所屬的目錄(通常是def
)。TABLE_SCHEMA
:列所屬的資料庫名稱。TABLE_NAME
:列所屬的表名稱。COLUMN_NAME
:列的名稱。ORDINAL_POSITION
:列在表中的位置。COLUMN_DEFAULT
:列的預設值。IS_NULLABLE
:列是否允許為空。DATA_TYPE
:列的資料型別。CHARACTER_MAXIMUM_LENGTH
:字元列的最大長度。NUMERIC_PRECISION
:數值列的精度。NUMERIC_SCALE
:數值列的小數位數。
-
SCHEMATA
- 描述:包含所有資料庫的資訊。
- 常用欄位:
CATALOG_NAME
:資料庫所屬的目錄(通常是def
)。SCHEMA_NAME
:資料庫的名稱。DEFAULT_CHARACTER_SET_NAME
:資料庫的預設字符集。DEFAULT_COLLATION_NAME
:資料庫的預設排序規則。
-
STATISTICS
- 描述:包含所有索引的資訊。
- 常用欄位:
TABLE_CATALOG
:索引所屬的目錄(通常是def
)。TABLE_SCHEMA
:索引所屬的資料庫名稱。TABLE_NAME
:索引所屬的表名稱。INDEX_NAME
:索引的名稱。NON_UNIQUE
:索引是否允許重複值。SEQ_IN_INDEX
:索引欄位在索引中的位置。COLUMN_NAME
:索引欄位的名稱。CARDINALITY
:索引的基數(近似值)。SUB_PART
:索引欄位的字首長度(如果有的話)。
-
ROUTINES
- 描述:包含所有儲存過程和函式的資訊。
- 常用欄位:
SPECIFIC_NAME
:儲存過程或函式的名稱。ROUTINE_CATALOG
:儲存過程或函式所屬的目錄(通常是def
)。ROUTINE_SCHEMA
:儲存過程或函式所屬的資料庫名稱。ROUTINE_NAME
:儲存過程或函式的名稱。ROUTINE_TYPE
:儲存過程或函式的型別(如PROCEDURE
或FUNCTION
)。DATA_TYPE
:返回值的資料型別(僅適用於函式)。IS_DETERMINISTIC
:儲存過程或函式是否是確定性的。SQL_DATA_ACCESS
:儲存過程或函式對資料的訪問級別。
-
VIEWS
- 描述:包含所有檢視的資訊。
- 常用欄位:
TABLE_CATALOG
:檢視所屬的目錄(通常是def
)。TABLE_SCHEMA
:檢視所屬的資料庫名稱。TABLE_NAME
:檢視的名稱。VIEW_DEFINITION
:檢視的定義SQL語句。CHECK_OPTION
:檢視的檢查選項(如NONE
、LOCAL
、CASCADED
)。IS_UPDATABLE
:檢視是否可更新。
-
USER_PRIVILEGES
- 描述:包含所有使用者的全域性許可權資訊。
- 常用欄位:
GRANTEE
:使用者的名稱和主機。TABLE_CATALOG
:許可權所屬的目錄(通常是def
)。PRIVILEGE_TYPE
:許可權型別(如SELECT
、INSERT
、UPDATE
等)。IS_GRANTABLE
:是否可以授予他人。
-
SCHEMA_PRIVILEGES
- 描述:包含所有資料庫的許可權資訊。
- 常用欄位:
GRANTEE
:使用者的名稱和主機。TABLE_CATALOG
:許可權所屬的目錄(通常是def
)。TABLE_SCHEMA
:資料庫的名稱。PRIVILEGE_TYPE
:許可權型別(如SELECT
、INSERT
、UPDATE
等)。IS_GRANTABLE
:是否可以授予他人。
-
TABLE_PRIVILEGES
- 描述:包含所有表的許可權資訊。
- 常用欄位:
GRANTEE
:使用者的名稱和主機。TABLE_CATALOG
:許可權所屬的目錄(通常是def
)。TABLE_SCHEMA
:資料庫的名稱。TABLE_NAME
:表的名稱。PRIVILEGE_TYPE
:許可權型別(如SELECT
、INSERT
、UPDATE
等)。IS_GRANTABLE
:是否可以授予他人。
-
COLUMN_PRIVILEGES
- 描述:包含所有列的許可權資訊。
- 常用欄位:
GRANTEE
:使用者的名稱和主機。TABLE_CATALOG
:許可權所屬的目錄(通常是def
)。TABLE_SCHEMA
:資料庫的名稱。TABLE_NAME
:表的名稱。COLUMN_NAME
:列的名稱。PRIVILEGE_TYPE
:許可權型別(如SELECT
、INSERT
、UPDATE
等)。IS_GRANTABLE
:是否可以授予他人。
示例查詢
以下是一些常見的查詢示例,展示瞭如何使用 information_schema
資料庫獲取後設資料資訊:
-
查詢所有資料庫:
SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;
-
查詢特定資料庫中的所有表:
SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name';
-
查詢特定表的所有列:
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
-
查詢特定表的所有索引:
SELECT INDEX_NAME, NON_UNIQUE, COLUMN_NAME, SEQ_IN_INDEX FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
-
查詢特定使用者的全域性許可權:
SELECT PRIVILEGE_TYPE, IS_GRANTABLE FROM information_schema.USER_PRIVILEGES WHERE GRANTEE = '''username''@''host''';
透過 information_schema
資料庫,你可以輕鬆地獲取和管理資料庫的後設資料資訊,這對於資料庫管理和最佳化非常有用。