mysql中从系统表中查询表我想要的信息

mysql中 INFORMATION_SCHEMA.TABLES表存放了所有表的元数据信息，例如表名，所有，记录数等等

下面给出查询相关重要信息的sql:

1， 查询mobile数据库表空间大小

select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_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 where TABLE_SCHEMA = 'mobile';

2， 查询那些表是占空间的

Select
   Concat(table_schema, '.', table_name) As "Name"
  ,Concat(Round(table_rows / 1000000, 2), 'M') As "Rows"
  ,Concat(Round(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') As "Row Size"
  ,Concat(Round(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') As "Index Size"
  ,Concat(Round(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') As "Total"
  ,Round(index_length / data_length, 2) "Row / Index Ratio"
From information_schema.TABLES
Order By data_length + index_length DESC
Limit 10;

3， 根据表名称模糊查询，例如我查询那些表名里面带有push的：

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '%push%')

4， 根据字段名称模糊查询，例如我查询那些表里面字段带有open_id的

SELECT Table_Name, Column_Name
FROM Information_schema.Columns WHERE Column_Name LIKE '%open_id%' AND Table_Schema = 'mobile';

5， 根据记录行数查询前20名的

select table_schema,table_name,table_rows from information_schema.tables order by table_rows desc limit 20;

6， 根据大小查询前20名的

select table_name, concat(round(sum(DATA_LENGTH/1024/1024),2),'MB')as data,round(sum(DATA_LENGTH/1024/1024),2)as total
from information_schema.TABLES where table_schema='mobile'
group by table_name
order by total desc
limit 20;

7， 根据索引大小查询前20名的

select table_name, concat(round(sum(DATA_LENGTH/1024/1024),2),'MB')as data,round(sum(INDEX_LENGTH/1024/1024),2)as total
from information_schema.TABLES where table_schema='mobile'
group by table_name
order by total desc
limit 20;

8， 更多的我会继续补充。