mysql错误提示Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)的一个解决办法

转载 2013年12月05日 22:48:14

mysql 中关于:Error Code: 1267 Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

最近使用mysql遇到了一个问题,当创建一个视图时,出现了如下提示:

Error Code: 1267
Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='


分析这肯定是有关编 码的问题,我的数据库的编码方式设为gbk_chinese_ci,在使用普通表时一直是正常的,没有出现任何问题,而这次是创建一个视图,创建视图的语句如下:


CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `minierp`@`%` 
    SQL SECURITY DEFINER
VIEW `viewpicklistdetaildate` AS
    select 
        `tbstoreroompicklistdetail`.`单号` AS `单号`,
        `tbstoreroompicklistdetail`.`物资编码` AS `物资编码`,
        `tbmaterialinfo`.`物资类型` AS `物资类型`,
        `tbmaterialinfo`.`物资描述` AS `物资描述`,
        `tbmaterialinfo`.`生产厂家` AS `生产厂家`,
        `tbstoreroompicklistdetail`.`单价` AS `单价`,
        `tbstoreroompicklistdetail`.`数量` AS `数量`,
        `tbstoreroompicklistindex`.`收货日期` AS `收货日期`,
        round((`tbstoreroompicklistdetail`.`单价` * `tbstoreroompicklistdetail`.`数量`),
                2) AS `总价`,
        `tbstoreroompicklistdetail`.`物资批号` AS `物资批号`,
        `tbstoreroompicklistdetail`.`工作号` AS `工作号`,
        `tbstoreroompicklistdetail`.`图号` AS `图号`,
        `tbstoreroompicklistdetail`.`工程号` AS `工程号`
    from
        ((`tbstoreroompicklistdetail`
        join `tbmaterialinfo`)
        join `tbstoreroompicklistindex`)
    where
        ((`tbstoreroompicklistdetail`.`图号`  = '%中文条件%'))
            and (`tbstoreroompicklistdetail`.`物资编码` = `tbmaterialinfo`.`物资编码`)
            and (`tbstoreroompicklistindex`.`单号` = `tbstoreroompicklistdetail`.`单号`))
    order by `tbstoreroompicklistindex`.`单号` , `tbstoreroompicklistdetail`.`GUid`


经反复试验发现 如下规律:

a . 数据库采用gbk 方式编码时:

          字段名      查询条件          结果 

1        中文         中文                 建立视图失败

2        英文         中文                 建立视图成功

3        中文         英文                  建立视图成功


也就是说在gbk编码方式时,字段名与查询条件不可同时为中文。(事实上我之前用表时一直都是正常的,这个规律仅适用于视图)


b. 数据库采用utf8方式编码(将有关字段也都改为utf8编码方式)

经试验这种方式字段名与查询条件都可以用中文。

所以,上述问题的一个解决方案就是数据库采用utf8编码方式。

      

另外,经试验,还有一种解决方案,这种方案不修改数据库,仅修改程序代码就可以了,这个解决方案中的SQL语句如下:



CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `minierp`@`%` 
    SQL SECURITY DEFINER
VIEW `viewpicklistdetaildate` AS
    select 
        `tbstoreroompicklistdetail`.`单号` AS `单号`,
        `tbstoreroompicklistdetail`.`物资编码` AS `物资编码`,
        `tbmaterialinfo`.`物资类型` AS `物资类型`,
        `tbmaterialinfo`.`物资描述` AS `物资描述`,
        `tbmaterialinfo`.`生产厂家` AS `生产厂家`,
        `tbstoreroompicklistdetail`.`单价` AS `单价`,
        `tbstoreroompicklistdetail`.`数量` AS `数量`,
        `tbstoreroompicklistindex`.`收货日期` AS `收货日期`,
        round((`tbstoreroompicklistdetail`.`单价` * `tbstoreroompicklistdetail`.`数量`),
                2) AS `总价`,
        `tbstoreroompicklistdetail`.`物资批号` AS `物资批号`,
        `tbstoreroompicklistdetail`.`工作号` AS `工作号`,
        `tbstoreroompicklistdetail`.`图号` AS `图号`,
        `tbstoreroompicklistdetail`.`工程号` AS `工程号`
    from
        ((`tbstoreroompicklistdetail`
        join `tbmaterialinfo`)
        join `tbstoreroompicklistindex`)
    where
        ((`tbstoreroompicklistdetail`.`图号`  =  convert( '%中文条件%'  using gbk) )
            and (`tbstoreroompicklistdetail`.`物资编码` = `tbmaterialinfo`.`物资编码`)
            and (`tbstoreroompicklistindex`.`单号` = `tbstoreroompicklistdetail`.`单号`))
    order by `tbstoreroompicklistindex`.`单号` , `tbstoreroompicklistdetail`.`GUid`


建上文红色部分语句,我用到了convert 关键字, SQL语句为 convert( '%中文条件%'  using gbk)

事实上,这样也可以:    convert( `tbstoreroompicklistdetail`.`图号` using utf8 )  =   '%中文条件%'  


最后问题终于解决,但是我还是有一些疑惑之处:上文说过,其实在我这个程序中,普通表进行查询时,字段名与查询条件都用中文的情况是很多的,都很正常的,为什么创建视图就不行了呢?如果谁知道希望您能告诉我。


数据库错误发生 1267 Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) fo

在操作MYsql数据库时,报“ error code [1267]; Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_g...

对 mysql Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (gb2312_chinese_ci,I的解决

对于常见的乱码问题,有的在数据库本身已经为gbk或gb2312时候,但是查询出来却是乱码,这是因为mysql在连接的过程中还有道编码,因此将连接的编码设为gb2312或utf-8即可,如:jdbc:m...

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

问题描述:MySQL进行字符串比较时发生错误: SELECT a.equ_no, b.fullCode FROM equipment a, ( SELECT t.*, getEqu...

Mysql编码引起的Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT)错误

前几天写了个Mysql存储过程,在本地环境能执行成功,但是Linux服务器里却报: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (...

解决Mysql:Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

发现是字符集的问题首先、查看mysql的my.cnf,确认为utf8mysql> show variables like "%char%";+--------------------------+--...

Illegal mix of collations (gb2312_chinese_ci,IMPLICIT) and (gbk_chinese_ci,COERCIBLE) for operation

对数据库操作时报如下错误: 数据库连接出错:SELECT COUNT(*) AS c FROM  table_content WHERE name='燚' Illegal mix of c...

处理【Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operatio】

【错误详情】:{DAL:DAL05}{Host:192.168.100.158}Illegal mix of collations (utf8_general_ci,IMPLICIT) and (ut...

java.sql.SQLException: Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

在操作MYsql数据库时,报“ SQL state [HY000]; error code [1267]; Illegal mix of collations (gbk_chinese_ci,IMPL...

java.sql.SQLException: Illegal mix of collations

java.sql.SQLException: Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COER...

过滤掉字符串中的非数字

例如将字符串中“@#¥…×(&!”
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql错误提示Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)的一个解决办法
举报原因:
原因补充:

(最多只允许输入30个字)