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 ;

  转载地址：https://blog.csdn.net/u013850277/article/details/78585067