MySQL字段编码不一致导致的“Illegal mix of collations”错误

举报
江晚正愁余 发表于 2024/04/08 15:01:12 2024/04/08
【摘要】 MySQL字段编码不一致导致的“Illegal mix of collations”错误在MySQL数据库中,当字段的字符集和排序规则不一致时,可能会出现“Illegal mix of collations”错误。如报错: ### Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLI...

MySQL字段编码不一致导致的“Illegal mix of collations”错误



在MySQL数据库中,当字段的字符集和排序规则不一致时,可能会出现“Illegal mix of collations”错误。

如报错: 

### Cause: java.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
; uncategorized SQLException; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='; nested exception is java.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='] with root cause


这个错误通常发生在执行涉及不同字符集和排序规则的数据比较操作时。以下是解决这个问题的步骤:


步骤一:检查数据库和表的字符集
首先,您需要确定数据库、表和字段的字符集设置。可以使用以下命令来查看:

  1. SHOW CREATE DATABASE your_database_name;
  2. SHOW CREATE TABLE your_table_name;

这将显示数据库和表的创建语句,从中可以查看字符集设置。


步骤二:统一字符集和排序规则


如果发现数据库、表或字段的字符集不一致,您需要将它们统一到一个字符集和排序规则下。

可以使用以下命令来修改数据库、表或字段的字符集和排序规则:

ALTER DATABASE your_database_name CHAR ACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE your_table_name CHANGE column_name column_name column_type CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

在上述命令中,将your_database_name替换为实际的数据库名称,your_table_name替换为实际的表名称,column_name替换为实际的列名称,column_type替换为列的数据类型。


select

table_schema,

table_name,

column_name,

character_set_name,

collation_name

from

information_schema.columns

where

TABLE_SCHEMA ='db2'

order by

table_schema,

table_name,

ordinal_position;


通过以上命令查看哪些表或字段名规则不一致,可以根据情况修改



步骤三:检查数据的一致性


在进行字符集和排序规则的修改后,您需要检查数据的一致性,确保没有数据丢失或损坏。可以使用以下命令来检查数据的一致性:

  1. CHECK TABLE your_table_name;

这将检查表中的数据是否符合当前的字符集和排序规则要求。如果出现错误,请根据错误信息进行相应的处理。
注意事项:在进行字符集和排序规则的修改之前,请务必备份数据库或表的数据,以防意外情况发生。

此外,修改字符集和排序规则可能会对已有的应用程序产生影响,请确保在修改之前进行充分的测试和验证。


总结:解决MySQL字段编码不一致导致的“Illegal mix of collations”错误需要统一数据库、表和字段的字符集和排序规则。通过检查数据库、表和字段的字符集设置,并进行相应的修改,可以避免出现这个错误。在进行修改之前,请务必备份数据并测试应用程序的兼容性。


批量解决方法


日志报错:va.sql.SQLException: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='


SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '原排序规则',CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', TABLE_NAME, ' COLLATE=utf8mb4_general_ci;') '修正SQL'
FROM information_schema.`TABLES` where 	TABLE_COLLATION != 'utf8mb4_general_ci' and TABLE_SCHEMA='数据库名称';

SELECT
	TABLE_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 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  TABLE_NAME in(select TABLE_NAME from information_schema.`TABLES` where table_type = 'BASE TABLE' and TABLE_SCHEMA='数据库名称') and 
	-- -过滤正确排序规则
	COLLATION_NAME != 'utf8mb4_general_ci'
	-- -数据库名称
	AND TABLE_SCHEMA = '数据库名称';


将上面2条语句中修正SQL复制出来执行即可.
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。