排除“非法混合整理”;mysql错误

MySQL真的不喜欢混合排序规则，除非它可以将它们强制到同一个排序规则(这在您的情况下显然是不可行的)。难道不能通过核对条款强制使用相同的排序规则吗?(或更简单的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操作符:

二进制str是CAST(str AS BINARY)的简写。

你的解决方案可能是这样的:

SELECT * FROM table WHERE BINARY a = BINARY b;

或者,

SELECT * FROM table ORDER BY BINARY a;

请记住，正如Jacob Stamm在评论中指出的那样，“转换列来比较它们将导致对该列的任何索引被忽略”。

关于这个排序业务的更多细节，我强烈推荐Eggyal对这个问题的出色回答。

把我的2c加入到未来谷歌员工的讨论中。

我正在调查一个类似的问题，在使用自定义功能接收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 |
+--------------------+-----------------+

我可以看出DB使用utf8_general_ci，而表是使用utf8_unicode_ci定义的:

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

注意，视图有零排序规则。视图和函数似乎有排序规则定义，即使该查询为一个视图显示为空。使用的排序规则是创建视图/函数时定义的DB排序规则。

可悲的解决方案是既改变db排序规则，又重新创建视图/函数，迫使它们使用当前的排序规则。

• 修改数据库的排序规则:

  ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;
  

• Changing the table collation:

  ALTER TABLE mydb CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  

I hope this will help someone.

有时转换字符集可能是危险的，特别是在具有大量数据的数据库上。我认为最好的选择是使用“二进制”操作符:

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. < p > 这个“整理”到底是什么?

   如字符集和排序规则中所述:

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

   假设我们有一个有四个字母的字母表:" A "， " B "， " a "， " b "。我们给每个字母一个数字:" A " = 0， " B " = 1， " a " = 2， " b " = 3。字母“A”是一个符号，数字0是“A”的B0，这四个字母及其编码的组合是B1。

   假设我们想比较两个字符串值" A "和" B "。最简单的方法是查看编码:0表示“A”，1表示“B”。因为0小于1，所以我们说" A "小于" B "。我们刚才所做的是对字符集应用排序规则。排序规则是一组规则(在本例中只有一条规则):“比较编码”。我们称这种最简单的排序规则为二进制排序规则。

   但是如果我们想说小写字母和大写字母是相等的呢?那么我们将至少有两条规则:(1)将小写字母“a”和“b”等效于“A”和“B”;(2)然后比较编码。我们称之为不区分大小写排序。它比二进制排序规则稍微复杂一些。

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

   进一步的例子在整理效果的例子下给出

2. < p > 好的，但是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 uses coercibility values with the following rules to resolve ambiguities:

   • Use the collation with the lowest coercibility value.

   • If both sides have the same coercibility, then:

     • If both sides are Unicode, or both sides are not Unicode, it is an error.

     • If one of the sides has a Unicode character set, and another side has a non-Unicode character set, the side with Unicode character set wins, and automatic character set conversion is applied to the non-Unicode side. For example, the following statement does not return an error:

       SELECT CONCAT(utf8_column, latin1_column) FROM t1;
       

       它返回的结果具有utf8的字符集和与utf8_column相同的排序规则。latin1_column的值在连接前自动转换为utf8

     • 对于操作数来自相同字符集但混合了_bin排序规则和_ci或_cs排序规则的操作，将使用_bin排序规则。这类似于混合了非二进制和二进制字符串的操作将操作数计算为二进制字符串，不同的是它用于排序而不是数据类型。

     李< / ul > < / >
     李< /引用> < / >
   • < p > 那么什么是“非法混合排序”呢?

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

     问题中给出的特定错误Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='告诉我们，两个具有相同强制力的非unicode字符串之间存在相等比较。它进一步告诉我们，排序规则不是在语句中显式给出的，而是从字符串的来源(例如列元数据)中暗示出来的

   • < p > 这很好，但是如何解决这样的错误呢?

     正如上面所引用的手册摘录所表明的那样，这个问题可以用许多方法来解决，其中有两种方法是合理的，值得推荐:

     • < p > 更改一个(或两个)字符串的排序规则，使它们匹配，不再有任何歧义。

       如何做到这一点取决于字符串来自哪里:字面表达式接受在collation_connection系统变量中指定的排序规则;表中的值采用列元数据中指定的排序规则

     • < p > 强制一根弦不可矫顽力。

       我省略了上面的引用:

       MySQL为强制值赋值如下:

       • 显式COLLATE子句的矫顽力为0。(完全不是强制性的。)

       • 具有不同排序规则的两个字符串的连接具有1的矫顽力。

       • 列或存储的例程参数或局部变量的排序规则具有2的矫顽力。

       • 一个“系统常数”(由< A href="http://dev.mysql.com/doc/en/information-functions.html#function_user">USER()或< A href="http://dev.mysql.com/doc/en/information-functions.html#function_version">VERSION()等函数返回的字符串)的可强制值为3。

       • 文字的排序具有4的矫顽力。

       • NULL或由NULL派生的表达式具有5的矫顽力。

       因此，简单地向比较中使用的字符串之一添加COLLATE子句将强制使用该排序规则

     而其他的则是非常糟糕的做法，如果它们只是用来解决这个错误:

     • < p > 强制其中一个(或两个)弦具有其他矫顽力值，以便其中一个优先。

       使用CONCAT()或CONCAT_WS()将导致具有1矫顽力的字符串;并且(如果在存储例程中)使用参数/局部变量将导致字符串具有2.

     • < p > 更改一个(或两个)字符串的编码，使一个是Unicode，另一个不是。

       这可以通过使用CONVERT(expr USING transcoding_name)进行代码转换来完成;或通过改变数据的底层字符集(例如，修改列，更改character_set_connection为文字值，或从客户端以不同的编码发送它们，并更改character_set_client /添加字符集引入器)。注意，如果一些想要的字符不能在新字符集中编码，更改编码将导致其他问题

     • < p > 更改一个(或两个)字符串的编码，使它们都相同，并更改一个字符串以使用相关的_bin排序规则。

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

     李< / ul > < / >

一个可能的解决方案是将整个数据库转换为UTF8(另见此问题)。

解决方案，如果文字涉及。

我使用Pentaho数据集成，不需要指定sql语法。 使用非常简单的DB查找就会出现错误

生成的代码为 "SELECT DATA_DATE AS latest_DATA_DATE FROM hr_cc_normalised_data_date_v WHERE 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”的来源。

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的MD5(...)产生一个固定长度的32字节十六进制字符串。SHA1(...)给出了一个40字节的十六进制字符串。这可以存储在CHAR(32) CHARACTER SET ascii(或sha1的40)中。

或者，更好的是，将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_connection和mysql.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) and (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 QUERY WINDOW

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)

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))