数据盘点查询语句

use lk_clud;

/*数据大小和索引大小   TABLE_SCHEMA改为数据库名即可*/
/*table_size 通常指的是一个表（table）在存储介质上所占用的总空间大小。这个大小通常包括表的数据部分（data）和索引部分（index），有时还可能包括其他与表相关的元数据或存储开销
*/
SELECT TABLE_NAME,
       CONCAT(TRUNCATE(SUM(data_length) / 1024 / 1024, 2), ' MB') AS data_size,
       CONCAT(TRUNCATE(SUM(index_length) / 1024 / 1024, 2), ' MB') AS index_size,
       CONCAT(TRUNCATE((SUM(data_length) + SUM(index_length)) / 1024 / 1024, 2), ' MB') AS table_size
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'lk_clud'
GROUP BY TABLE_NAME
ORDER BY SUM(data_length) DESC;


/*建表时间   table_name改为数据表名即可*/
SELECT date_format(create_time, '%Y年%m月%d日 %H:%i:%s') AS create_time,table_name
FROM information_schema.tables
WHERE table_schema = database() AND table_name = 'auth_user';


/*一个库的数据  table_schema改为数据库名即可*/
select
table_schema as '数据库',
table_name as '表名',
table_comment as '表注释',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='lk_clud'
order by table_rows desc, index_length desc;