MySQL元數據有哪些

本篇內容主要講解“MySQL元數據有哪些”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“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;

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

| Field | Type | Null | Key | Default | Extra |

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

| CHARACTER_SET_NAME | varchar(64) | NO | | | |

| DEFAULT_COLLATE_NAME | varchar(64) | NO | | | |

| DESCRIPTION | varchar(60) | NO | | | |

| 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元數據有哪些”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！