mysql 错误代码1267批量修改编码的解决办法

mysql 错误代码1267批量修改编码的解决办法

问题描述

错误代码： 1267
Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=

解决办法

1，排查错误，先查询database，再查看出错表的编码，再看出错表中字段的编码。

show table status like 'hr_eval_calculate_score';
show table status --查询所有表的编码
-- utf8mb4_general_ci
show table status like 'hr_eval_appraisee';
-- utf8mb4_unicode_ci
SHOW FULL COLUMNS FROM hr_eval_appraisee;

排查发现，数据库中的一大部分表编码都是utf8mb4_unicode_ci，编码错误，正确为utf8mb4_general_ci
2，批量修改表编码

SELECT 
CONCAT("ALTER TABLE `", TABLE_NAME,"` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") 
AS target_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="eval"
AND TABLE_TYPE="BASE TABLE"

通过如上sql的执行会生成很多的修改脚本，将生成出来的脚本，放在执行器中，执行即可。
3，批量修改所有表中的字段，生成批量脚本的sql如下：
其中information_schema.COLUMNS表中包含了很多字段的信息，需要其他信息的可以从表中查出进行拼接。

SELECT
	CONCAT(
		'ALTER TABLE `', table_name,
		'` MODIFY COLUMN `',
		column_name,
		'` ',
		DATA_TYPE,
		'(',
		CHARACTER_MAXIMUM_LENGTH,
		') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
		(
			CASE
			WHEN IS_NULLABLE = 'NO' THEN
				' NOT NULL'
			ELSE
				''
			END
		),
		' COMMENT "', COLUMN_COMMENT ,'"',
		';'
	)
FROM
	information_schema.COLUMNS
WHERE
	TABLE_SCHEMA = 'eval'; --eval数据库名称
	

通过如上sql的执行会生成很多的修改脚本，将生成出来的脚本，放在执行器中，执行即可。
4，到此解决问题。

sql小技巧

当时从网上找了很多的脚本，但是都有缺陷，可以利用Navicat来进行生成sql标准sql脚本。比如，找到对应字段的错误编码修改，在保存的时候会在sql预览里面生成一条更新sql，依据这个sql的形式，来拼接你的批量sql，就不会因为批量更新，错误更新了其他信息。