库大小统计

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 distinct CONCAT(‘ALTER TABLE oms.’,TABLE_NAME,’ DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;’)
from information_schema.tables where TABLE_SCHEMA =‘oms’ and table_collation =‘utf8mb3’

批量修改字段字符集代码构造

select CONCAT(‘ALTER TABLE oms.',TABLE_NAME,' modify column ',column_name,' ‘,COLUMN_TYPE,’ CHARACTER SET utf8mb4 COLLATE utf8mb4_bin’,CASE WHEN IS_NULLABLE=‘NO’ THEN ’ NOT NULL’ ELSE ’ DEFAULT NULL’ END,’ COMMENT ‘’’,COLUMN_COMMENT,’’’;’)
from information_schema.columns where TABLE_SCHEMA =‘oms’ and CHARACTER_SET_NAME =‘utf8mb3’

导出脚本构造

select CONCAT(’/home/mysql/bin/mysqldump -h192.168.xx.197 -uroot -p密码 -S/home/mysql/mysql.sock --set-gtid-purged=OFF dzfp_zzs_kpfw_arm ‘,table_name,’>/home/backup/mysql/backup20221117/’,table_name,’.sql’) from information_schema.tables where table_schema=‘dzfp_zzs_kpfw_arm’

导入脚本构造

select CONCAT(’/home/mysql/bin/mysql -uroot -p密码 -P3306 -S/home/mysql/mysql.sock dzfp_zzs_kpfw_arm_c4 ‘,’</home/backup/mysql/backup20221117/’,table_name,’.sql’) from information_schema.tables where table_schema=‘dzfp_zzs_kpfw_arm’

获取元数据信息代码

select
a.table_schema as “库名称”,
a.table_name as “表名称”,
b.table_comment as “表注释”,
a.ordinal_position as “字段序号”,
a.column_name as “字段名称”,
a.column_comment as “字段注释”,
a.column_type as “字段类型”,
a.column_default as “字段默认值”,
is_nullable as “是否允许为空”
from information_schema.COLUMNS a, information_schema.TABLES b
where a.table_schema not in(‘information_schema’,‘performance_schema’,‘sys’,‘mysql’)
and a.table_schema=b.TABLE_SCHEMA and a.TABLE_NAME=b.TABLE_NAME
order by a.table_schema,a.table_name,a.ordinal_position