英文:
MySQL: Illegal mix of collations: both same collation
问题
我看到Stack Overflow上有很多关于类似问题的提问，但没有一个涉及到这个特定的细节。
我使用类似这样的方式创建了一个数据库：
CREATE DATABASE db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
我创建了一个具有相同字符集和校对规则的表：
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id int AUTO_INCREMENT PRIMARY KEY,
data varchar(255)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci ENGINE=INNODB;
INSERT INTO test(data) VALUES ('apple'),('banana'),('cherry'),('date');
然后，我使用CTE连接了这个表：
WITH cte(name) AS (
SELECT cast('apple' as char(255))
UNION ALL SELECT cast('cherry' as char(255))
)
SELECT * FROM cte JOIN test on cte.name=test.data;
我收到以下错误：
Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT)
for operation '='.
问题在于，数据库的默认校对规则与表的默认校对规则相同。
如果我在表的末尾不使用校对规则子句创建表，它是正常的。如果我使用nchar而不是char也可以正常工作。
我知道，为什么我不只是创建一个没有校对规则子句的表，或者使用nchar呢？问题是，如果我指定了校对规则，为什么char不起作用？
英文:
I see a number of questions on SO regarding something similar, but none of them addresses this particular detail.
I created a database using something like this:
CREATE DATABASE db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
I create a table with the same character set & collation:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id int AUTO_INCREMENT PRIMARY KEY,
data varchar(255)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci ENGINE=INNODB;
INSERT INTO test(data) VALUES ('apple'),('banana'),('cherry'),('date');
I then join the table with a CTE:
WITH cte(name) AS (
SELECT cast('apple' as char(255))
UNION ALL SELECT cast('cherry' as char(255))
)
SELECT * FROM cte JOIN test on cte.name=test.data;
I get the following error:
> Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT)
for operation '='.
The thing is, the default collation for the database is the same as that for the table.
If I create the table without the collation clause at the end, it’s fine. It also works if I use nchar instead of char.
I know, why don’t I just create the table without the collation clause, or use nchar? The question is why doesn’t it work with char if I specify the collation?
答案1
得分: 4
你的表没问题。
不兼容的是你的会话排序规则。
演示，使用你的示例表格和数据：
mysql> WITH cte(name) AS (
-> SELECT cast('apple' as char(255))
-> UNION ALL SELECT cast('cherry' as char(255))
-> )
-> SELECT * FROM cte JOIN test on cte.name=test.data;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation '=';
这是你遇到的错误，因为我的会话排序规则是utf8mb4_0900_ai_ci。
我可以更改会话排序规则并重新测试：
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci;
mysql> WITH cte(name) AS (
-> SELECT cast('apple' as char(255))
-> UNION ALL SELECT cast('cherry' as char(255))
-> )
-> SELECT * FROM cte JOIN test on cte.name=test.data;
+--------+----+--------+
| name | id | data |
+--------+----+--------+
| apple | 1 | apple |
| cherry | 3 | cherry |
+--------+----+--------+
英文:
Your table is fine.
It's your session collation that is incompatible.
Demo, using your example table and data:
mysql> WITH cte(name) AS (
-> SELECT cast('apple' as char(255))
-> UNION ALL SELECT cast('cherry' as char(255))
-> )
-> SELECT * FROM cte JOIN test on cte.name=test.data;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation '='
That's the error you got, because my session collation is utf8mb4_0900_ai_ci.
I can change the session collation and test again:
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci;
mysql> WITH cte(name) AS (
-> SELECT cast('apple' as char(255))
-> UNION ALL SELECT cast('cherry' as char(255))
-> )
-> SELECT * FROM cte JOIN test on cte.name=test.data;
+--------+----+--------+
| name | id | data |
+--------+----+--------+
| apple | 1 | apple |
| cherry | 3 | cherry |
+--------+----+--------+
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论