MySQL之information_schema数据库详细讲解

 更新时间:2021年8月16日 16:00  点击:1844

1. 概述

information_schema 数据库跟 performance_schema 一样,都是 MySQL 自带的信息数据库。其中 performance_schema 用于性能分析,而 information_schema 用于存储数据库元数据(关于数据的数据),例如数据库名、表名、列的数据类型、访问权限等。

information_schema 中的表实际上是视图,而不是基本表,因此,文件系统上没有与之相关的文件。

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

Database changed
mysql> 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.00 sec)

2. information_schema 库中常用的表

CHARACTER_SETS 表

提供了 mysql 可用字符集的信息。SHOW CHARACTER SET; 命令从这个表获取结果。

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
...
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.07 sec)

mysql> SELECT * FROM CHARACTER_SETS;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION                     | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
| big5               | big5_chinese_ci      | Big5 Traditional Chinese        |      2 |
| dec8               | dec8_swedish_ci      | DEC West European               |      1 |
| cp850              | cp850_general_ci     | DOS West European               |      1 |
...
| eucjpms            | eucjpms_japanese_ci  | UJIS for Windows Japanese       |      3 |
| gb18030            | gb18030_chinese_ci   | China National Standard GB18030 |      4 |
+--------------------+----------------------+---------------------------------+--------+
41 rows in set (0.00 sec)

SCHEMATA 表

当前 mysql 实例中所有数据库的信息。SHOW DATABASES; 命令从这个表获取数据。

mysql> 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          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | sys                | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | utf8                       | utf8_unicode_ci        | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
10 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
10 rows in set (0.00 sec)

TABLES 表

存储数据库中的表信息(包括视图),包括表属于哪个数据库,表的类型、存储引擎、创建时间等信息。SHOW TABLES FROM XX; 命令从这个表获取结果。

mysql> 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 |           16434816 |            0 |         0 |           NULL | 2018-04-23 11:51:32 | NULL                | NULL                | utf8_general_ci   |     NULL | max_rows=43690     |                                            |
| def           | information_schema | COLLATIONS                                           | SYSTEM VIEW | MEMORY             |      10 | Fixed      |       NULL |            231 |           0 |           16704765 |            0 |         0 |           NULL | 2018-04-23 11:51:32 | NULL                | NULL                | utf8_general_ci   |     NULL | max_rows=72628     |                                            |
...
| def           | zentao             | zt_usertpl                                           | BASE TABLE  | MyISAM             |      10 | Dynamic    |          0 |              0 |           0 |    281474976710655 |         1024 |         0 |              1 | 2017-08-16 16:36:45 | 2017-08-16 16:36:45 | NULL                | utf8_general_ci   |     NULL |                    |                                            |
+---------------+--------------------+------------------------------------------------------+-------------+--------------------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+--------------------------------------------+
525 rows in set (3.03 sec)

mysql> SHOW TABLES FROM zentao;
+-------------------+
| Tables_in_zentao  |
+-------------------+
| zt_action         |
| zt_block          |
| zt_branch         |
...
| zt_usertpl        |
+-------------------+
48 rows in set (0.00 sec)

COLUMNS 表

存储表中的列信息,包括表有多少列、每个列的类型等。SHOW COLUMNS FROM schemaname.tablename 命令从这个表获取结果。

