mysql错误提示Illegal mix of collations (gbk | 您所在的位置:网站首页 › MySQL错误1267 › mysql错误提示Illegal mix of collations (gbk |
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 ) = '%中文条件%'
最后问题终于解决,但是我还是有一些疑惑之处:上文说过,其实在我这个程序中,普通表进行查询时,字段名与查询条件都用中文的情况是很多的,都很正常的,为什么创建视图就不行了呢?如果谁知道希望您能告诉我。 |
CopyRight 2018-2019 实验室设备网 版权所有 |