MySQL入門--後設資料

1. MySQL 後設資料

1.1. 後設資料訪問方法

資料庫是資料的結構化集合。後設資料是“有關資料的資料”。 MySQL 透過以下方法提供對後設資料的訪問：

INFORMATION_SCHEMA ： MySQL 伺服器包含一個名為 INFORMATION_SCHEMA 的資料庫（模式）的資料字典，其中包含許多顯示為表的物件。

SHOW 語句： 用於獲取伺服器統計資訊、模式和模式物件的相關資料的專用語法。 SHOW DATABASES 和 SHOW TABLES 返回包含資料庫和表名的列表； SHOW COLUMNS 生成表中列的定義；使用 SHOW 語句需要有 SELECT 許可權。

DESCRIBE ：可用於查詢表結構和列屬性的 SQL 語句快捷方式

mysqlshow ：用作指向一些 SHOW 語句的命令列命令。您設定的引數將決定要顯示的資訊，然後程式會發出相應的 SHOW 語句並顯示語句的結果。

下面將詳細介紹這 4 中訪問後設資料的方法。

1.2. INFORMATION_SCHEMA 資料庫

INFORMATION_SCHEMA 資料庫充當資料庫後設資料的中央系統資訊庫，包含模式和模式物件、伺服器統計資訊（狀態變數、設定、連線）。它未儲存在硬碟上，從這個方面來看，它是“虛擬資料庫”；但是，它與其他任何資料庫一樣包含表，與其他任何表一樣可以使用 SELECT 來訪問其中表的內容

1) 查詢 INFORMATION_SCHEMA 中的表

mysql> SELECT TABLE_NAME

-> FROM INFORMATION_SCHEMA.TABLES

-> WHERE TABLE_SCHEMA = 'information_schema'

-> ORDER BY TABLE_NAME;

INFORMATION_SCHEMA 表包含以下型別的資訊：

Ø 表資訊

COLUMNS ：表和檢視中的列

ENGINES ：儲存引擎

SCHEMATA ：資料庫

TABLES ：資料庫中的表

VIEWS ：資料庫中的檢視

Ø 分割槽

PARTITIONS ：表分割槽

FILES ：儲存 MySQL NDB 磁碟資料表的檔案

Ø 許可權

COLUMN_PRIVILEGES ： MySQL 使用者帳戶所擁有的列許可權

SCHEMA_PRIVILEGES ： MySQL 使用者帳戶所擁有的資料庫許可權

TABLE_PRIVILEGES ： MySQL 使用者帳戶所擁有的表許可權

USER_PRIVILEGES ： MySQL 使用者帳戶所擁有的全域性許可權

Ø 字符集支援

CHARACTER_SETS ：可用的字符集

COLLATIONS ：每個字符集的整理

COLLATION_CHARACTER_SET_APPLICABILITY ：適用於特定字符集的整理

Ø 約束和索引

KEY_COLUMN_USAGE ：關鍵列的約束

REFERENTIAL_CONSTRAINTS ：外來鍵

STATISTICS ：表索引

TABLE_CONSTRAINTS ：表的約束

Ø 伺服器設定和狀態

KEY_COLUMN_USAGE ：約束

GLOBAL_STATUS ：所有 MySQL 連線的狀態值

GLOBAL_VARIABLES ：用於新的 MySQL 連線的值

PLUGINS ：伺服器外掛

PROCESSLIST ：指示哪些執行緒正在執行

SESSION_STATUS ：當前 MySQL 連線的狀態值

SESSION_VARIABLES ：當前 MySQL 連線的生效值

Ø 例程及相關資訊

EVENTS ：預定事件

ROUTINES ：儲存過程和功能

TRIGGERS ：資料庫中的觸發器

PARAMETERS ：儲存過程和功能引數以及儲存函式

Ø InnoDB

INNODB_CMP 和 INNODB_CMP_RESET ：對壓縮的 InnoDB 表的相關操作的狀態

INNODB_CMPMEM 和 INNODB_CMPMEM_RESET ： InnoDB 緩衝池中壓縮頁面的狀態

INNODB_LOCKS ： InnoDB 事務所請求和持有的每個鎖

INNODB_LOCK_WAITS ：每個阻塞的 InnoDB 事務的一個或多個行鎖

INNODB_TRX ：當前正在 InnoDB 內部執行的所有事務

TABLESPACES ：活動的表空間

有關 INFORMATION_SCHEMA 表的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/information-schema.html 。

例如：查詢 INFORMATION_SCHEMA 資料庫表列：

mysql> SELECT COLUMN_NAME

