mysql调用存储过程出现Illegal mix of collations错误

MySQL混杂校对异常解决

最新推荐文章于 2024-05-27 10:29:47 发布

转载最新推荐文章于 2024-05-27 10:29:47 发布 · 169 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/mrma/p/3440992.html

文章标签：

#数据库

本文介绍了一种在MySQL中遇到的字符串比较错误：Illegal mix of collations(utf8_unicode_ci,IMPLICIT)and(utf8_general_ci,IMPLICIT)for operation '='。通过调整存储过程中的字符串转换方式来解决这一问题。

执行sql语句正常

执行存储过程异常

提示

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

解决办法

CREATE PROCEDURE `proce_test`(admin_name VARCHAR(32) charset utf8)

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