天天看點
傳回

MySQL擷取資料庫中繼資料相關指令：DESC、SHOW、INFORMATION_SCHEMA、mysqlshow、mysqldump

  • MySQL提供了多種擷取資料庫中繼資料（即有關資料庫的資訊與它裡面的各種對象）的方式：
    • DESC/EXPLAIN用來檢視表資訊
    • 各種SHOW語句，例如SHOW DATABASES或SHOW TABLES等
    • 指令行程式，例如mysqlshow或mysqldump等

一、DESC/EXPLAIN

DESCRIBE `tbl_name`;
DESC      `tbl_name`;
EXPLAIN   `tbl_name`;
  • DESCRIBE/DESC、EXPLAIN語句可以檢視表的字段資訊，其中包括：字段名、字段資料類型、是否為主鍵、是否為預設值等等

示範案例 

DESC member;
EXPLAIN member;
MySQL擷取資料庫中繼資料相關指令：DESC、SHOW、INFORMATION_SCHEMA、mysqlshow、mysqldump
  • Field：字段名
  • Type：字段資料類型 
  • Null：該字段是否可以存儲NULL值
  • Key：該列是否已編制索引。PRI表示該列是表主鍵的一部分；UNI表示該列是UNIQUE索引的一部分；MUL表示在列中某個給定值允許出現多次
  • Default：預設值
  • Extra：附加資訊（例如AUTO_INCREMENT等）

二、SHOW語句

檢視資料庫 

SHOW DATABASES;
  • 該語句可以列出目前MySQL的資料庫。
MySQL擷取資料庫中繼資料相關指令：DESC、SHOW、INFORMATION_SCHEMA、mysqlshow、mysqldump

檢視資料表 

SHOW TABLES;
SHOW TABLES FROM `db_name`;

SHOW TABLES LIKE `xxx`;
SHOW TABLES FROM `db_name` LIKE `xxx`;
  • 該語句可以用來檢視目前資料庫下的表，或者指定資料庫下的表。該語句支援LIKE子句查詢。
MySQL擷取資料庫中繼資料相關指令：DESC、SHOW、INFORMATION_SCHEMA、mysqlshow、mysqldump

檢視表的建立資訊 

SHOW CREATE TABLE `tbl_name`[\G];
  • 該語句不僅可以檢視表建立時候的詳細語句，而且哈可以檢視存儲引擎和字元編碼。
  • 為了讓輸出結果更加美觀。可以在語句最後加上\G，會有格式化效果。
  • 例如，下面檢視建立member表時的語句：
MySQL擷取資料庫中繼資料相關指令：DESC、SHOW、INFORMATION_SCHEMA、mysqlshow、mysqldump

檢視表的詳細資訊 

SHOW TABLE STATUS;
SHOW TABLE STATUS FROM `db_name`;

SHOW TABLE STATUS LIKE 'tbl_name'[\G];
  • 可以使用該指令檢視目前資料庫中的所有表、指定表的描述資訊
  • 在MySQL 5.0以後的版本中， 也可以查詢INFORMATION_SCHEMA中對應的表。
  • 例如：
MySQL擷取資料庫中繼資料相關指令：DESC、SHOW、INFORMATION_SCHEMA、mysqlshow、mysqldump
  • Name：  表名稱
  • Engine：表的存儲引擎
  • Version：版本
  • Row_format：行格式。對于MyISAM引擎，這可能是Dynamic，Fixed或Compressed。動态行的行長度可變，例如Varchar或Blob類型字段。固定行是指行長度不變，例如Char和Integer類型字段。
  • Rows：表中的行數。對于非事務性表，這個值是精确的，對于事務性引擎，這個值通常是估算的。
  • Avg_row_length：平均每行包括的位元組數 
  • Data_length：整個表的資料量(機關：位元組)
  • Max_data_length：表可以容納的最大資料量
  • Index_length：索引占用磁盤的空間大小 
  • Data_free：對于MyISAM引擎，辨別已配置設定，但現在未使用的空間，并且包含了已被删除行的空間。
  • Auto_increment：下一個Auto_increment的值
  • Create_time：表的建立時間
  • Update_time：表的最近更新時間
  • Check_time：使用 check table 或myisamchk工具檢查表的最近時間
  • Collation：表的預設字元集和字元排序規則
  • Checksum：如果啟用，則對整個表的内容計算時的校驗和
  • Create_options：指表建立時的其他所有選項
  • Comment：包含了其他額外資訊，對于MyISAM引擎，包含了注釋徐标新，如果表使用的是innodb引擎 ，将現實表的剩餘空間。如果是一個視圖，注釋裡面包含了VIEW字樣。

