文章内容
2018/8/15 16:37:47,作 者: 黄兵
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=';
最近更新数据库的时候报如下错误:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=';
在SQLAlchemy报错的内容是:
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1267, "Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='") [SQL: 'SELECT material_product.p_id AS material_product_p_id, material_product.p_name AS material_product_p_name, material_product.b_name AS material_product_b_name, material_product.p_fk_p AS material_product_p_fk_p, material_product.p_fk_i AS material_product_p_fk_i \nFROM material_product \nWHERE %s = material_product.b_name'] [parameters: ('武钢',)] (Background on this error at: http://sqlalche.me/e/e3q8)
截图如下:
出错原因:
两张表的编码方式不一样。
首先看看查询语句:
SELECT material_product.p_id AS material_product_p_id, material_product.p_name AS material_product_p_name, material_product.b_name AS material_product_b_name, material_product.p_fk_p AS material_product_p_fk_p, material_product.p_fk_i AS material_product_p_fk_i FROM material_product WHERE material_product.b_name='武钢'
这个查询的的是material_product表,看看编码格式:
b_name又是外键,看看另外一张表(material_brand)的编码格式:
可以看到最后的编码格式不同,才会造成这种问题。
知道问题出在哪里了,就很好解决问题了。
解决方案:
修改material_product表的编码格式:
alter table material_product convert to character set utf8mb4 collate utf8mb4_bin;
如果出现错误提示:
Cannot change column 'b_name': used in a foreign key constraint 'material_product_ibfk_1' of table 'Material_Development.material_product'
之后再次执行,没有报错,问题解决。
有什么问题欢迎在下面留言。
参考资料:
mysql字符集问题:Illegal mix of collations
黄兵个人博客原创。
转载请注明出处:黄兵个人博客 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=';
会变色的中华白海豚,年老驼背还粉红色,越老越少女心
评论列表