当前位置：首页 > news >正文

MySQL元数据库information schema

news 2026/6/24 12:27:59

一、概述

information_schema提供了对数据库元数据、统计信息以及有关MySQLServer信息的访问（例如：数据库名或表名、字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典或系统目录。

在每个MySQL实例中都有一个独立的information_schema，用来存储MySQL实例中所有其他数据库的基本信息。information_schema库下包含多个只读表（非持久表），所以在磁盘中的数据目录下没有对应的关联文件，且不能对这些表设置触发器。虽然在查询时可以使用USE语句将默认数据库设置为information_schema，但该库下的所有表是只读的，不能执行INSERT、UPDATE、DELETE等数据变更操作。针对information_schema下的表的查询操作可以替代一些SHOW查询语句（例如：SHOW DATABASES、SHOW TABLES等）。

二、入门

查看当前数据库下表存储的信息

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS     |
| APPLICABLE_ROLES                      |
| CHARACTER_SETS                        |
| CHECK_CONSTRAINTS                     |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLLATIONS                            |
| COLUMN_PRIVILEGES                     |
| COLUMN_STATISTICS                     |
| COLUMNS                               |
| COLUMNS_EXTENSIONS                    |
| ENABLED_ROLES                         |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| INNODB_BUFFER_PAGE                    |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_CACHED_INDEXES                 |
| INNODB_CMP                            |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_CMP_RESET                      |
| INNODB_CMPMEM                         |
| INNODB_CMPMEM_RESET                   |
| INNODB_COLUMNS                        |
| INNODB_DATAFILES                      |
| INNODB_FIELDS                         |
| INNODB_FOREIGN                        |
| INNODB_FOREIGN_COLS                   |
| INNODB_FT_BEING_DELETED               |
| INNODB_FT_CONFIG                      |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_DELETED                     |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_INDEXES                        |
| INNODB_METRICS                        |
| INNODB_SESSION_TEMP_TABLESPACES       |
| INNODB_TABLES                         |
| INNODB_TABLESPACES                    |
| INNODB_TABLESPACES_BRIEF              |
| INNODB_TABLESTATS                     |
| INNODB_TEMP_TABLE_INFO                |
| INNODB_TRX                            |
| INNODB_VIRTUAL                        |
| KEY_COLUMN_USAGE                      |
| KEYWORDS                              |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| RESOURCE_GROUPS                       |
| ROLE_COLUMN_GRANTS                    |
| ROLE_ROUTINE_GRANTS                   |
| ROLE_TABLE_GRANTS                     |
| ROUTINES                              |
| SCHEMA_PRIVILEGES                     |
| SCHEMATA                              |
| SCHEMATA_EXTENSIONS                   |
| ST_GEOMETRY_COLUMNS                   |
| ST_SPATIAL_REFERENCE_SYSTEMS          |
| ST_UNITS_OF_MEASURE                   |
| STATISTICS                            |
| TABLE_CONSTRAINTS                     |
| TABLE_CONSTRAINTS_EXTENSIONS          |
| TABLE_PRIVILEGES                      |
| TABLES                                |
| TABLES_EXTENSIONS                     |
| TABLESPACES                           |
| TABLESPACES_EXTENSIONS                |
| TRIGGERS                              |
| USER_ATTRIBUTES                       |
| USER_PRIVILEGES                       |
| VIEW_ROUTINE_USAGE                    |
| VIEW_TABLE_USAGE                      |
| VIEWS                                 |
+---------------------------------------+
79 rows in set (0.00 sec)

根据MySQL版本的不同，表的个数和存放是有所不同的。

MySQL 5.6版本

总共有59张表，其中10张MyISAM引擎临时表(数据字典表)，49张Memory引擎临时表(保存统计信息和一些临时信息)。

MySQL 5.7版本

总共有61张表，其中10个InnoDB存储引擎临时表(数据字典表)，51个Memory引擎临时表。

MySQL 8.0版本

数据字典表(包含部分原memory引擎临时表)都迁移到了mysql数据库的schema架构下，且在mysql schema下这些数据字典表被隐藏，无法直接访问，需要通过information_schema下的同名表进行访问(统计信息表保留在information_schema下且仍然为Memory引擎)。

三、表分类

information_schema中的表可以按照其功能分为以下几个主要类别：

表结构相关表
权限管理相关表
统计信息相关表
事件调度器相关表
触发器相关表
视图相关表
字符集和校对规则相关表
存储过程和函数相关表

3.1 表结构相关表

tables表: 包含了数据库中所有表的元数据信息。通过查询tables表，可以获取表的名称、表类型、创建时间、最后修改时间等信息。
columns表: 包含了数据库中所有列的元数据信息。通过查询columns表，可以获取列的名称、数据类型、默认值、是否允许为空等信息。

3.2 权限管理相关表

