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元数据有哪些”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！