摘要：MySQL的information_schema测试研究。
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查询结果为空。
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。