檢視字段資訊 

SHOW [FULL] COLUMNS FROM `tbl_name`;
SHOW [FULL] FIELDS FROM `tbl_name`;

SHOW [FULL] COLUMNS FROM `tbl_name` LIKE `xx`;
  • 這兩個語句用來檢視表字段，顯示的資訊與DESC一樣。該語句支援LIKE子句篩選。
  • 例如：
MySQL擷取資料庫中繼資料相關指令：DESC、SHOW、INFORMATION_SCHEMA、mysqlshow、mysqldump
  • FULL參數會顯示更多的資訊，例如字段的操作權限、注釋等。例如：
MySQL擷取資料庫中繼資料相關指令：DESC、SHOW、INFORMATION_SCHEMA、mysqlshow、mysqldump

檢視索引資訊 

SHOW INDEX FROM `tbl_name`;
  • 該語句可以用來檢視表的索引資訊。
  • 例如：
MySQL擷取資料庫中繼資料相關指令：DESC、SHOW、INFORMATION_SCHEMA、mysqlshow、mysqldump

三、INFORMATION_SCHEMA擷取中繼資料

  • 擷取資料庫資訊的另一個辦法是通路INFORMATION_SCHEMA庫。這個資料庫是以SQL 标準為基礎建構的。也就是說，雖然有些内容是MySQL特有的，但對它的通路機制是标準化的。這使得information_schem a有着優于各種SHOW語句(它們都是MySQL特有的)的可移植性。
  • 通過SELECT語句可以通路INFORMATION_SCHEMA庫，并且這種方式非常靈活。在 SHOW語句的輸出裡，列的個數是固定的，而且無法把輸出内容捕獲到表裡去。利用INFORMATION_SCHEMA 資料庫 ，SELECT語句可以選取特定的輸出列，而且WHERE子句可以讓你通過各種表達式挑選你真正需要的資訊。不僅如此，你還可以使用連接配接或子査詢，也可以使用CREATE TABLE ... SELECT或INSERT INTO...SELECT把檢索結果存儲到另一個表，以便進一步處理。
  • 可以把INFORMATION_SCHEMA庫想象成一個虛拟的庫，其中的表是各種不同資料庫中繼資料的視圖。如果想要知道INFORMATION_SCHEMA庫包含了哪些表，可以使用SHOW TABLES語句： 
