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;

• Field：字段名
• Type：字段数据类型 
• Null：该字段是否可以存储NULL值
• Key：该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次
• Default：默认值
• Extra：附加信息（例如AUTO_INCREMENT等）

二、SHOW语句

查看数据库

SHOW DATABASES;

• 该语句可以列出当前MySQL的数据库。

查看数据表

SHOW TABLES;
SHOW TABLES FROM `db_name`;

SHOW TABLES LIKE `xxx`;
SHOW TABLES FROM `db_name` LIKE `xxx`;

• 该语句可以用来查看当前数据库下的表，或者指定数据库下的表。该语句支持LIKE子句查询。

查看表的创建信息

SHOW CREATE TABLE `tbl_name`[\G];

• 该语句不仅可以查看表创建时候的详细语句，而且哈可以查看存储引擎和字符编码。
• 为了让输出结果更加美观。可以在语句最后加上\G，会有格式化效果。
• 例如，下面查看创建member表时的语句：

查看表的详细信息

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

SHOW TABLE STATUS LIKE 'tbl_name'[\G];

• 可以使用该命令查看当前数据库中的所有表、指定表的描述信息
• 在MySQL 5.0以后的版本中， 也可以查询INFORMATION_SCHEMA中对应的表。
• 例如：

• 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子句筛选。
• 例如：

• FULL参数会显示更多的信息，例如字段的操作权限、注释等。例如：

查看索引信息

SHOW INDEX FROM `tbl_name`;

• 该语句可以用来查看表的索引信息。
• 例如：

三、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;

• 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;

• 如果想要显示某个表的信息，可以使用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将把该数据库 里所有表的结构显示出来。否则，它将只会显示那些通过名字指定的那些表的信息。