说明
-
文章内容适用于 Mysql5.7、Mysql 8.0，其它版本可自行验证。
-
数据库、表、字段的字符集排序规则修改为：
utf8mb4和utf8mb4_general_ci。 -
Mysql表中字符类型的字段长度为支持的字符个数，修改字符集不需要增加字段长度。
-
经验证，字符集的变更不需要重新创建索引。
1.若库的字符集需修改：
整理留存相关库原字符集SQL，以备回滚使用：
ALTER DATABASE [库名] CHARACTER SET [原字符集] COLLATE [原排序规则];
执行以下SQL修改指定库的字符集：
ALTER DATABASE [库名] CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
2.执行以下SQL并记录表原字符集SQL和表需修改字符集SQL
SELECTDISTINCTtable_schema,table_name,character_set_name,collation_name,CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' CONVERT TO CHARACTER SET \'', character_set_name, '\' COLLATE \'', collation_name, '\';') '表原字符集SQL',CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' CONVERT TO CHARACTER SET \'utf8mb4\' COLLATE \'utf8mb4_general_ci\';') '表需修改字符集SQL'
FROMinformation_schema.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('mysql','performance_schema','sys','information_schema','mysql_ha','mysql_db_monitor')AND COLLATION_NAME IS NOT NULL AND COLLATION_NAME != 'utf8mb4_general_ci';
注：
表原字符集SQL用于回滚备份。表需修改字符集SQL用于执行表整体字符集修改。
3.执行以下SQL并记录字段原字符集SQL
SELECTTABLE_SCHEMA '数据库',TABLE_NAME '表',COLUMN_NAME '字段',CHARACTER_SET_NAME '原字符集',COLLATION_NAME '原排序规则',CONCAT('ALTER TABLE ',TABLE_SCHEMA, '.', TABLE_NAME,' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET ', CHARACTER_SET_NAME, ' COLLATE ', COLLATION_NAME, ( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),( CASE WHEN COLUMN_COMMENT = '' THEN ' ' ELSE concat( ' COMMENT''', COLUMN_COMMENT, '''' ) END ),';' ) '字段原字符集SQL',CONCAT('ALTER TABLE ',TABLE_SCHEMA, '.', TABLE_NAME,' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),( CASE WHEN COLUMN_COMMENT = '' THEN ' ' ELSE concat( ' COMMENT''', COLUMN_COMMENT, '''' ) END ),';' ) '字段需修正字符集SQL'
FROM information_schema.`COLUMNS` WHERE 1=1AND COLLATION_NAME != 'utf8mb4_general_ci'AND TABLE_SCHEMA NOT IN ('mysql','performance_schema','sys','information_schema','mysql_ha','mysql_db_monitor');
注： 字段原字符集SQL用于回滚备份。
4.执行修正SQL
逐条执行第2步中获取的表需修改字符集SQL。
执行失败情况处理：
外键原因：需先关闭外键约束，全部执行完成后再打开外键约束。
-- 关闭外键约束
SET FOREIGN_KEY_CHECKS=0;-- 打开外键约束
SET FOREIGN_KEY_CHECKS=1;
表字段过长：需酌情减短相关字段的长度。
5.若需回滚原字符集，则按以下步骤进行
a. 执行第1步中备份的相关库原字符集SQL。
b. 执行第2步中备份的表原字符集SQL。
c. 执行第3步中备份的字段原字符集SQL。
