数据库中INFORMATION_SCHEMA的说明及使用

参考

http://blog.163.com/freestyle_le/blog/static/1832794482011713103747931/

useinformation_schema

mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| CLIENT_STATISTICS                     |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| INDEX_STATISTICS                      |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_TEMPORARY_TABLES               |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| QUERY_RESPONSE_TIME                   |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TABLE_STATISTICS                      |
| TEMPORARY_TABLES                      |
| THREAD_STATISTICS                     |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| USER_STATISTICS                       |
| VIEWS                                 |
| INNODB_SYS_COLUMNS                    |
| INNODB_RSEG                           |
| INNODB_CMP                            |
| INNODB_TRX                            |
| INNODB_SYS_TABLESTATS                 |
| INNODB_LOCK_WAITS                     |
| XTRADB_ADMIN_COMMAND                  |
| INNODB_LOCKS                          |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_CMP_RESET                      |
| INNODB_BUFFER_POOL_PAGES              |
| INNODB_SYS_TABLES                     |
| INNODB_BUFFER_POOL_PAGES_INDEX        |
| INNODB_CMPMEM                         |
| INNODB_BUFFER_POOL_PAGES_BLOB         |
| INNODB_CMPMEM_RESET                   |
| INNODB_SYS_FIELDS                     |
| INNODB_TABLE_STATS                    |
| INNODB_SYS_STATS                      |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_INDEXES                    |
| INNODB_INDEX_STATS                    |
+---------------------------------------+

1、KEY_COLUMN_USAGE表:描述了具有约束的键列。

select * from KEY_COLUMN_USAGE limit 10;
+--------------------+-------------------+-----------------+---------------+--------------+-----------------------------------------+-----------------------------------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| 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                | control_stat      | PRIMARY         | def           | control_stat | t_menu                                  | pk_menu                                 |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | control_stat      | PRIMARY         | def           | control_stat | t_result_control_playerflowrate_current | pk_control_current                      |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | control_stat      | PRIMARY         | def           | control_stat | t_result_control_playerflowrate_detail  | pk_control_detail                       |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | control_stat      | PRIMARY         | def           | control_stat | t_result_control_playerflowrate_history | pk_control_history                      |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | control_stat      | PRIMARY         | def           | control_stat | t_result_js_error_detail                | pk_result_js_error_detail               |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | control_stat      | PRIMARY         | def           | control_stat | t_result_web_plugin_send_speed_current  | pk_result_web_plugin_send_speed_current |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | control_stat      | PRIMARY         | def           | control_stat | t_result_web_plugin_system_current      | pk_result_web_plugin_system_current     |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | control_stat      | PRIMARY         | def           | control_stat | t_role                                  | pk_role                                 |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | control_stat      | PRIMARY         | def           | control_stat | t_role_menu                             | pk_role_menu                            |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
| def                | control_stat      | PRIMARY         | def           | control_stat | t_user                                  | pk_user                                 |                1 |                          NULL | NULL                    | NULL                  | NULL                   |
+--------------------+-------------------+-----------------+---------------+--------------+-----------------------------------------+-----------------------------------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
10 rows in set (6.44 sec)

2、STATISTICS表:给出了关于表索引的信息。

mysql> select * from STATISTICS limit 10;
+---------------+--------------+-----------------------------------------+------------+--------------+-----------------------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
| 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           | control_stat | t_menu                                  |          0 | control_stat | PRIMARY                                             |            1 | pk_menu            | A         |           9 |     NULL | NULL   |          | BTREE      |         |               |
| def           | control_stat | t_result_control_playerflowrate_current |          0 | control_stat | PRIMARY                                             |            1 | pk_control_current | A         |           0 |     NULL | NULL   |          | BTREE      |         |               |
| def           | control_stat | t_result_control_playerflowrate_current |          1 | control_stat | i_result_control_playerflowrate_current_roomid      |            1 | roomid             | A         |           0 |     NULL | NULL   |          | BTREE      |         |               |
| def           | control_stat | t_result_control_playerflowrate_current |          1 | control_stat | i_result_control_playerflowrate_current_asnid       |            1 | asnid              | A         |           0 |     NULL | NULL   |          | BTREE      |         |               |
| def           | control_stat | t_result_control_playerflowrate_current |          1 | control_stat | i_result_control_playerflowrate_current_provinceid  |            1 | provinceid         | A         |           0 |     NULL | NULL   |          | BTREE      |         |               |
| def           | control_stat | t_result_control_playerflowrate_detail  |          0 | control_stat | PRIMARY                                             |            1 | pk_control_detail  | A         |           0 |     NULL | NULL   |          | BTREE      |         |               |
| def           | control_stat | t_result_control_playerflowrate_detail  |          1 | control_stat | t_result_control_playerflowrate_detail_roomid       |            1 | roomid             | A         |           0 |     NULL | NULL   |          | BTREE      |         |               |
| def           | control_stat | t_result_control_playerflowrate_detail  |          1 | control_stat | t_result_control_playerflowrate_detail_provinceid   |            1 | provinceid         | A         |           0 |     NULL | NULL   |          | BTREE      |         |               |
| def           | control_stat | t_result_control_playerflowrate_detail  |          1 | control_stat | t_result_control_playerflowrate_detail_asn          |            1 | asn                | A         |           0 |     NULL | NULL   |          | BTREE      |         |               |
| def           | control_stat | t_result_control_playerflowrate_detail  |          1 | control_stat | idx_result_control_playerflowrate_detail_min_roomid |            1 | minutecol          | A         |           0 |     NULL | NULL   |          | BTREE      |         |               |
+---------------+--------------+-----------------------------------------+------------+--------------+-----------------------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+

相关推荐