mysql 字符串=比较 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_gene

最新推荐文章于 2025-03-05 19:05:15 发布

usenrong

最新推荐文章于 2025-03-05 19:05:15 发布

阅读量836

点赞数

文章标签：数据库 java php

本文链接：https://blog.youkuaiyun.com/usenrong/article/details/84811507

版权

本文介绍了MySQL中因字符串比较导致的错误：'Illegal mix of collations'，并提供了解决方案，通过使用CONVERT函数来统一字符串的字符集和排序规则。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL进行字符串比较时发生错误：

SELECT
	a.equ_no,
	b.fullCode
FROM
	equipment a,
	(
		SELECT
			t.*, getEquTypeFullCode (t.equType_id) AS fullCode
		FROM
			equ_type t
	) b
WHERE
	substring(a.equ_no, 1, 5) = b.fullCode

错误如下： Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

解决方法：将比较等式一边进行字符串转换，如改为“CONVERT(b.fullCode USING utf8) COLLATE utf8_unicode_ci”

UPDATE template_messag_doctor a SET a.sended=1 where  EXISTS (select 1 from template_messag_exclude_openid b where a.openid = CONVERT(b.openid USING utf8) COLLATE utf8_unicode_ci  )

SELECT
	a.equ_no,
	b.fullCode
FROM
	equipment a,
	(
		SELECT
			t.*, getEquTypeFullCode (t.equType_id) AS fullCode
		FROM
			equ_type t
	) b
WHERE
	substring(a.equ_no, 1, 5) = CONVERT(b.fullCode USING utf8) COLLATE utf8_unicode_ci;

UPDATE template_messag_doctor a SET a.sended=1 where  EXISTS (select 1 from template_messag_exclude_openid b where a.openid = CONVERT(b.openid USING utf8) COLLATE utf8_unicode_ci  )