mysql> SELECT * FROM COLUMNS LIMIT 2,5;
+---------------+--------------------+----------------+--------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-------------+------------+-------+------------+----------------+-----------------------+
| 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           | information_schema | CHARACTER_SETS | DESCRIPTION        |                3 |                | NO          | varchar   |                       60 |                    180 |              NULL |          NULL |               NULL | utf8               | utf8_general_ci | varchar(60) |            |       | select     |                |                       |
| def           | information_schema | CHARACTER_SETS | MAXLEN             |                4 | 0              | NO          | bigint    |                     NULL |                   NULL |                19 |             0 |               NULL | NULL               | NULL            | bigint(3)   |            |       | select     |                |                       |
| def           | information_schema | COLLATIONS     | COLLATION_NAME     |                1 |                | NO          | varchar   |                       32 |                     96 |              NULL |          NULL |               NULL | utf8               | utf8_general_ci | varchar(32) |            |       | select     |                |                       |
| def           | information_schema | COLLATIONS     | CHARACTER_SET_NAME |                2 |                | NO          | varchar   |                       32 |                     96 |              NULL |          NULL |               NULL | utf8               | utf8_general_ci | varchar(32) |            |       | select     |                |                       |
| def           | information_schema | COLLATIONS     | ID                 |                3 | 0              | NO          | bigint    |                     NULL |                   NULL |                19 |             0 |               NULL | NULL               | NULL            | bigint(11)  |            |       | select     |                |                       |
+---------------+--------------------+----------------+--------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-------------+------------+-------+------------+----------------+-----------------------+
5 rows in set (0.08 sec)

STATISTICS 表

表索引的信息。SHOW INDEX FROM schemaname.tablename; 命令从这个表获取结果。

mysql> SHOW INDEX FROM szhuizhong.users;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY       |            1 | UserID      | A         |        1460 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | Account_index |            1 | Account     | A         |        1460 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | CorpID        |            1 | FromID      | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

USER_PRIVILEGES 表

用户权限表。内容源自 mysql.user 授权表。是非标准表。

mysql> 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          |
| 'root'@'%'              | def           | DELETE                  | YES          |
| 'root'@'%'              | def           | CREATE                  | YES          |
| 'root'@'%'              | def           | DROP                    | YES          |
| 'root'@'%'              | def           | RELOAD                  | YES          |
| 'root'@'%'              | def           | SHUTDOWN                | YES          |
| 'root'@'%'              | def           | PROCESS                 | YES          |
| 'root'@'%'              | def           | FILE                    | YES          |
| 'root'@'%'              | def           | REFERENCES              | YES          |
| 'root'@'%'              | def           | INDEX                   | YES          |
| 'root'@'%'              | def           | ALTER                   | YES          |
| 'root'@'%'              | def           | SHOW DATABASES          | YES          |
| 'root'@'%'              | def           | SUPER                   | YES          |
| 'root'@'%'              | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'%'              | def           | LOCK TABLES             | YES          |
| 'root'@'%'              | def           | EXECUTE                 | YES          |
| 'root'@'%'              | def           | REPLICATION SLAVE       | YES          |
| 'root'@'%'              | def           | REPLICATION CLIENT      | YES          |
| 'root'@'%'              | def           | CREATE VIEW             | YES          |
| 'root'@'%'              | def           | SHOW VIEW               | YES          |
| 'root'@'%'              | def           | CREATE ROUTINE          | YES          |
| 'root'@'%'              | def           | ALTER ROUTINE           | YES          |
| 'root'@'%'              | def           | CREATE USER             | YES          |
| 'root'@'%'              | def           | EVENT                   | YES          |
| 'root'@'%'              | def           | TRIGGER                 | YES          |
| 'root'@'%'              | def           | CREATE TABLESPACE       | YES          |
+-------------------------+---------------+-------------------------+--------------+
29 rows in set (0.00 sec)

SCHEMA_PRIVILEGES 表

方案权限表。给出了关于方案(数据库)权限的信息。内容来自 mysql.db 授权表。是非标准表。

