本文讨论了在MySQL中遇到的字符串比较错误问题，具体为非法混合字符集错误，并提供了解决方案：通过使用字符串转换函数来确保比较操作在相同字符集中进行。文章还介绍了相关SQL语法调整及注意事项。
问题描述：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;
扫一扫
1499
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
