MySQL从5.0开始，提供了一个新的数据库，information_schema，用来记录MySQL中的元数据信息。
什么是元数据呢？元数据（Metadata），又称中介数据、中继数据，是描述数据的数据（data about data）。对于数据库来说，就是表名、列明、列类型、索引等各种结构定义。
这个库比较特殊，它是一个虚拟数据库，物理上并不存在，库里供我们查阅信息的各种表也并不是实际存在的物理表，而是视图。
在MySQL 5.7中，information_schema库里包含了61张视图，我们调比较常用的一起来看看。
root@database-one 00:12: [(none)]> use information_schema
Database changed
root@database-one 00:12: [information_schema]> select version();
+------------+
| version() |
+------------+
| 5.7.16-log |
+------------+
1 row in set (0.01 sec)
root@database-one 00:12: [information_schema]> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_LOCKS |
| INNODB_TRX |
| INNODB_SYS_DATAFILES |
| INNODB_FT_CONFIG |
| INNODB_SYS_VIRTUAL |
| INNODB_CMP |
| INNODB_FT_BEING_DELETED |
| INNODB_CMP_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMPMEM_RESET |
| INNODB_FT_DELETED |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_LOCK_WAITS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_SYS_INDEXES |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_BUFFER_PAGE |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_INDEX_CACHE |
| INNODB_SYS_TABLESPACES |
| INNODB_METRICS |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_CMPMEM |
| INNODB_BUFFER_POOL_STATS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLESTATS |
+---------------------------------------+
61 rows in set (0.03 sec)
复制SCHEMATA，提供当前MySQL实例中所有数据库的信息，show databases的结果就是从这里取的。
root@database-one 00:14: [information_schema]> select * from SCHEMATA;
+--------------+----------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+----------------------+----------------------------+------------------------+----------+
| def | information_schema | utf8 | utf8_general_ci | NULL |
| def | atuotest_case | utf8 | utf8_general_ci | NULL |
| def | export_test | utf8 | utf8_bin | NULL |
| def | gftest | utf8 | utf8_general_ci | NULL |
| def | installmentdb | latin1 | latin1_swedish_ci | NULL |
......
复制TABLES，提供数据库中表的信息，包含表名、类型、引擎、创建时间等各种信息，show tables from schemaname的结果就是从这里取的。
root@database-one 00:18: [information_schema]> select * from tables;
+---------------+----------------------+------------------------------------------------------+-------------+--------------------+---------+------------+------------+----------------+-------------+--------------------+--------------+-------------+----------------+---------------------+---------------------+---------------------+--------------------+------------+-------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+----------------------+------------------------------------------------------+-------------+--------------------+---------+------------+------------+----------------+-------------+--------------------+--------------+-------------+----------------+---------------------+---------------------+---------------------+--------------------+------------+-------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------+
| def | information_schema | CHARACTER_SETS | SYSTEM VIEW | MEMORY | 10 | Fixed | NULL | 384 | 0 | 98608896 | 0 | 0 | NULL | 2020-02-16 00:22:47 | NULL | NULL | utf8_general_ci | NULL | max_rows=262144 | |
| def | information_schema | COLLATIONS | SYSTEM VIEW | MEMORY | 10 | Fixed | NULL | 231 | 0 | 100229283 | 0 | 0 | NULL | 2020-02-16 00:22:47 | NULL | NULL | utf8_general_ci | NULL | max_rows=435771 | |
| def | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | MEMORY | 10 | Fixed | NULL | 195 | 0 | 98146620 | 0 | 0 | NULL | 2020-02-16 00:22:47 | NULL | NULL | utf8_general_ci | NULL | max_rows=516222 | |
......
复制TABLE_CONSTRAINTS，提供表的约束信息。
root@database-one 00:24: [information_schema]> select * from TABLE_CONSTRAINTS;
+--------------------+----------------------+--------------------------------+----------------------+------------------------------------------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+----------------------+--------------------------------+----------------------+------------------------------------------------+-----------------+
| def | gftest | PRIMARY | gftest | emp_copy | PRIMARY KEY |
| def | installmentdb | PRIMARY | installmentdb | t_install_order | PRIMARY KEY |
| def | installmentdb | uidx_inst_no | installmentdb | t_install_order | UNIQUE |
| def | installmentdb | uidx_req_seq | installmentdb | t_install_order | UNIQUE |
| def | installmentdb | PRIMARY | installmentdb | t_install_pay_order | PRIMARY KEY |
| def | installmentdb | uidx_inst_no | installmentdb | t_install_pay_order | UNIQUE |
| def | installmentdb | uidx_pay_no | installmentdb | t_install_pay_order | UNIQUE |
| def | installmentdb | PRIMARY | installmentdb | t_install_refund_order | PRIMARY KEY |
| def | installmentdb | uidx_refund_no | installmentdb | t_install_refund_order | UNIQUE |
| def | installmentdb | uidx_req_seq | installmentdb | t_install_refund_order | UNIQUE |
| def | jiradb | PRIMARY | jiradb | ao_21d670_whitelist_rules | PRIMARY KEY |
| def | jiradb | PRIMARY | jiradb | ao_21f425_message_ao | PRIMARY KEY |
......
复制PARTITIONS，提供表的分区信息。
root@database-one 00:27: [information_schema]> select * from PARTITIONS;
+---------------+----------------------+------------------------------------------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+---------------------------+-------------------------+-----------------------+------------+----------------+-------------+--------------------+--------------+-----------+---------------------+---------------------+---------------------+------------+-------------------+-----------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME |
+---------------+----------------------+------------------------------------------------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+---------------------------+-------------------------+-----------------------+------------+----------------+-------------+--------------------+--------------+-----------+---------------------+---------------------+---------------------+------------+-------------------+-----------+-----------------+
| def | information_schema | CHARACTER_SETS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 384 | 0 | 98608896 | 0 | 0 | 2020-02-16 00:29:22 | NULL | NULL | NULL | | | NULL |
| def | information_schema | COLLATIONS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | 231 | 0 | 100229283 | 0 | 0 | 2020-02-16 00:29:22 | NULL | NULL | NULL | | | NULL |
......
复制partition相关的字段为null时，代表这个表是非分区表。
COLUMNS，提供表中的列信息，show columns from schemaname.tablename的结果就是从这里取的。
root@database-one 00:35: [information_schema]> select * from COLUMNS where table_schema='gftest' and table_name='emp';
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+---------------+------------+-------+---------------------------------+----------------+-----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | GENERATION_EXPRESSION |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+---------------+------------+-------+---------------------------------+----------------+-----------------------+
| def | gftest | emp | ename | 1 | NULL | YES | varchar | 10 | 30 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(10) | | | select,insert,update,references | | |
| def | gftest | emp | age | 2 | NULL | YES | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(11) | | | select,insert,update,references | | |
| def | gftest | emp | sal | 3 | NULL | YES | decimal | NULL | NULL | 10 | 2 | NULL | NULL | NULL | decimal(10,2) | | | select,insert,update,references | | |
| def | gftest | emp | hiredate | 4 | NULL | YES | date | NULL | NULL | NULL | NULL | NULL | NULL | NULL | date | | | select,insert,update,references | | |
| def | gftest | emp | deptno | 5 | NULL | YES | int | NULL | NULL | 10 | 0 | NULL | NULL | NULL | int(2) | | | select,insert,update,references | | |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+---------------+------------+-------+---------------------------------+----------------+-----------------------+
5 rows in set (0.01 sec)
复制VIEWS，提供视图信息。需要有show views权限，否则无法查看。
root@database-one 00:37: [information_schema]> select * from VIEWS where table_schema='test';
+---------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+---------+---------------+----------------------+----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION | CHECK_OPTION | IS_UPDATABLE | DEFINER | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+---------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+---------+---------------+----------------------+----------------------+
| def | test | customerorders | select `c`.`id` AS `id`,`p`.`hiredate` AS `hiredate` from (`test`.`range_timestamp` `c` join `test`.`hash_datetime` `p` on((`p`.`id` = `c`.`id`))) group by `c`.`id` order by `p`.`hiredate` desc | NONE | NO | root@% | DEFINER | latin1 | latin1_swedish_ci |
| def | test | customerorders1 | select `c`.`id` AS `id` from (`test`.`range_timestamp` `c` join `test`.`hash_datetime` `p` on((`p`.`id` = `c`.`id`))) group by `c`.`id` order by `p`.`id` desc | NONE | NO | root@% | DEFINER | latin1 | latin1_swedish_ci |
| def | test | customerorders2 | select `c`.`id` AS `id`,`p`.`hiredate` AS `hiredate` from (`test`.`range_timestamp` `c` join `test`.`hash_datetime` `p` on((`p`.`id` = `c`.`id`))) where (`c`.`hiredate` >= '2015-12-11') group by `c`.`id` order by `p`.`hiredate` desc | NONE | NO | root@% | DEFINER | latin1 | latin1_swedish_ci |
+---------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+---------+---------------+----------------------+----------------------+
3 rows in set (0.22 sec)
复制STATISTICS，提供索引的信息，show index from schemaname.tablename的结果就是从这里取的。
root@database-one 00:37: [information_schema]> select * from STATISTICS where table_schema='gftest';
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
| def | gftest | emp_copy | 0 | gftest | PRIMARY | 1 | eno | A | 40 | NULL | NULL | | BTREE | | |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
1 row in set (0.08 sec)
复制TRIGGERS，提供触发器信息，必须有super权限才能查看。
root@database-one 00:40: [information_schema]> select * from TRIGGERS;
+-----------------+----------------+----------------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------+---------------------+----------------------+----------------------+--------------------+
| TRIGGER_CATALOG | TRIGGER_SCHEMA | TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_CATALOG | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | ACTION_ORDER | ACTION_CONDITION | ACTION_STATEMENT | ACTION_ORIENTATION | ACTION_TIMING | ACTION_REFERENCE_OLD_TABLE | ACTION_REFERENCE_NEW_TABLE | ACTION_REFERENCE_OLD_ROW | ACTION_REFERENCE_NEW_ROW | CREATED | SQL_MODE | DEFINER | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+-----------------+----------------+----------------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------+---------------------+----------------------+----------------------+--------------------+
| def | sakila | customer_create_date | INSERT | def | sakila | customer | 1 | NULL | SET NEW.create_date = NOW() | ROW | BEFORE | NULL | NULL | OLD | NEW | 2019-05-31 18:15:34.97 | NO_AUTO_VALUE_ON_ZERO | myzone@% | utf8 | utf8_general_ci | latin1_swedish_ci |
......
复制USER_PRIVILEGES，提供用户权限信息，信息是从 mysql.user表中加载的。
root@database-one 00:43: [information_schema]> select * from USER_PRIVILEGES;
+-------------------------+---------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+-------------------------+--------------+
| 'mysql.sys'@'localhost' | def | USAGE | NO |
| 'root'@'%' | def | SELECT | YES |
| 'root'@'%' | def | INSERT | YES |
| 'root'@'%' | def | UPDATE | YES |
......
复制SCHEMA_PRIVILEGES，提供数据库权限信息，信息是从mysql.db中加载的。
root@database-one 00:45: [information_schema]> select * from SCHEMA_PRIVILEGES;
+-------------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+--------------+-------------------------+--------------+
| 'mysql.sys'@'localhost' | def | sys | TRIGGER | NO |
| 'zeus'@'%' | def | mysql | SELECT | NO |
| 'zeus'@'%' | def | mysql | INSERT | NO |
| 'zeus'@'%' | def | mysql | UPDATE | NO |
......
复制TABLE_PRIVILEGES，提供表权限信息，是从 mysql.tables_priv表中加载的。
root@database-one 00:48: [information_schema]> select * from TABLE_PRIVILEGES;
+-------------------------+---------------+--------------+------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+--------------+------------+----------------+--------------+
| 'mysql.sys'@'localhost' | def | sys | sys_config | SELECT | NO |
+-------------------------+---------------+--------------+------------+----------------+--------------+
1 row in set (0.01 sec)
复制COLUMN_PRIVILEGES，提供列权限信息。信息是从mysql.columns_priv表中加载的。
root@database-one 00:48: [information_schema]> select * from COLUMN_PRIVILEGES;
Empty set (0.01 sec)
复制通过上面几个跟权限相关的表，我们可以清晰地看到MySQL授权的层次，SCHEMA，TABLE，COLUMN级别，当然这些都是基于用户来授予的。总的来说MySQL的授权也是相当的精细的，可以具体到列。我的测试数据库中因为没有分配过列级别的权限，所以COLUMN_PRIVILEGES查询结果为空。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
文章被以下合辑收录
评论
相关阅读
【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1327次阅读
2025-03-13 11:40:53
MySQL8.0统计信息总结
闫建(Rock Yan)
500次阅读
2025-03-17 16:04:03
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
469次阅读
2025-03-13 14:38:19
SQL优化 - explain查看SQL执行计划（一）
金同学
397次阅读
2025-03-13 16:04:22
MySQL突然崩溃？教你用gdb解剖core文件，快速锁定“元凶”!
szrsu
377次阅读
2025-03-13 00:29:43
MySQL生产实战优化（利用Index skip scan优化性能提升257倍）
chengang
330次阅读
2025-03-17 10:36:40
MySQL数据库当前和历史事务分析
听见风的声音
302次阅读
2025-04-01 08:47:17
MySQL 生产实践-Update 二级索引导致的性能问题排查
chengang
250次阅读
2025-03-28 16:28:31
一键装库脚本3分钟极速部署，传统耗时砍掉95%！
IT邦德
240次阅读
2025-03-10 07:58:44
墨天轮个人数说知识点合集
JiekeXu
236次阅读
2025-04-01 15:56:03