SHOW TABLES IN INFORMATION_SCHEMA;
MySQL擷取資料庫中繼資料相關指令：DESC、SHOW、INFORMATION_SCHEMA、mysqlshow、mysqldump
  • SCHEMATA、TABLES、VIEWS、ROUTINES、TRIGGERS、EVENTS、 PARAMETERS、PARTITIONS、COLUMNS：與資料庫、表、視圖、存儲例程、觸 發器、事件、表分區和列有關的資訊。
  • FILES：與用于存儲表空間資料的那些檔案有關的資訊。
  • TABLE_CONSTRAINTS、 KEY_COLUMN_USAGE：與具有限制條件(如唯一值索引或外鍵)的表和列有關的資訊。
  • STATISTICS：與表索引特性有關的資訊。
  • REFERENTIAL_CONSTRAINTS：與外鍵有關的資訊。
  • CHARACTER_SETS、COLLATIONS、COLLATION_CHARACTER_SET_APPLICABILITY：與所支援的字元集、每種字元集的排序規則，以及每種排序規則 與其字元集之間的映射關系有關的資訊。
  • ENGINES、PLUGINS：與存儲引擎和伺服器插件有關的資訊。
  • USER_PRIVILEGES、SCHEMA_PRIVILEGES、TABLE_PRIVILEGES、COLUMN_PRIVILEGES：與全局、資料庫 、表和列的權限配置設定有關的資訊，分别來自于m ysql資料庫裡的user、db、tables_priv和coluuin_priv表。
  • GLOBAL_VARIABLES、SESSION_VARIABLES、GLOBAL_STATUS、 SESSION_STATUS：與全局和會話的系統變量值和狀态變量值。
  • PROCESSLIST：與伺服器内的執行線程有關的資訊。
  • 個别存儲引擎還會在information_schema裡增加它們自己的表。例如，InnoDB就會這樣做。
  • 如果想要确定某個給定的INFORMATION_SCHEMA裡表都包含有哪些列，則可以使用語 句SHOW COLUMNS或 DESCRIBE： 
DESC INFORMATION_SCHEMA.CHARACTER_SETS;
MySQL擷取資料庫中繼資料相關指令：DESC、SHOW、INFORMATION_SCHEMA、mysqlshow、mysqldump
  • 如果想要顯示某個表的資訊，可以使用SELECT語句。(INFORMATION_SCHEMA資料庫 及其裡面的所有表和列的名字都不區分大小寫)。用于檢視INFORMATION_SCHEMA裡的某個給定表的所有列的通用査詢語句，如下所示： 
SELECT * FROM INFORMATION_SCHEMA.tbl_name;
  • 如果想要有選擇地査看，則可以加上一條 WHERE子句。
  • 前面介紹了如何利用SHOW語 句去檢査某個表是否存在，以及它所用的存儲引擎是哪一種。INFORMATION_SCHEMA裡的表可以提供同樣的資訊。下面的査詢語句使用了 INFORMATION_SCHEMA裡的表來測試某個特定的表是否存在。如果該表存在，則傳回1；如果它不存在，則傳回0。 
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=`sampdb` AND TABLES_NAME=`member`;
  • 下面這條査詢語句可用于檢査某個表所使用的存儲引擎是哪一種： 
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=`sampdb` AND TABLES_NAME=`stduent`;

四、從指令行擷取中繼資料

  • 在使用mysqlshow和mysqldump時，請記得要指定必要的連接配接參數選項，如--host、 --user或--password。

mysqlshow

  • mysqlshow工具可以在不進入MySQL的情況下，檢視MySQL的相關資訊。
  • 預設情況下，直接輸入顯示資料庫清單 
mysqlshow -uroot -p
  • 也可以用來檢視指定資料庫中所有的表。例如下面顯示sampdb資料庫中所有的表 
mysqlshow sampdb -uroot -p
  • 可以同時指定資料庫名和表名，檢視表的資訊，等價于DESC/SHOW FIELD等指令。如下所示： 
mysqlshow sampdb member -uroot -p
  • 例如下面還有幾個示範： 
# 檢視表的索引
mysqlshow --keys db_name tbl_name

# 檢視資料庫裡所有表的描述資訊
mysqlshow --status db_name

mysqldump

  • 用戶端程式mysqldump能夠讓你看到CREATE TABLE語句(與SHOW CREATE TABLE語句很像)所定義的表結構。
  • 如果使用mysqldump來査看表結構，請切記加上--no-data選項，否則看到的内容将是表裡的資料！ 
mysqldump --no-data db_name [tbl_name] ...;
  • 如果你指定了資料庫的名字，而沒有給出任何表名，那 麼mysqldump将把該資料庫 裡所有表的結構顯示出來。否則，它将隻會顯示那些通過名字指定的那些表的資訊。
資料庫元資訊 desc information mysqlshow mysqldump
上一篇: MySQL資料庫基礎（mysql資料類型、資料表的操作）
下一篇: mysql 檢視中繼資料常用語句

繼續閱讀