MySQL 获取元数据

— 元数据访问方法
1.什么是元数据:数据库是数据的结构化集合,元数据是“有关数据的数据”;
2.MySQL通过以访问元数据的方法:
（1）INFORMATION_SCHEMA:MySQL服务器包含一个被实现为名为INFORMATION_SCHEMA的数据库(模式)的数据字典,其中包含许多显示为表的对象;
（2）SHOW语句:用于获取服务器统计信息,模式和模式对象的相关数据的专用语法;

• SHOW DATABASES和SHOW TABLES:返回包含数据库和表名的列表;
• SHOW COLUMNS:生成表中列的定义;SHOW COLUMNS FROM table等于DESC table;
• 需要有SELECT特权才能使用SHOW语句;

（3）DESCRIBE:可用于检查表结构和列属性的SQL语句快捷方式;
（4）mysqlshow:用作指向一些SHOW语句的命令行前端的客户机程序;

— INFORMATION_SCHEMA数据库
— INFORMATION_SCHEMA表;
1.表信息
• COLUMNS:表和视图中的列
• ENGINES:存储引擎
• SCHEMATA:数据库
• TABLES:数据库中的表
• VIEWS:数据库中的视图
2.分区
• PARTITIONS:表分区
• FILES:存储 MySQL NDB 磁盘数据表的文件
3.特权
• COLUMN_PRIVILEGES:MySQL 用户帐户所拥有的列特权
• SCHEMA_PRIVILEGES:MySQL 用户帐户所拥有的数据库特权
• TABLE_PRIVILEGES:MySQL 用户帐户所拥有的表特权
• USER_PRIVILEGES:MySQL 用户帐户所拥有的全局特权
4.字符集支持
• CHARACTER_SETS:可用的字符集
• COLLATIONS:每个字符集的排序
• COLLATION_CHARACTER_SET_APPLICABILITY:适用于特定字符集的排序
5.约束和索引
• KEY_COLUMN_USAGE:关键列的约束
• REFERENTIAL_CONSTRAINTS:外键
• STATISTICS:表索引
• TABLE_CONSTRAINTS:表的约束
6.服务器设置和状态
• KEY_COLUMN_USAGE:约束
• GLOBAL_STATUS:所有 MySQL 连接的状态值
• GLOBAL_VARIABLES:用于新的 MySQL 连接的值
• PLUGINS:服务器插件
• PROCESSLIST:指示哪些线程正在运行
• SESSION_STATUS:当前 MySQL 连接的状态值
• SESSION_VARIABLES:当前 MySQL 连接的生效值
7.例程及相关信息
• EVENTS:预定事件
• ROUTINES:存储过程和功能
• TRIGGERS:数据库中的触发器
• PARAMETERS:存储过程和功能参数以及存储函数
8.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表列;
— 对INFORMATION_SCHEMA使用SELECT;
— INFORMATION_SCHEMA示例;
1.显示用于给定数据库中表的存储引擎;
2.查找所有包含SET列的表;
3.显示每个字符集的默认排序规则;
4.显示每个数据库中表的编号;
5.INFORMATION_SCHEMA表是只读的,无法用INSERT/DELETE/UPDATE之类的语句进行修改;如果执行这些类型的语句以尝试更改INFORMATION_SCHEMA表中的数据,服务器将生成错误;

— 使用INFORMATION_SCHEMA表创建Shell命令;
1.本幻灯片中的示例所示,SQL语句将生成一条输出,仅导出world_innodb数据库中那些以单词“Country”开始的的表;
2.输出将生成可以在shell命令行上正确执行的shell脚本;下一步是将此输出存储在一个可
在shell命令行中执行的批处理文件中,这通过添加子句INTO OUTFILE来完成:

   SELECT CONCAT(“mysqldump -uroot -pmysql “, TABLE_SCHEMA, ” “, TABLE_NAME, ” >> “,TABLE_SCHEMA, “.sql”)
   FROM TABLES WHERE TABLE_NAME LIKE ‘Country%’
   INTO OUTFILE ‘\\tmp\\Country_Dump.sh’

