对mysql的information_schema库的一些认知

information_schema库中表的信息如下所示：

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                      |
| 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_BUFFER_PAGE                    |
| INNODB_TRX                            |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_LOCK_WAITS                     |
| INNODB_CMPMEM                         |
| INNODB_CMP                            |
| INNODB_LOCKS                          |
| INNODB_CMPMEM_RESET                   |
| INNODB_CMP_RESET                      |
| INNODB_BUFFER_PAGE_LRU                |
+---------------------------------------+

查看表名和表行数

select table_name,table_rows from information_schema.tables where TABLE_SCHEMA='dbname' order by table_rows;

如上所示查询的是information_schema.tables 表信息，TABLE_SCHEMA中的dbname是指你要查询的表所在的数据库名，根据表行数从小到大排序。

tables表字段

tables表的字段如下所示，可根据字段查询相应的信息：

mysql > desc information_schema.tables;

+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
|TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |   表所在的数据库名
|TABLE_NAME      | varchar(64)         | NO   |     |         |       |   表名字
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
|ENGINE          | varchar(64)         | YES  |     | NULL    |       |   表引擎
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
|TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |   表行数
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
|DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |   表数据大小
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
|INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |   表索引大小
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+