mysql> SELECT * FROM SCHEMA_PRIVILEGES;
+-------------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE                 | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE          | IS_GRANTABLE |
+-------------------------+---------------+--------------+-------------------------+--------------+
| 'mysql.sys'@'localhost' | def           | sys          | TRIGGER                 | NO           |
| 'root'@'%'              | def           | mysql        | SELECT                  | YES          |
| 'root'@'%'              | def           | mysql        | INSERT                  | YES          |
| 'root'@'%'              | def           | mysql        | UPDATE                  | YES          |
| 'root'@'%'              | def           | mysql        | DELETE                  | YES          |
| 'root'@'%'              | def           | mysql        | CREATE                  | YES          |
| 'root'@'%'              | def           | mysql        | DROP                    | YES          |
| 'root'@'%'              | def           | mysql        | REFERENCES              | YES          |
| 'root'@'%'              | def           | mysql        | INDEX                   | YES          |
| 'root'@'%'              | def           | mysql        | ALTER                   | YES          |
| 'root'@'%'              | def           | mysql        | CREATE TEMPORARY TABLES | YES          |
| 'root'@'%'              | def           | mysql        | LOCK TABLES             | YES          |
| 'root'@'%'              | def           | mysql        | EXECUTE                 | YES          |
| 'root'@'%'              | def           | mysql        | CREATE VIEW             | YES          |
| 'root'@'%'              | def           | mysql        | SHOW VIEW               | YES          |
| 'root'@'%'              | def           | mysql        | CREATE ROUTINE          | YES          |
| 'root'@'%'              | def           | mysql        | ALTER ROUTINE           | YES          |
| 'root'@'%'              | def           | mysql        | EVENT                   | YES          |
| 'root'@'%'              | def           | mysql        | TRIGGER                 | YES          |
+-------------------------+---------------+--------------+-------------------------+--------------+
19 rows in set (0.00 sec)

TABLE_PRIVILEGES 表

表权限表。给出了关于表权限的信息。内容源自 mysql.tables_priv 授权表。是非标准表。

mysql> 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.00 sec)

COLUMN_PRIVILEGES 表

列权限表。给出了关于列权限的信息。内容源自 mysql.columns_priv 授权表。是非标准表。

mysql> SELECT * FROM COLUMN_PRIVILEGES;
Empty set (0.00 sec)

COLLATIONS 表

提供了关于各字符集的对照信息。SHOW COLLATION; 命令从这个表获取结果。

mysql> SELECT * FROM COLLATIONS;
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME           | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------------+--------------------+-----+------------+-------------+---------+
| big5_chinese_ci          | big5               |   1 | Yes        | Yes         |       1 |
| big5_bin                 | big5               |  84 |            | Yes         |       1 |
| dec8_swedish_ci          | dec8               |   3 | Yes        | Yes         |       1 |
| dec8_bin                 | dec8               |  69 |            | Yes         |       1 |
...
| gb18030_bin              | gb18030            | 249 |            | Yes         |       1 |
| gb18030_unicode_520_ci   | gb18030            | 250 |            | Yes         |       8 |
+--------------------------+--------------------+-----+------------+-------------+---------+
222 rows in set (0.03 sec)

COLLATION_CHARACTER_SET_APPLICABILITY 表

指明了可用于校对的字符集。相当于 SHOW COLLATION 命令结果的前两个字段。

mysql> SELECT * FROM COLLATION_CHARACTER_SET_APPLICABILITY;
+--------------------------+--------------------+
| COLLATION_NAME           | CHARACTER_SET_NAME |
+--------------------------+--------------------+
| big5_chinese_ci          | big5               |
| big5_bin                 | big5               |
| dec8_swedish_ci          | dec8               |
...
| gb18030_bin              | gb18030            |
| gb18030_unicode_520_ci   | gb18030            |
+--------------------------+--------------------+
222 rows in set (0.00 sec)

TABLE_CONSTRAINTS 表

描述了存在约束的表。以及表的约束类型。

