MySQL中的信息架構 (Information Schema)

一、Information Schema是什麼？

MySQL中有一個叫做Information Schema的資料庫，它包含了關於MySQL server實例、資料庫、表、列、索引、用戶以及許可權等信息的元數據。Information Schema不是一個真正的資料庫，而是一個MySQL server在啟動時自動創建的資料庫視圖。它僅包含一些元數據，且這些元數據是只讀的，用戶不能在Information Schema資料庫中插入、更新或刪除任何信息，而是只能查詢。Information Schema可以幫助我們為MySQL的管理和維護提供更加直觀和精細的視圖和信息樹。

二、Information Schema有哪些基本的表

Information Schema資料庫中有多達30多個表，而這些表分別包含了不同方面的關於MySQL server實例、資料庫、表、列、索引、用戶、許可權以及伺服器狀態等信息的元數據。下面是Information Schema中的一些基本表：

• CHARACTER_SETS : 包含了mysql server所支持的字符集信息。
• COLUMNS: 包含了所有表中的欄位信息。
• EVENTS: 包含了系統中所有事件調度器的信息。
• KEY_COLUMN_USAGE: 指明了關鍵字列及它們被哪些主鍵或外鍵使用。
• PROCESSLIST: 顯示了當前正在執行的server線程信息。
• VIEWS: 包含了所有視圖的信息。

三、Information Schema的實際應用

了解Information Schema可以幫助我們更好的管理MySQL資料庫。通過查詢Information Schema，我們可以輕鬆獲得一些重要的元數據信息，如：

• 檢索所有MySQL資料庫中的表名或視圖名及其結構、約束、索引等屬性信息。 例如：SELECT * FROM information_schema.tables WHERE table_schema =’dbName’;
• 查詢表結構中的約束信息，如主鍵、外鍵、唯一性約束等。例如：SELECT * FROM information_schema.table_constraints WHERE table_schema=’dbName’ AND table_name=’tableName’;
• 查詢特定列的信息，如列名、列數據類型、是否為NULL、默認值以及注釋。例如：SELECT column_name, data_type, is_nullable, column_default, column_comment FROM information_schema.columns WHERE table_schema=’dbName’ AND table_name=’tableName’;
• 獲得MySQL中所有用戶和用戶的許可權信息。例如：SELECT * FROM mysql.user WHERE User=’username’;
• 檢查InnoDB的表空間、緩衝池和鎖等的使用情況。例如：SELECT * FROM information_schema.INNODB_BUFFER_PAGE L WHERE L.TABLE_NAME = `tableName`;

四、示例代碼

下面是幾個使用Information Schema查詢表結構和列信息的示例代碼：

-- 查詢特定表的所有列名和數據類型
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable';

-- 查詢特定表中某些列的信息
SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable' AND COLUMN_NAME IN ('col1', 'col2', 'col3');

-- 查詢特定資料庫中的所有表名和表類型
SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'myDatabase';

-- 查詢特定資料庫中的所有表
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'myDatabase' AND TABLE_TYPE = 'BASE TABLE';

五、總結

Information Schema是一個非常重要而貴重的工具，它幫助我們更好地管理和維護MySQL資料庫。通過查詢Information Schema，我們可以獲得非常有用的元數據信息，包括MySQL實例、表、列、索引、用戶以及許可權等方面的信息。如果充分利用Information Schema，我們可以優化MySQL的性能，提高我們的工作效率。