在数据库管理中,经常会需要统一数据库字符集(处理索引匹配问题、不同排序规则的表关联报错问题等)
这里统一设置为utf8mb4编码、utf8mb4_general_ci排序规则
一下是生成的执行语句,生成后需要在数据库中批量执行
1、修改表结构 字符集
-- 修改表结构字符集语句
SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', TABLE_NAME, ' COLLATE=utf8mb4_general_ci;') '修正SQL'
FROM information_schema.`TABLES`
WHERE TABLE_COLLATION not RLIKE 'mb4' and TABLE_SCHEMA = '要修改的数据库名';
2、批量修改表结构字符集(这里要慎重处理,最好现在测试环境测试完毕之后。再去更改生产环境),要注意表结构和默认值的处理,如果update语句只更改字段的字符集会丢失默认值、是否费控、备注等信息(前面遇到了这个坑,完善了一下生成语句)
--修改表字段字符集语句
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' MODIFY COLUMN `',COLUMN_NAME,'` ',COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci '
,if(IS_NULLABLE = 'YES',' NULL ',' NOT NULL')
,if(COLUMN_DEFAULT is not null ,concat(' DEFAULT ',if(data_type like '%char%',concat("'",COLUMN_DEFAULT,"'"),COLUMN_DEFAULT)),' ')
,if(COLUMN_COMMENT is not null ,concat(' COMMENT "',COLUMN_COMMENT,'"'),' ')
,';') '修正SQL'
FROM information_schema.`COLUMNS`
WHERE COLLATION_NAME != 'utf8mb4_general_ci' and TABLE_SCHEMA = '要修改的数据库名';
生成完sql语句之后 批量执行一下就可以了。遇到大表执行会比较慢,谨慎处理~