常用mysql语句(查看所有数据库各表容量大小)

查看进程

show full processlist

杀死进程

kill  进程号

查看所有数据库各表容量大小

select 
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

查看所有数据库各表记录数、容量大小

select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

查询表字段中文注释

SELECT "列名","数据类型","字段类型","长度","是否为空","默认值","备注"  
FROM  dual 
union all 
SELECT COLUMN_NAME 列名,COLUMN_TYPE 数据类型,DATA_TYPE 字段类型,CHARACTER_MAXIMUM_LENGTH 长度,IS_NULLABLE 是否为空,   COLUMN_DEFAULT 默认值,COLUMN_COMMENT 备注  FROM  INFORMATION_SCHEMA.COLUMNS where 
-- developerclub为数据库名称，到时候只需要修改成你要导出表结构的数据库即可
 table_schema ='hanying' AND 
 -- article为表名，到时候换成你要导出的表的名称 -- 如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了，所以还是建议写上要导出的名名称 
 table_name  = 'hanying'