MySQL入门--元数据

来源：这里教程网时间：2026-03-01 12:08:40 作者：

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;

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

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

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