mysql> SELECT * FROM TABLE_CONSTRAINTS;
+--------------------+-------------------+--------------------+--------------+---------------------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME    | TABLE_SCHEMA | TABLE_NAME                | CONSTRAINT_TYPE |
+--------------------+-------------------+--------------------+--------------+---------------------------+-----------------+
| def                | mysql             | PRIMARY            | mysql        | columns_priv              | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | db                        | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | engine_cost               | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | event                     | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | func                      | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | gtid_executed             | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | help_category             | PRIMARY KEY     |
| def                | mysql             | name               | mysql        | help_category             | UNIQUE          |
| def                | mysql             | PRIMARY            | mysql        | help_keyword              | PRIMARY KEY     |
| def                | mysql             | name               | mysql        | help_keyword              | UNIQUE          |
| def                | mysql             | PRIMARY            | mysql        | help_relation             | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | help_topic                | PRIMARY KEY     |
| def                | mysql             | name               | mysql        | help_topic                | UNIQUE          |
| def                | mysql             | PRIMARY            | mysql        | innodb_index_stats        | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | innodb_table_stats        | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | ndb_binlog_index          | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | plugin                    | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | proc                      | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | procs_priv                | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | proxies_priv              | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | server_cost               | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | servers                   | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | slave_master_info         | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | slave_relay_log_info      | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | slave_worker_info         | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | tables_priv               | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | time_zone                 | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | time_zone_leap_second     | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | time_zone_name            | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | time_zone_transition      | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | time_zone_transition_type | PRIMARY KEY     |
| def                | mysql             | PRIMARY            | mysql        | user                      | PRIMARY KEY     |
| def                | sys               | PRIMARY            | sys          | sys_config                | PRIMARY KEY     |
| def                | zentao            | PRIMARY            | zentao       | zt_action                 | PRIMARY KEY     |
...
| def                | zentao            | account            | zentao       | zt_usergroup              | UNIQUE          |
| def                | zentao            | PRIMARY            | zentao       | zt_userquery              | PRIMARY KEY     |
| def                | zentao            | PRIMARY            | zentao       | zt_usertpl                | PRIMARY KEY     |
+--------------------+-------------------+--------------------+--------------+---------------------------+-----------------+
213 rows in set (0.37 sec)

KEY_COLUMN_USAGE 表

描述了具有约束的键列。

mysql> SELECT * FROM KEY_COLUMN_USAGE;
+--------------------+-------------------+--------------------+---------------+--------------+---------------------------+--------------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME    | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME                | COLUMN_NAME        | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------------+-------------------+--------------------+---------------+--------------+---------------------------+--------------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| def                | mysql             | PRIMARY            | def           | mysql        | columns_priv              | Host               |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | mysql             | PRIMARY            | def           | mysql        | columns_priv              | Db                 |                2 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | mysql             | PRIMARY            | def           | mysql        | columns_priv              | User               |                3 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | mysql             | PRIMARY            | def           | mysql        | columns_priv              | Table_name         |                4 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | mysql             | PRIMARY            | def           | mysql        | columns_priv              | Column_name        |                5 |                          NULL | NULL                    | NULL                  | NULL                   |
...
| def                | mysql             | PRIMARY            | def           | mysql        | time_zone_leap_second     | Transition_time    |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | mysql             | PRIMARY            | def           | mysql        | time_zone_name            | Name               |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | mysql             | PRIMARY            | def           | mysql        | time_zone_transition      | Time_zone_id       |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | mysql             | PRIMARY            | def           | mysql        | time_zone_transition      | Transition_time    |                2 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | mysql             | PRIMARY            | def           | mysql        | time_zone_transition_type | Time_zone_id       |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | mysql             | PRIMARY            | def           | mysql        | time_zone_transition_type | Transition_type_id |                2 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | mysql             | PRIMARY            | def           | mysql        | user                      | Host               |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | mysql             | PRIMARY            | def           | mysql        | user                      | User               |                2 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | sys               | PRIMARY            | def           | sys          | sys_config                | variable           |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
+--------------------+-------------------+--------------------+---------------+--------------+---------------------------+--------------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
278 rows in set (0.03 sec)

ROUTINES 表

提供了关于存储子程序(存储程序和函数)的信息。此时,ROUTINES 表不包含自定义函数(UDF)。名为“mysql.proc name”的列指明了对应于 INFORMATION_SCHEMA.ROUTINES 表的 mysql.proc 列。

VIEWS 表

给出了关于数据库中的视图的信息。需要有 show views 权限,否则无法查看视图信息。

