select CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) as 数据表名,concat(truncate(sum(DATA_LENGTH+DATA_FREE+INDEX_LENGTH)/1024/1024,2),' MB') as total_size, concat(truncate(sum(DATA_LENGTH)/1024/1024,2),' MB') as data_size,concat(truncate(sum(DATA_FREE)/1024/1024,2),' MB') as data_free, concat(truncate(sum(INDEX_LENGTH)/1024/1024,2),'MB') as index_size from information_schema.tables group by TABLE_NAME order by data_length desc;
或者使用select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;也可以,这个是查询data_free大于0的所有表。
然后看到我这个叫history_device_flow_day的表里情况如下: