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