手机版 欢迎访问it开发者社区(www.mfbz.cn)网站

当前位置: > 开发

MySQL 表(表名含有大写字母的所有表)

时间:2021/8/3 1:27:23|来源:|点击: 次

表名含有大写字母的所有表:

select table_name 
from information_schema.tables 
where table_schema='databasename' and binary table_name REGEXP '[A-Z]';

有json格式的表:

 select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.columns where DATA_TYPE like 'json' and TABLE_SCHEMA not in ('sys','mysql','information_schema','performance_schema');

表大小:

select TABLE_SCHEMA,TABLE_TYPE,ENGINE,TABLE_NAME,ROUND((DATA_LENGTH+INDEX_LENGTH+DATA_FREE)/1024/1024/1024) TOTAL_GB 
from information_schema.tables 
where TABLE_SCHEMA='databasename' 
order by TOTAL_GB desc limit 10;

表数量:

select TABLE_SCHEMA,count(TABLE_NAME) from tables where TABLE_SCHEMA not in ('mysql','information_schema','performation_schema','sys') group by TABLE_SCHEMA;

分区数量:

select TABLE_SCHEMA,TABLE_NAME,count(PARTITION_NAME) from PARTITIONS where TABLE_SCHEMA not in ('mysql','information_schema','performation_schema','sys') group by TABLE_SCHEMA,TABLE_NAME;

查询大于1G无索引表:

SELECT TABLE_SCHEMA, TABLE_NAME, total_gb 
FROM (select T.TABLE_SCHEMA,T.TABLE_NAME,round(((data_length + index_length + data_free) / 1024 / 1024 / 1024),2) AS total_gb from TABLES T where T.TABLE_TYPE = 'BASE TABLE' AND T.TABLE_SCHEMA NOT IN ('SYS', 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')) T 
WHERE total_gb > 1 
order by total_gb desc;

Copyright © 2002-2019 某某自媒体运营 版权所有