mysql - 归类的非法混合 (utf8_unicode_ci,IMPLICIT) 和 (utf8_general_ci,IMPLICIT)

Question

这是我的查询:

INSERT INTO location_province(name, country)   
SELECT child.name
      ,location_country.id
  FROM location_1 child
 INNER JOIN location_1 parent
    ON child.parent_id = parent.id
 INNER JOIN location_country
    ON location_country.name = parent.name
 WHERE child.location_type = 1

它抛出这个错误:

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

出了什么问题，我该如何解决？

---

注意在查询末尾添加COLLATE utf8_unicode_ci 也不起作用。

Answer 1

是的，这是因为 JOIN ON 子句和每个错误，ON 条件中涉及的那些列的排序规则不匹配。这些列的排序规则必须匹配。我的意思是下面几行

ON child.parent_id = parent.id  ------ 1
 INNER JOIN location_country
    ON location_country.name = parent.name ------ 2

检查您要加入的表并进行验证

好吧，加入时更改排序规则

 INNER JOIN location_country
    ON location_country.name collate utf8_general_ci = parent.name collate utf8_general_ci