问题描述: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

错误如下:

[Err] 1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

解决方法:将比较等式一边进行字符串转换,如改为“CONVERT(b.fullCode 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;