请问这可能是什么问题？Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='
latin1_general_ci，而where子句中列的排序规则是latin1_general_cs。这通常是由于比较两个不兼容的排序规则字符串或尝试将不同排序规则的数据选择到组合列中造成的。
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;
COLLATE latin1_general_ci 存在一个错误，会导致另一个错误：COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1' - 即使您没有一个 CHARACTER SET 'latin1' 的列！解决方法是使用 BINARY 转换。也可以参考这个问题。 - Mel_T要么改变一个（或两个）字符串的排序规则，使它们匹配，要么在表达式中添加一个COLLATE子句。
What is this "collation" stuff anyway?
As documented under Character Sets and Collations in General:
A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.
Suppose that we have an alphabet with four letters: “
A”, “B”, “a”, “b”. We give each letter a number: “A” = 0, “B” = 1, “a” = 2, “b” = 3. The letter “A” is a symbol, the number 0 is the encoding for “A”, and the combination of all four letters and their encodings is a character set.Suppose that we want to compare two string values, “
A” and “B”. The simplest way to do this is to look at the encodings: 0 for “A” and 1 for “B”. Because 0 is less than 1, we say “A” is less than “B”. What we've just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): “compare the encodings.” We call this simplest of all possible collations a binary collation.But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters “
a” and “b” as equivalent to “A” and “B”; (2) then compare the encodings. We call this a case-insensitive collation. It is a little more complex than a binary collation.In real life, most character sets have many characters: not just “
A” and “B” but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks. Also in real life, most collations have many rules, not just for whether to distinguish lettercase, but also for whether to distinguish accents (an “accent” is a mark attached to a character as in German “Ö”), and for multiple-character mappings (such as the rule that “Ö” = “OE” in one of the two German collations).
Further examples are given under Examples of the Effect of Collation.
Okay, but how does MySQL decide which collation to use for a given expression?
As documented under Collation of Expressions:
In the great majority of statements, it is obvious what collation MySQL uses to resolve a comparison operation. For example, in the following cases, it should be clear that the collation is the collation of column
charset_name:SELECT x FROM T ORDER BY x; SELECT x FROM T WHERE x = x; SELECT DISTINCT x FROM T;However, with multiple operands, there can be ambiguity. For example:
SELECT x FROM T WHERE x = 'Y';Should the comparison use the collation of the column
x, or of the string literal'Y'? Bothxand'Y'have collations, so which collation takes precedence?Standard SQL resolves such questions using what used to be called “coercibility” rules.
[ 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;It returns a result that has a character set of
utf8and the same collation asutf8_column. Values oflatin1_columnare automatically converted toutf8before concatenating.For an operation with operands from the same character set but that mix a
_bincollation and a_cior_cscollation, the_bincollation is used. This is similar to how operations that mix nonbinary and binary strings evaluate the operands as binary strings, except that it is for collations rather than data types.
So what is an "illegal mix of collations"?
An "illegal mix of collations" occurs when an expression compares two strings of different collations but of equal coercibility and the coercibility rules cannot help to resolve the conflict. It is the situation described under the third bullet-point in the above quotation.
The particular error given in the question, Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '=', tells us that there was an equality comparison between two non-Unicode strings of equal coercibility. It furthermore tells us that the collations were not given explicitly in the statement but rather were implied from the strings' sources (such as column metadata).
That's all very well, but how does one resolve such errors?
As the manual extracts quoted above suggest, this problem can be resolved in a number of ways, of which two are sensible and to be recommended:
Change the collation of one (or both) of the strings so that they match and there is no longer any ambiguity.
How this can be done depends upon from where the string has come: Literal expressions take the collation specified in the collation_connection system variable; values from tables take the collation specified in their column metadata.
Force one string to not be coercible.
I omitted the following quote from the above:
MySQL assigns coercibility values as follows:
An explicit
COLLATEclause has a coercibility of 0. (Not coercible at all.)The concatenation of two strings with different collations has a coercibility of 1.
The collation of a column or a stored routine parameter or local variable has a coercibility of 2.
A “system constant” (the string returned by functions such as
USER()orVERSION()) has a coercibility of 3.The collation of a literal has a coercibility of 4.
NULLor an expression that is derived fromNULLhas a coercibility of 5.
Thus simply adding a COLLATE clause to one of the strings used in the comparison will force use of that collation.
Whilst the others would be terribly bad practice if they were deployed merely to resolve this error:
Force one (or both) of the strings to have some other coercibility value so that one takes precedence.
Use of CONCAT() or CONCAT_WS() would result in a string with a coercibility of 1; and (if in a stored routine) use of parameters/local variables would result in strings with a coercibility of 2.
Change the encodings of one (or both) of the strings so that one is Unicode and the other is not.
This could be done via transcoding with CONVERT(expr USING transcoding_name); or via changing the underlying character set of the data (e.g. modifying the column, changing character_set_connection for literal values, or sending them from the client in a different encoding and changing character_set_client / adding a character set introducer). Note that changing encoding will lead to other problems if some desired characters cannot be encoded in the new character set.
Change the encodings of one (or both) of the strings so that they are both the same and change one string to use the relevant _bin collation.
Methods for changing encodings and collations have been detailed above. This approach would be of little use if one actually needs to apply more advanced collation rules than are offered by the _bin collation.
我遇到了类似的问题，当使用接收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排序规则。尽管此查询显示一个视图为空，但似乎视图和函数都有排序规则定义。使用的排序规则是在创建视图/函数时定义的数据库排序规则。
令人沮丧的解决方法是更改数据库排序规则并重新创建视图/函数，以强制它们使用当前的排序规则。
更改数据库的排序规则：
ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;
更改表的排序规则：
ALTER TABLE mydb CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
show full columns from my_table;。 - Jonathan Tranalter table <TABLE> modify column <COL> varchar(255) collate utf8_general_ci; 命令解决了问题。 - ChloeSHOW session variables like '%collation%';告诉您“collation_connection”是“utf8mb4_general_ci”？那么，请先运行SET collation_connection = utf8mb4_unicode_ci。 - pixelbrackets有时将字符集转换可能会很危险，尤其是对于具有大量数据的数据库。我认为最好的选择是使用 "binary" 运算符：
e.g : WHERE binary table1.column1 = binary table2.column1
我遇到了类似的问题，试图使用 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命令，变量的校对规则与表格配置的校对规则相匹配。
以下解决方案对我有用。
CONVERT( Table1.FromColumn USING utf8) = CONVERT(Table2.ToColumn USING utf8)
如果涉及文字，则解决方案。
我正在使用Pentaho Data Integration，无法指定SQL语法。使用非常简单的DB查找出现了错误“Illegal mix of collations (cp850_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '='"
生成的代码为 "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'的来源。
该视图是通过'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;
BINARY(...)数据类型。MD5(...)生成固定长度为32个字节的十六进制字符串。SHA1(...)生成40个字节的十六进制字符串。这可以存储到CHAR(32) CHARACTER SET ascii中（SHA1为40）。UNHEX(MD5(...))存储到BINARY(16)中。这样可以将列的大小减小一半。（但是，它会变得不太可打印。）如果想要可读的哈希值，请使用SELECT HEX(hash) ...。BINARY列没有排序问题。SHOW VARIABLES LIKE "collation_database";
SHOW TABLE STATUS;
如果你喜欢随处添加“collate”，并使用强制性的“overrides”来增强你的代码，那就随便吧。
MySQL非常不喜欢混合使用字符集，除非它可以将它们强制转换为相同的字符集（显然在您的情况下不可行）。您不能通过COLLATE子句强制使用相同的字符集吗？（如果适用，还可以使用更简单的BINARY快捷方式...）。