3.然后可以在命令行中执行此文件,命令行将运行本幻灯片中所示的两个mysqldump命令:

    shell> \\tmp\\Country_Dump.sh
    shell> \\tmp\\mysqldump -uroot -pmysql world_innodb Country >> world_innodb.sql
    shell> \\tmp\\mysqldump -uroot -pmysql world_innodb Country_Language >> world_innodb.sql

— 使用INFORMATION_SCHEMA表创建SQL语句;
1.本幻灯片中的示例使用mysql命令执行了一个语句,以制作world_innodb数据库中所有表的精确副本;
（1）–silent命令在输出中删除列标题;
（2）–skip-column-names命令删除输出中的格式(使输出类似于表的格式);
（3）这两个命令用来确保对命令自身的解释是正确的,没有任何干扰执行的外部格式或标题行问题;
2.添加管道符号[|]并随之执行mysql命令会将这些SQL语句发送到MySQL服务器以便执行:

shell> mysql -uroot -pmysql –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 -pmysql

— MySQL支持的 SHOW 语句;
1.除了INFORMATION_SCHEMA表之外,MySQL还支持SHOW和DESCRIBE语句,作为访问元数据的备选方式;
2.SHOW和DESCRIBE语法不如使用INFORMATION_SCHEMA查询灵活,但是对于大多数用途,SHOW和DESCRIBE语法就足够了;在这些情况下,使用MySQL特定语法通常会更快速,简单;
3.可以通过多种形式使用SHOW语句,如下所示:

• SHOW DATABASES:列出可用数据库的名称
• SHOW TABLES:列出默认数据库中的表
• SHOW TABLES FROM <database_name>:列出指定数据库中的表
• SHOW COLUMNS FROM <table_name>:显示表的列结构
• SHOW INDEX FROM <table_name>:显示表中有关索引和索引列的信息
• SHOW CHARACTER SET:显示可用的字符集及其默认排序
• SHOW COLLATION:显示每个字符集的排序

— SHOW语句示例;
mysql> SHOW DATABASES;
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

— 其他SHOW语句示例;
— DESCRIBE语句;
1.DESC table_name等效于SHOW COLUMNS FROM table_name;但是,SHOW COLUMNS支持可选的LIKE和WHERE子句,而DESCRIBE不支持;
2.当指定表名称作为参数时,EXPLAIN等效于DESCRIBE:mysql> EXPLAIN table_name;

— mysqlshow客户机;
1.mysqlshow客户机为各种格式的SHOW语句提供了一个命令行界面,这些语句用于列出数据库的名称,数据库中的表或有关表列或索引的信息;
2.mysqlshow客户机的选项部分可包含任一标准连接参数选项,例如–host或–user;如果默认连接参数不适合,则必须提供选项;mysqlshow也接受特定于其自身运行的选项;
3.使用–help选项调用mysqlshow可查看其选项的完整列表;
4.mysqlshow所执行的操作取决于已提供的非选项参数的数量;

— mysqlshow示例;
1.在没有参数的情况下,mysqlshow将显示类似于SHOW DATABASES的结果;
2.在使用单个参数的情况下,mysqlshow将该参数解释为数据库名称,并针对该数据库显示类似于SHOW TABLES的结果;
3.在有两个参数的情况下,mysqlshow将参数解释为数据库和表名称,并针对该表显示类似于SHOW FULL COLUMNS的结果;
4.在有三个参数的情况下,其输出与两个参数的情况相同,不同之处在于:mysqlshow将第三个参数当做列名称,且仅针对该列显示SHOW FULL COLUMNS输出;
5.如果命令行中最后的参数包含特殊字符,mysqlshow会将该参数解释为模式,且仅显示与该模式匹配的名称;特殊字符包括:%或*(匹配任一字符序列),以及_或?(匹配任一单个字符);本示例中的命令仅显示那些名称始于w的数据库