对mysql中的“非法排序规则混合”错误进行故障排除

MySQL真的不喜欢混合排序规则，除非它可以将它们强制到同一个排序规则（这在您的情况下显然是不可行的）。您不能通过 COLLATE 子句强制使用相同的排序规则吗？（或更简单的快捷方式（如果适用）......BINARY

这通常是由于比较两个不兼容的排序规则字符串或尝试将不同排序规则的数据选择到组合列中所致。

该子句允许您指定查询中使用的排序规则。COLLATE

例如，以下子句将始终给出您发布的错误：WHERE

WHERE 'A' COLLATE latin1_general_ci = 'A' COLLATE latin1_general_cs

解决方案是为查询中的两列指定共享排序规则。下面是使用该子句的示例：COLLATE

SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;

另一种选择是使用运算符：BINARY

BINARY str 是 CAST（str AS BINARY） 的简写。

您的解决方案可能如下所示：

SELECT * FROM table WHERE BINARY a = BINARY b;

或

SELECT * FROM table ORDER BY BINARY a;

请记住，正如雅各布·斯塔姆（Jacob Stamm）在评论中指出的那样，“对列进行转换将导致忽略该列上的任何索引”。

有关此整理业务的更多详细信息，我强烈推荐 eggyal 对同一问题的出色回答。

将我的 2c 添加到讨论中，供未来的 Google 员工使用。

我正在调查一个类似的问题，在使用接收 varchar 参数的自定义函数时出现以下错误：

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

使用以下查询：

mysql> show variables like "collation_database";
    +--------------------+-----------------+
    | Variable_name      | Value           |
    +--------------------+-----------------+
    | collation_database | utf8_general_ci |
    +--------------------+-----------------+

我能够分辨出数据库使用的是utf8_general_ci，而表是使用 utf8_unicode_ci 定义的：

mysql> show table status;
    +--------------+-----------------+
    | Name         | Collation       |
    +--------------+-----------------+
    | my_view      | NULL            |
    | my_table     | utf8_unicode_ci |
    ...

请注意，视图具有 NULL 排序规则。视图和函数似乎具有排序规则定义，即使此查询对一个视图显示 null。使用的排序规则是在创建视图/函数时定义的数据库排序规则。

可悲的解决方案是更改数据库排序规则并重新创建视图/函数以强制它们使用当前排序规则。

• 更改数据库的排序规则：

  ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;
  

• 更改表格排序规则：

  ALTER TABLE mydb CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  

我希望这会对某人有所帮助。

有时转换字符集可能很危险，尤其是在具有大量数据的数据库上。我认为最好的选择是使用“二进制”运算符：

e.g : WHERE binary table1.column1 = binary table2.column1

我用过，但没有用。ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;

在此查询中：

Select * from table1, table2 where table1.field = date_format(table2.field,'%H');

这对我有用：

Select * from table1, table2 where concat(table1.field) = date_format(table2.field,'%H');

是的，只有一个.concat

TL;博士

更改一个（或两个）字符串的排序规则，使它们匹配，或者向表达式添加子句。COLLATE

---

1. 这个“整理”的东西到底是什么？

   如一般字符集和排序规则中所述：

   字符集是一组符号和编码。排序规则是一组用于比较字符集中字符的规则。让我们用一个虚构字符集的例子来明确区别。

   假设我们有一个包含四个字母的字母表：“”、“”、“”、“”。我们给每个字母一个数字：“” = 0，“” = 1，“” = 2，“” = 3。字母“”是一个符号，数字 0 是 “” 的编码，所有四个字母及其编码的组合是一个字符集。ABabABabAA

   假设我们要比较两个字符串值 “” 和 “”。最简单的方法是查看编码：0 表示 “”，1 表示 “”。因为 0 小于 1，所以我们说 “” 小于 “”。我们刚刚所做的是将排序规则应用于我们的字符集。排序规则是一组规则（在本例中只有一条规则）：“比较编码”。我们将所有可能的排序规则中最简单的排序规则称为二进制排序规则。ABABAB

   但是，如果我们想说小写字母和大写字母是等价的呢？那么我们至少会有两个规则：（1）将小写字母“”和“”等同于“”和“”;（2）然后比较编码。我们称之为不区分大小写的排序规则。它比二进制排序规则复杂一些。abAB

   在现实生活中，大多数字符集都有很多字符：不仅仅是“”和“”，而是整个字母表，有时是多个字母表或具有数千个字符的东方书写系统，以及许多特殊符号和标点符号。同样在现实生活中，大多数排序规则都有很多规则，不仅用于是否区分字母大小写，还用于是否区分重音（“重音”是附加在字符上的标记，如德语中的“”），以及多字符映射（例如两个德语排序规则之一中的“” = “”）。ABÖÖOE

   在排序规则效果的示例中给出了更多示例。