-> FROM INFORMATION_SCHEMA.COLUMNS

-> WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'

-> AND TABLE_NAME = 'VIEWS';

當使用 SELECT 語句在 INFORMATION_SCHEMA 表中檢索後設資料時，您可以使用任何常見的 SELECT 功能。透過使用 CREATE TABLE...SELECT 語句或 INSERT...SELECT 語句，您可以將 INFORMATION_SCHEMA 查詢的結果檢索到其他表中。您可以儲存結果，以便稍後在其他語句中使用它們。

A. 顯示用於給定資料庫中表的儲存引擎

mysql> SELECT TABLE_NAME, ENGINE

-> FROM INFORMATION_SCHEMA.TABLES

-> WHERE TABLE_SCHEMA = 'world_innodb';

B. 查詢所有包含 SET 列的表

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

-> FROM INFORMATION_SCHEMA.COLUMNS

-> WHERE DATA_TYPE = 'set';

C. 顯示每個字符集的預設整理

mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME

-> FROM INFORMATION_SCHEMA.COLLATIONS

-> WHERE IS_DEFAULT = 'Yes';

D. 顯示每個資料庫中表的編號

mysql> SELECT TABLE_SCHEMA, COUNT(*)

-> FROM INFORMATION_SCHEMA.TABLES

-> GROUP BY TABLE_SCHEMA;

E. INFORMATION_SCHEMA 表是隻讀的，無法用 INSERT 、 DELETE 或 UPDATE 之類的語句進行修改。如果執行這些型別的語句以嘗試更改 INFORMATION_SCHEMA 表中的資料，伺服器將生成錯誤。

mysql> DELETE FROM INFORMATION_SCHEMA.VIEWS;

ERROR 1044 (42000): Access denied for user

'root'@'localhost' to database 'information_schema'

2) 使用 INFORMATION_SCHEMA 表建立 Shell 命令

使用 CONCAT 功能可以將字串內容結合起來建立可在命令列中執行的 shell 指令碼。如示例所示， SQL 語句將生成一條輸出，僅轉儲 world_innodb 資料庫中那些以單詞“ Country ”開始的的表。輸出將生成可以在 shell 命令列上正確執行的 shell 指令碼。下一步是將此輸出儲存在一個可在 shell 命令列中執行的批處理檔案中。這透過新增子句 INTO OUTFILE 來完成：

mysql> SELECT CONCAT("mysqldump -uroot -p ",

-> TABLE_SCHEMA, " ",TABLE_NAME, " >> ",TABLE_SCHEMA,".sql")

-> FROM TABLES WHERE TABLE_NAME LIKE 'Country%'

-> INTO OUTFILE '/Country_Dump.sh';

然後可以在命令列中執行此檔案，命令列將執行示例的兩個 mysqldump 命令：

shell> \tmp\Country_Dump.sh

shell> \tmp\mysqldump -uroot -poracle world_innodb Country >>world_innodb.sql

shell> \tmp\mysqldump -uroot -poracle world_innodb Country_Language >>world_innodb.sql

3) 使用 INFORMATION_SCHEMA 表建立 SQL 語句

使用 mysql 命令建立 SQL 語句，使用 -e 選項輸入 SELECT/CONCAT 語句：

shell> mysql -uroot -p --silent --skip-column-names -e

"SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.',

TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.',

TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'world_innodb';"

將導致以下語句傳送到標準輸出：

CREATE TABLE world_innodb.City_backup LIKE world_innodb.City;

CREATE TABLE world_innodb.Country_backup LIKE world_innodb.Country_backup;

CREATE TABLE world_innodb.CountryLanguage_backup LIKE world_innodb.CountryLanguage_backup;

INFORMATION_SCHEMA 表可建立可在命令列中執行的 SQL 語句。本示例使用 mysql 命令執行了一個語句，以製作 world_innodb 資料庫中所有表的精確副本。此命令將建立 SQL 輸出，如果執行該輸出，將基於 world_innodb 資料庫中的表建立三個備份表。

注： --silent 命令在輸出中刪除列標題， --skip-column-names 命令刪除輸出中的格式（使輸出類似於表的格式）。這兩個命令用來確保對命令自身的解釋是正確的，沒有任何干擾執行的外部格式或標題行問題。

新增管道符號 (|) 並隨之執行 mysql 命令會將這些 SQL 語句傳送到 MySQL 伺服器以便執行：

shell> mysql -uroot -p --silent --skip-column-names -e "SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'world_innodb';" | mysql -uroot -poracle

1.3. SHOW 語句

