mysql的 information_schema 資料庫介紹

卡米i發表於2024-11-06

information_schema 是 MySQL 中的一個系統資料庫,它提供了關於資料庫後設資料的資訊。後設資料是指描述資料庫結構和內容的資料,例如表、列、索引、使用者許可權等。information_schema 資料庫是隻讀的,使用者不能對其進行修改。

位置

information_schema 資料庫存在於每個 MySQL 例項中,不需要單獨建立。當你連線到 MySQL 伺服器時,可以透過以下方式訪問 information_schema 資料庫:

USE information_schema;

或者在查詢中直接指定 information_schema 資料庫:

SELECT * FROM information_schema.tables;

主要表

information_schema 資料庫包含多個表,每個表都提供了不同型別的資訊。以下是一些常用的表及其描述:

  1. TABLES

    • 描述:包含所有表的資訊。
    • 常用欄位:
      • TABLE_CATALOG:表所屬的目錄(通常是 def)。
      • TABLE_SCHEMA:表所屬的資料庫名稱。
      • TABLE_NAME:表的名稱。
      • TABLE_TYPE:表的型別(如 BASE TABLEVIEW)。
      • ENGINE:表的儲存引擎(如 InnoDBMyISAM 等)。
      • TABLE_ROWS:表中的行數(近似值)。
      • AUTO_INCREMENT:自動遞增欄位的下一個值。
  2. 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:數值列的小數位數。
  3. SCHEMATA

    • 描述:包含所有資料庫的資訊。
    • 常用欄位:
      • CATALOG_NAME:資料庫所屬的目錄(通常是 def)。
      • SCHEMA_NAME:資料庫的名稱。
      • DEFAULT_CHARACTER_SET_NAME:資料庫的預設字符集。
      • DEFAULT_COLLATION_NAME:資料庫的預設排序規則。
  4. STATISTICS

    • 描述:包含所有索引的資訊。
    • 常用欄位:
      • TABLE_CATALOG:索引所屬的目錄(通常是 def)。
      • TABLE_SCHEMA:索引所屬的資料庫名稱。
      • TABLE_NAME:索引所屬的表名稱。
      • INDEX_NAME:索引的名稱。
      • NON_UNIQUE:索引是否允許重複值。
      • SEQ_IN_INDEX:索引欄位在索引中的位置。
      • COLUMN_NAME:索引欄位的名稱。
      • CARDINALITY:索引的基數(近似值)。
      • SUB_PART:索引欄位的字首長度(如果有的話)。
  5. ROUTINES

    • 描述:包含所有儲存過程和函式的資訊。
    • 常用欄位:
      • SPECIFIC_NAME:儲存過程或函式的名稱。
      • ROUTINE_CATALOG:儲存過程或函式所屬的目錄(通常是 def)。
      • ROUTINE_SCHEMA:儲存過程或函式所屬的資料庫名稱。
      • ROUTINE_NAME:儲存過程或函式的名稱。
      • ROUTINE_TYPE:儲存過程或函式的型別(如 PROCEDUREFUNCTION)。
      • DATA_TYPE:返回值的資料型別(僅適用於函式)。
      • IS_DETERMINISTIC:儲存過程或函式是否是確定性的。
      • SQL_DATA_ACCESS:儲存過程或函式對資料的訪問級別。
  6. VIEWS

    • 描述:包含所有檢視的資訊。
    • 常用欄位:
      • TABLE_CATALOG:檢視所屬的目錄(通常是 def)。
      • TABLE_SCHEMA:檢視所屬的資料庫名稱。
      • TABLE_NAME:檢視的名稱。
      • VIEW_DEFINITION:檢視的定義SQL語句。
      • CHECK_OPTION:檢視的檢查選項(如 NONELOCALCASCADED)。
      • IS_UPDATABLE:檢視是否可更新。
  7. USER_PRIVILEGES

    • 描述:包含所有使用者的全域性許可權資訊。
    • 常用欄位:
      • GRANTEE:使用者的名稱和主機。
      • TABLE_CATALOG:許可權所屬的目錄(通常是 def)。
      • PRIVILEGE_TYPE:許可權型別(如 SELECTINSERTUPDATE 等)。
      • IS_GRANTABLE:是否可以授予他人。
  8. SCHEMA_PRIVILEGES

    • 描述:包含所有資料庫的許可權資訊。
    • 常用欄位:
      • GRANTEE:使用者的名稱和主機。
      • TABLE_CATALOG:許可權所屬的目錄(通常是 def)。
      • TABLE_SCHEMA:資料庫的名稱。
      • PRIVILEGE_TYPE:許可權型別(如 SELECTINSERTUPDATE 等)。
      • IS_GRANTABLE:是否可以授予他人。
  9. TABLE_PRIVILEGES

    • 描述:包含所有表的許可權資訊。
    • 常用欄位:
      • GRANTEE:使用者的名稱和主機。
      • TABLE_CATALOG:許可權所屬的目錄(通常是 def)。
      • TABLE_SCHEMA:資料庫的名稱。
      • TABLE_NAME:表的名稱。
      • PRIVILEGE_TYPE:許可權型別(如 SELECTINSERTUPDATE 等)。
      • IS_GRANTABLE:是否可以授予他人。
  10. COLUMN_PRIVILEGES

    • 描述:包含所有列的許可權資訊。
    • 常用欄位:
      • GRANTEE:使用者的名稱和主機。
      • TABLE_CATALOG:許可權所屬的目錄(通常是 def)。
      • TABLE_SCHEMA:資料庫的名稱。
      • TABLE_NAME:表的名稱。
      • COLUMN_NAME:列的名稱。
      • PRIVILEGE_TYPE:許可權型別(如 SELECTINSERTUPDATE 等)。
      • IS_GRANTABLE:是否可以授予他人。

示例查詢

以下是一些常見的查詢示例,展示瞭如何使用 information_schema 資料庫獲取後設資料資訊:

  1. 查詢所有資料庫:

    SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;
  2. 查詢特定資料庫中的所有表:

    SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name';
  3. 查詢特定表的所有列:

    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';
  4. 查詢特定表的所有索引:

    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';
  5. 查詢特定使用者的全域性許可權:

    SELECT PRIVILEGE_TYPE, IS_GRANTABLE 
    FROM information_schema.USER_PRIVILEGES 
    WHERE GRANTEE = '''username''@''host''';

透過 information_schema 資料庫,你可以輕鬆地獲取和管理資料庫的後設資料資訊,這對於資料庫管理和最佳化非常有用。

相關文章