Mysql 查看库中各个表数据量、创建更新时间等情况

来源：互联网发布：北京工业大学软件编辑：程序博客网时间：2024/06/04 19:51

mysql> USE information_schema;

查看 TABLES表属性如下：

mysql> DESC 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   |     |         |       |+-----------------+---------------------+------+-----+---------+-------+21 rows in set (0.00 sec)从上面该表结构，我们可以很容易查出该表创建时间、更新时间、表条数等，下面以表数据量与创建时间为例说明。

把test库中所以表数据量以及对应表的创建时间查出来

SELECT table_schema,table_name,table_rows,CREATE_TIME FROM TABLES WHERE TABLE_SCHEMA='test'  ORDER BY table_rows DESC;

把test库中所有为空的表

SELECT table_schema,table_name,table_rows FROM TABLES WHERE TABLE_SCHEMA='test'  AND table_rows = 0 ORDER BY table_name ;

阅读全文

0 0