2. 好的，但是MySQL如何决定对给定表达式使用哪种排序规则？

   如表达式排序规则中所述：

   在绝大多数语句中，MySQL使用什么排序规则来解析比较操作是显而易见的。例如，在以下情况下，应该明确排序规则是列的排序规则：charset_name

   SELECT x FROM T ORDER BY x;
   SELECT x FROM T WHERE x = x;
   SELECT DISTINCT x FROM T;
   

   但是，对于多个操作数，可能会存在歧义。例如：

   SELECT x FROM T WHERE x = 'Y';
   

   比较应该使用列的排序规则，还是字符串文字的排序规则？两者都有排序规则，那么哪个排序规则优先呢？x'Y'x'Y'

   标准 SQL 使用过去称为“强制性”的规则来解决这些问题。

   [ deletia ]

   MySQL使用具有以下规则的强制值来解决歧义：

   • 使用具有最低强制值的排序规则。

   • 如果双方具有相同的强制力，则：

     • 如果两面都是 Unicode，或者两面都不是 Unicode，则为错误。

     • 如果其中一端具有 Unicode 字符集，而另一端具有非 Unicode 字符集，则具有 Unicode 字符集的一侧优先，并且自动字符集转换将应用于非 Unicode 端。例如，以下语句不会返回错误：

       SELECT CONCAT(utf8_column, latin1_column) FROM t1;
       

       它返回一个结果，该结果具有 的字符集和与 相同的排序规则。的值在连接之前自动转换为。utf8utf8_columnlatin1_columnutf8

     • 对于操作数来自同一字符集但混合了归类和 or 归类的操作，将使用归类。这类似于混合非二进制字符串和二进制字符串的操作将操作数计算为二进制字符串的方式，只不过它适用于排序规则而不是数据类型。_bin_ci_cs_bin

3. 那么，什么是“非法排序规则组合”呢？

   当表达式比较两个不同排序规则但具有相同强制性的字符串时，就会发生“排序规则的非法混合”，并且强制规则无法帮助解决冲突。这就是上述引文中第三个要点下描述的情况。

   问题中给出的特定错误 ，告诉我们两个具有相等可执行性的非 Unicode 字符串之间存在相等比较。它进一步告诉我们，排序规则不是在语句中明确给出的，而是从字符串的来源（例如列元数据）中隐含的。Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='

