库大小统计
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 ‘,’