除了 INFORMATION_SCHEMA 表之外， MySQL 還支援 SHOW 和 DESCRIBE 語句，作為訪問後設資料的備選方式。 SHOW 和 DESCRIBE 語法不如使用 INFORMATION_SCHEMA 查詢靈活，但是對於大多數用途， SHOW 和 DESCRIBE 語法就足夠了。在這些情況下，使用 MySQL 特定語法通常會更快速、簡單。 MySQL 支援的 SHOW 語句有：

SHOW DATABASES

SHOW TABLES

SHOW TABLE STATUS

SHOW CREATE TABLE

SHOW OPEN TABLES

SHOW INDEX

SHOW COLUMNS

SHOW PROCESSLIST

SHOW COLLATION

SHOW CHARACTER SET

SHOW 語句示例：

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

| world_innodb |

+--------------------+

mysql> SHOW TABLES;

mysql> SHOW TABLES FROM mysql;

mysql> SHOW TABLES FROM INFORMATION_SCHEMA;

mysql> SHOW COLUMNS FROM CountryLanguage;

mysql> SHOW FULL COLUMNS FROM CountryLanguage\G

mysql> SHOW DATABASES LIKE 'm%';

mysql> SHOW COLUMNS FROM Country WHERE `Default` IS NULL;

mysql> SHOW INDEX FROM City\G

mysql> SHOW CHARACTER SET;

mysql> SHOW COLLATION;

1.4. DESCRIBE 語句

DESCRIBE 等效於 SHOW COLUMNS ，可以縮寫為 DESC

mysql> DESCRIBE <table_name>;

顯示 INFORMATION_SCHEMA 表資訊

mysql> DESCRIBE INFORMATION_SCHEMA.CHARACTER_SETS;

+----------------------+-------------+------+-----+---------+-------+

+----------------------+-------------+------+-----+---------+-------+

| MAXLEN | bigint(3) | NO | | 0 | |

+----------------------+-------------+------+-----+---------+-------+

例如：

mysql> DESCRIBE table_name;

mysql> DESC table_name;

以下語句等效於上述 DESCRIBE/DESC 示例：

mysql> SHOW COLUMNS FROM table_name;

但是， SHOW COLUMNS 支援可選的 LIKE 和 WHERE 子句，而 DESCRIBE 不支援。

EXPLAIN ：當指定表名稱作為引數時， EXPLAIN 等效於 DESCRIBE ：

mysql> EXPLAIN table_name;

1.5. mysqlshow 命令

mysqlshow 為各種格式的 SHOW 語句提供了一個命令列介面，這些語句用於列出資料庫的名稱、資料庫中的表或有關表列或索引的資訊。

mysqlshow 的選項部分可包含任一標準連線引數選項，例如 --host 或 --user 。如果預設連線引數不適合，則必須提供選項。 mysqlshow 也接受特定於其自身執行的選項。使用 --help 選項呼叫 mysqlshow 可檢視其選項的完整列表。 mysqlshow 所執行的操作取決於已提供的非選項引數的數量。

mysqlshow 示例

顯示所有資料庫或特定資料庫、表和 / 或列的相關資訊：

A. 在沒有引數的情況下， mysqlshow 將顯示類似於 SHOW DATABASES 的結果

shell> mysqlshow -u<user_name> -p<password>

+--------------------+

| Databases |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

| world_innodb |

+--------------------+

B. 在使用單個引數的情況下， mysqlshow 將該引數解釋為資料庫名稱，並針對該資料庫顯示類似於 SHOW TABLES 的結果。

shell> mysqlshow world_innodb

C. 在有兩個引數的情況下， mysqlshow 將引數解釋為資料庫和表名稱，並針對該表顯示類似於 SHOW FULL COLUMNS 的結果。

shell> mysqlshow world_innodb City

D. 在有三個引數的情況下，其輸出與兩個引數的情況相同，不同之處在於： mysqlshow 將第三個引數當做列名稱，且僅針對該列顯示 SHOW FULL COLUMNS 輸出。

shell> mysqlshow world_innodb City CountryCode

E. 如果命令列中最後的引數包含特殊字元， mysqlshow 會將該引數解釋為模式，且僅顯示與該模式匹配的名稱。特殊字元包括： % 或 * （匹配任一字元序列），以及 _ 或 ? （匹配任一單個字元）。本示例中的命令僅顯示那些名稱始於 w 的資料庫。

shell> mysqlshow "w%"

注：這些示例要求在執行命令時使用使用者和口令作為引數。

MySQL入門--後設資料

MySQL入門--後設資料

相關文章