4. 这一切都很好，但是如何解决这些错误呢？

   正如上面引用的手册摘录所表明的那样，这个问题可以通过多种方式解决，其中两种是明智的，值得推荐：

   • 更改一个（或两个）字符串的排序规则，使它们匹配并且不再有任何歧义。

     如何做到这一点取决于字符串的来源：文本表达式采用 collation_connection 系统变量中指定的排序规则;表中的值采用其列元数据中指定的排序规则。

   • 强制一个字符串不具有强制性。

     我从上面省略了以下引述：

     MySQL按如下方式分配可执行性值：

     • 显式子句的强制性为 0。（完全没有强制力。COLLATE

     • 具有不同排序规则的两个字符串的串联具有 1 的强制性。

     • 列或存储的例程参数或局部变量的排序规则具有 2 的强制性。

     • “系统常量”（由 USER（） 或 VERSION（） 等函数返回的字符串）的强制性为 3。

     • 文字的排序规则具有 4 的强制性。

     • NULL或者派生自 的具有 5 的强制力的表达式。NULL

     因此，只需将子句添加到比较中使用的字符串之一，将强制使用该排序规则。COLLATE

   而其他的如果只是为了解决这个错误而部署它们，那将是非常糟糕的做法：

   • 强制一个（或两个）字符串具有其他强制值，以便其中一个优先。

     使用 CONCAT（） 或 CONCAT_WS（） 将生成一个强制为 1 的字符串;并且（如果在存储例程中）使用参数/局部变量将导致强制性为 2 的字符串。

   • 更改一个（或两个）字符串的编码，使一个字符串是 Unicode，另一个不是。

     这可以通过使用 CONVERT（expr USING transcoding_name） 进行转码来完成;或者通过更改数据的基础字符集（例如，修改列、更改文本值的character_set_connection，或以不同的编码从客户端发送它们并更改character_set_client/添加字符集引入器）。请注意，如果某些所需的字符无法在新字符集中编码，则更改编码将导致其他问题。

   • 更改一个（或两个）字符串的编码，使它们相同，并更改一个字符串以使用相关的_bin排序规则。

     上面详细介绍了更改编码和排序规则的方法。如果实际上需要应用比排序规则提供的更高级的排序规则，则此方法将毫无用处。_bin

一个可能的解决方案是将整个数据库转换为 UTF8（另请参阅此问题）。

如果涉及文本，则为解决方案。

我正在使用 Pentaho 数据集成，但无法指定 sql 语法。 使用非常简单的数据库查找给出了错误 “非法混合运算'='的排序规则 （cp850_general_ci，COERCIBLE） 和 （latin1_swedish_ci，COERCIBLE）”

生成的代码是 “从hr_cc_normalised_data_date_v中选择DATA_DATE作为latest_DATA_DATE，其中 PSEUDO_KEY = ？”

简而言之，查找是查看，当我发布时

mysql> show full columns from hr_cc_normalised_data_date_v;
+------------+------------+-------------------+------+-----+
| Field      | Type       | Collation         | Null | Key |
+------------+------------+-------------------+------+-----+
| PSEUDO_KEY | varchar(1) | cp850_general_ci  | NO   |     |
| DATA_DATE  | varchar(8) | latin1_general_cs | YES  |     |
+------------+------------+-------------------+------+-----+

这解释了“cp850_general_ci”的来源。

该视图是使用 'SELECT 'X',......' 创建的 根据手册，像这样的文字应该从服务器设置继承它们的字符集和排序规则，这些设置被正确定义为“latin1”和“latin1_general_cs” 由于这显然没有发生，所以我在创建视图时强制了它

CREATE OR REPLACE VIEW hr_cc_normalised_data_date_v AS
SELECT convert('X' using latin1) COLLATE latin1_general_cs        AS PSEUDO_KEY
    ,  DATA_DATE
FROM HR_COSTCENTRE_NORMALISED_mV
LIMIT 1;

现在，它显示两列的latin1_general_cs，并且错误已消失。:)

如果您遇到问题的列是“哈希”，请考虑以下事项......

如果“哈希”是二进制字符串，则实际上应该使用数据类型。BINARY(...)

如果“哈希”是十六进制字符串，则不需要 utf8，并且应该避免这样做，因为字符检查等。例如，MySQL 生成一个固定长度的 32 字节十六进制字符串。 给出一个 40 字节的十六进制字符串。这可以存储到（或 sha1 为 40）。MD5(...)SHA1(...)CHAR(32) CHARACTER SET ascii

或者，更好的是，存储到 .这样一来，列的大小就减小了一半。（然而，它确实使它相当不可打印。 如果你想让它可读。UNHEX(MD5(...))BINARY(16)SELECT HEX(hash) ...

比较两列没有排序规则问题。BINARY

排序规则问题的另一个来源是表。检查存储过程和功能的排序规则：mysql.proc

SELECT
  p.db, p.db_collation, p.type, COUNT(*) cnt
FROM mysql.proc p
GROUP BY p.db, p.db_collation, p.type;

还要注意和列。mysql.proc.collation_connectionmysql.proc.character_set_client