mysql> SELECT * FROM VIEWS LIMIT 1\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: sys
          TABLE_NAME: host_summary
     VIEW_DEFINITION: select if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`) AS `host`,sum(`stmt`.`total`) AS `statements`,`sys`.`format_time`(sum(`stmt`.`total_latency`)) AS `statement_latency`,`sys`.`format_time`(ifnull((sum(`stmt`.`total_latency`) / nullif(sum(`stmt`.`total`),0)),0)) AS `statement_avg_latency`,sum(`stmt`.`full_scans`) AS `table_scans`,sum(`io`.`ios`) AS `file_ios`,`sys`.`format_time`(sum(`io`.`io_latency`)) AS `file_io_latency`,sum(`performance_schema`.`accounts`.`CURRENT_CONNECTIONS`) AS `current_connections`,sum(`performance_schema`.`accounts`.`TOTAL_CONNECTIONS`) AS `total_connections`,count(distinct `performance_schema`.`accounts`.`USER`) AS `unique_users`,`sys`.`format_bytes`(sum(`mem`.`current_allocated`)) AS `current_memory`,`sys`.`format_bytes`(sum(`mem`.`total_allocated`)) AS `total_memory_allocated` from (((`performance_schema`.`accounts` join `sys`.`x$host_summary_by_statement_latency` `stmt` on((`performance_schema`.`accounts`.`HOST` = `stmt`.`host`))) join `sys`.`x$host_summary_by_file_io` `io` on((`performance_schema`.`accounts`.`HOST` = `io`.`host`))) join `sys`.`x$memory_by_host_by_current_bytes` `mem` on((`performance_schema`.`accounts`.`HOST` = `mem`.`host`))) group by if(isnull(`performance_schema`.`accounts`.`HOST`),'background',`performance_schema`.`accounts`.`HOST`)
        CHECK_OPTION: NONE
        IS_UPDATABLE: NO
             DEFINER: mysql.sys@localhost
       SECURITY_TYPE: INVOKER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.03 sec)

TRIGGERS 表

提供了关于触发程序的信息。必须有 super 权限才能查看该表。

mysql> SELECT * FROM TRIGGERS LIMIT 1\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: sys
              TRIGGER_NAME: sys_config_insert_set_user
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: sys
        EVENT_OBJECT_TABLE: sys_config
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2017-05-27 11:18:43.60
                  SQL_MODE: 
                   DEFINER: mysql.sys@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)

到此这篇关于MySQL之information_schema数据库详细讲解的文章就介绍到这了,更多相关MySQL之information_schema数据库内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞!

[!--infotagslink--]

相关文章

  • MySQL性能监控软件Nagios的安装及配置教程

    这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
  • 详解Mysql中的JSON系列操作函数

    新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
  • 深入研究mysql中的varchar和limit(容易被忽略的知识)

    为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15
  • MySQL 字符串拆分操作(含分隔符的字符串截取)

    这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
  • mysql的3种分表方案

    一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
  • Windows服务器MySQL中文乱码的解决方法

    我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
  • Centos5.5中安装Mysql5.5过程分享

    这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
  • 用VirtualBox构建MySQL测试环境

    宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
  • 忘记MYSQL密码的6种常用解决方法总结

    首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
  • MySQL数据库备份还原方法

    MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
  • Navicat for MySQL 11注册码\激活码汇总

    Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
  • Mysql命令大全(详细篇)

    一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
  • 基于PostgreSQL和mysql数据类型对比兼容

    这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
  • Mysql中 show table status 获取表信息的方法

    这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12
  • mysql IS NULL使用索引案例讲解

    这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
  • 20分钟MySQL基础入门

    这篇文章主要为大家分享了20分钟MySQL基础入门教程,快速掌握MySQL基础知识,真正了解MySQL,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2016-12-02
  • RHEL6.5编译安装MySQL5.6.26教程

    一、准备编译环境,安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21
  • mongodb与mysql命令详细对比

    传统的关系数据库一般由数据库(database)、表(table)、记录(record)三个层次概念组成,MongoDB是由数据库(database)、集合(collection)、文档对象(document)三个层次组成。MongoDB对于关系型数据库里的表,但是集合中没有列、行和关...2013-09-11
  • MySQL远程连接不上的解决方法

    这篇文章主要为大家详细介绍了MySQL远程连接不上的解决方法,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2017-01-26
  • Delphi远程连接Mysql的实现方法

    这篇文章主要介绍了Delphi远程连接Mysql的实现方法,需要的朋友可以参考下...2020-06-30