user_privileges表: 包含了用户在全局范围内的权限信息。通过查询user_privileges表，可以获取用户的权限级别和权限范围。
table_privileges表 : 包含了用户在特定表上的权限信息。通过查询table_privileges表，可以获取用户在某个表上的权限级别和权限范围。

3.3 统计信息相关表

statistics表: 包含了表的索引信息。通过查询statistics表，可以获取表的索引名称、索引类型、唯一性等信息。

3.4 事件调度器相关表

events表: 包含了数据库中所有事件的元数据信息。通过查询events表，可以获取事件的名称、调度器名称、触发条件、执行频率等信息。

3.5 触发器相关表

triggers表: 包含了数据库中所有触发器的元数据信息。通过查询triggers表，可以获取触发器的名称、触发时机、触发事件、触发条件等信息。

3.6 视图相关表

views表: 包含了数据库中所有视图的元数据信息。通过查询views表，可以获取视图的名称、定义、创建时间等信息。

3.7 字符集和校对规则相关表

character_sets表: 包含了数据库中所有字符集的元数据信息。通过查询character_sets表，可以获取字符集的名称、编码、最大长度等信息。
collations表: 包含了数据库中所有校对规则的元数据信息。通过查询collations表，可以获取校对规则的名称、字符集、排序规则等信息。

3.8 存储过程和函数相关表

routines表: 包含了数据库中所有存储过程和函数的元数据信息。通过查询routines表，可以获取存储过程和函数的名称、定义、参数列表等信息。

四、实践操作

常用的show命令如下：

SHOW DATABASES;                          #查看所有数据库
SHOW TABLES;                             #查看当前库的所有表
SHOW TABLES FROM  库名                   #查看某个指定库下的表
SHOW CREATE DATABASE 库名                #查看建库语句
SHOW CREATE TABLE 库名.表名              #查看建表语句
SHOW GRANTS FOR  root@'localhost'        #查看用户的权限信息
SHOW charset；                           #查看字符集
SHOW COLLATION                           #查看校对规则
SHOW PROCESSLIST;                        #查看数据库连接情况
SHOW INDEX FROM                          #表的索引情况
SHOW STATUS                              #数据库状态查看
SHOW STATUS LIKE '%lock%';               #模糊查询数据库某些状态
SHOW VARIABLES                           #查看所有配置信息
SHOW VARIABLES LIKE '%lock%';            #查看部分配置信息
SHOW ENGINES                             #查看支持的所有的存储引擎
SHOW ENGINE INNODB STATUS\G              #查看InnoDB引擎相关的状态信息
SHOW BINARY LOGS                         #列举所有的二进制日志
SHOW MASTER STATUS                       #查看数据库的日志位置信息
SHOW BINLOG evnets IN                    #查看二进制日志事件
SHOW SLAVE STATUS \G                     #查看从库状态
SHOW RELAYLOG EVENTS                     #查看从库relaylog事件信息
DESC (SHOW colums FROM city)             #查看表的列定义信息
-- https://dev.mysql.com/doc/refman/8.0/en/show.html

常用查询语句

-- 1. 查询整个数据库中所有库和所对应的表信息
SELECT
TABLE_SCHEMA,
GROUP_CONCAT(TABLE_NAME)
FROM
information_schema.TABLES
GROUP BY TABLE_SCHEMA;--2. 统计所有库中的表个数
SELECT
TABLE_SCHEMA,
COUNT(TABLE_NAME)
FROM
information_schema.TABLES
GROUP BY TABLE_SCHEMA;-- 3. 查询所有innodb引擎的表及所在的库
SELECT
TABLE_SCHEMA,
TABLE_NAME,
ENGINE
FROM
information_schema.TABLES
WHERE ENGINE = 'InnoDB';-- 4. 统计world数据库下每张表的磁盘空间占用
SELECT
TABLE_SCHEMA,
TABLE_NAME,
(
TABLE_ROWS * AVG_ROW_LENGTH + INDEX_LENGTH
) / 1024 AS size_KB
FROM
information_schema.TABLES
WHERE TABLE_SCHEMA = 'world';-- 5. 统计数据库总数据量
SELECT
SUM(
TABLE_ROWS * AVG_ROW_LENGTH + INDEX_LENGTH
) / 1024 AS size_KB
FROM
information_schema.TABLES;-- 6. 生成整个数据库下的所有表的单独备份语句
--格式：
--mysqldump -uroot -p123 库名 表名 >/bak/库名_表名.sql
--语句拼接：
SELECT
CONCAT(
"mysqldump -uroot -p123 ",
TABLE_SCHEMA,
" ",
TABLE_NAME,
" ",
">/bak/",
TABLE_SCHEMA,
"_",
TABLE_NAME,
".sql"
)
FROM
information_schema.TABLES
WHERE TABLE_SCHEMA = 'world'
INTO OUTFILE '/tmp/b.sh';