我遇到了类似的问题，正在尝试使用带有字符串变量的FIND_IN_SET过程。

SET @my_var = 'string1,string2';
SELECT * from my_table WHERE FIND_IN_SET(column_name,@my_var);

并收到错误

错误代码：1267。排序规则的非法混合 （utf8_unicode_ci，IMPLICIT） 和 （utf8_general_ci，IMPLICIT） 用于操作“find_in_set”

简短的回答：

无需更改任何collation_YYYY变量，只需在变量声明旁边添加正确的排序规则，即

SET @my_var = 'string1,string2' COLLATE utf8_unicode_ci;
SELECT * from my_table WHERE FIND_IN_SET(column_name,@my_var);

长答案：

我首先检查了排序规则变量：

mysql> SHOW VARIABLES LIKE 'collation%';
    +----------------------+-----------------+
    | Variable_name        | Value           |
    +----------------------+-----------------+
    | collation_connection | utf8_general_ci |
    +----------------------+-----------------+
    | collation_database   | utf8_general_ci |
    +----------------------+-----------------+
    | collation_server     | utf8_general_ci |
    +----------------------+-----------------+

然后我检查了表格排序规则：

mysql> SHOW CREATE TABLE my_table;

CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column_name` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=125 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

这意味着我的变量配置了默认排序规则 utf8_general_ci，而我的表配置为 utf8_unicode_ci。

通过在变量声明旁边添加 COLLATE 命令，变量排序规则与为表配置的排序规则匹配。

此代码需要放在数据库上运行 SQL 查询/查询中

SQL 查询窗口

ALTER TABLE `table_name` CHANGE `column_name` `column_name`   VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

请将 table_name 和 column_name 替换为适当的名称。

如果您安装了phpMyAdmin，则可以按照以下链接中的说明进行操作： https://mediatemple.net/community/products/dv/204403914/default-mysql-character-set-and-collation 您必须将数据库的排序规则与所有表的排序规则以及表的字段进行匹配，然后重新编译所有存储过程和函数。有了这个，一切都应该会再次工作。

很有意思...现在，做好准备。我查看了所有“添加整理”解决方案，对我来说，这些都是创可贴修复。现实情况是，数据库设计是“糟糕的”。是的，标准更改和新内容被添加，等等，但这并没有改变糟糕的数据库设计事实。我拒绝在SQL语句中添加“collate”只是为了让我的查询正常工作。唯一对我有用的解决方案是重新设计数据库/表，以匹配我将长期使用和接受的字符集。在本例中，我选择使用字符集“utf8mb4”。

因此，当您遇到“非法”错误消息时，这里的解决方案是重新设计数据库和表。这比听起来容易和快捷得多。甚至可能不需要导出数据并从 CSV 重新导入数据。更改数据库的字符集，并确保表的所有字符集都匹配。

使用以下命令来指导您：

SHOW VARIABLES LIKE "collation_database";
SHOW TABLE STATUS;

现在，如果你喜欢在这里和那里添加“collate”，并用强制填充“覆盖”来加强你的代码，这是我的猜测。

以下解决方案对我有用。

CONVERT( Table1.FromColumn USING utf8)    =  CONVERT(Table2.ToColumn USING utf8) 

我个人在手术中遇到了这个问题。 如果你不愿意，你可以尝试将你的参数转换为过程。 我尝试过使用 collate（在选择中设置一个），但没有一个适合我。alter table

CONVERT(my_param USING utf32) 做到了。

在我的情况下，函数的默认返回类型是来自数据库的类型/排序规则（utf8mb4_general_ci），但数据库列是 ascii。

WHERE ascii_col = md5(concat_ws(',', a,b,c))

快速修复是

WHERE ascii_col = BINARY md5(concat_ws(',', a,b,c))

我比较了两个数据库中的字段。一个使用 utf8，另一个使用 utf8mb4。我注意到只有整理到 utf8mb4（我用utf8mb4_unicode_ci）才有效。尝试utf8_unicode_ci产生错误。

概括地说：在比较具有不同字符集的字符串时，可能只有一个字符集适用于 COLLATE 命令。