1、查看数据库的基本信息
mysql> SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'db' [AND TABLE_NAME = 'table'];
+---------------+--------------+------------+------------+--------+---------+------------+------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS |
+---------------+--------------+------------+------------+--------+---------+------------+------------+
| def | db | table | BASE TABLE | InnoDB | 10 | Compact | 0 |
+---------------+--------------+------------+------------+--------+---------+------------+------------+
+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+
| AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME |
+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+
| 0 | 16384 | 0 | 0 | 0 | 1 | 2013-12-25 16:17:29 |
+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+
+-------------+------------+-----------------+----------+----------------+---------------+
| UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+-------------+------------+-----------------+----------+----------------+---------------+
| NULL | NULL | utf8_general_ci | NULL | | |
+-------------+------------+-----------------+----------+----------------+---------------+
1 row in set (0.07 sec)
2、查看数据库的大小
mysql> SELECT (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) AS size FROM information_schema.TABLES WHERE TABLE_SCHEMA='push';
+---------+
| size |
+---------+
| 8830976 |
+---------+
1 row in set (0.00 sec)
说明:得到的结果是以字节为单位,除以1024为K,除以1024*1024(=1048576)为M。
3、查看数据库的修改时间
SELECT UPDATE_TIME FROM information_schema.TABLES WHERE TABLE_SCHEMA='db' ORDER BY UPDATE_TIME DESC LIMIT 1;
SELECT UPDATE_TIME FROM information_schema.TABLES WHERE TABLE_SCHEMA='db' and TABLE_NAME = 'table';
说明:MyISAM类型表才支持,其他类型(如InnoDB)查出来的修改时间为null