blocks|key|3252666|text|MySQL确实不喜欢混合排序规则，除非它可以将它们强制为同一个排序规则(这在您的情况下显然是不可行的)。您不能通过COLLATE+clause强制使用相同的排序规则吗？(如果适用，也可以使用更简单的BINARY快捷方式...)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3252667|entityMap|0|LINK|mutability|MUTABLE|url|http://www.datutorials.com/mysql/mysql_ref_manual_Charset.php#Charset-collate^0|2R|6|1L|E|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

MySQL really dislikes mixing collations unless it can coerce them to the same one (which clearly is not feasible in your case). Can't you just force the same collation to be used via a <a href="http://www.datutorials.com/mysql/mysql_ref_manual_Charset.php#Charset-collate" rel="nofollow noreferrer">COLLATE clause</a>? (or the simpler <code>BINARY</code> shortcut if applicable...).

blocks|key|3252801|text|这通常是由于比较两个不兼容排序规则的字符串或尝试将不同排序规则的数据选择到组合列中而导致的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3252802|子句COLLATE允许您指定查询中使用的排序规则。|offset|length|style|CODE|3252803|例如，下面的WHERE子句将始终给出您发布的错误：|3252804|WHERE+'A'+COLLATE+latin1_general_ci+=+'A'+COLLATE+latin1_general_cs|code-block|syntax|javascript|3252805|您的解决方案是为查询中的两列指定共享排序规则。下面是一个使用COLLATE子句的示例：|3252806|SELECT+*+FROM+table+ORDER+BY+key+COLLATE+latin1_general_ci;|3252807|另一种选择是使用BINARY运算符：|3252808|3252809|二进制字符串是CAST(字符串为二进制)的简写。|blockquote|3252810|3252811|3252812|您的解决方案可能如下所示：|3252813|SELECT+*+FROM+table+WHERE+BINARY+a+=+BINARY+b;|3252814|或,|3252815|SELECT+*+FROM+table+ORDER+BY+BINARY+a;|3252816|entityMap^0|0|2|7|0|6|5|0|0|U|7|0|0|8|6|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1B|8|@$D|1C|E|1D|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|1E|8|@$D|1F|E|1G|F|G]]|9|@]|A|$]]|$1|J|3|K|5|L|7|1H|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1I|8|@$D|1J|E|1K|F|G]]|9|@]|A|$]]|$1|Q|3|R|5|L|7|1L|8|@]|9|@]|A|$M|N]]|$1|S|3|T|5|6|7|1M|8|@$D|1N|E|1O|F|G]]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]|$1|V|3|W|5|X|7|1Q|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]|$1|Z|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1T|8|@]|9|@]|A|$]]|$1|12|3|13|5|L|7|1U|8|@]|9|@]|A|$M|N]]|$1|14|3|15|5|6|7|1V|8|@]|9|@]|A|$]]|$1|16|3|17|5|L|7|1W|8|@]|9|@]|A|$M|N]]|$1|18|3|-4|5|6|7|1X|8|@]|9|@]|A|$]]]|19|$]]

This is generally caused by comparing two strings of incompatible collation or by attempting to select data of different collation into a combined column.

The clause <code>COLLATE</code> allows you to specify the collation used in the query.

For example, the following <code>WHERE</code> clause will always give the error you posted:

<pre><code>WHERE 'A' COLLATE latin1_general_ci = 'A' COLLATE latin1_general_cs
</code></pre>

Your solution is to specify a shared collation for the two columns within the query. Here is an example that uses the <code>COLLATE</code> clause:

<pre><code>SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;
</code></pre>

Another option is to use the <code>BINARY</code> operator:

<blockquote>
 BINARY str is the shorthand for CAST(str AS BINARY).
</blockquote>

Your solution might look something like this:

<pre><code>SELECT * FROM table WHERE BINARY a = BINARY b;
</code></pre>

or,

<pre><code>SELECT * FROM table ORDER BY BINARY a;
</code></pre>

blocks|key|307936|text|将我的2c添加到对未来谷歌人的讨论中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|307937|我正在调查一个类似的问题，在使用接收varchar参数的自定义函数时，我得到了以下错误：|307938|Illegal+mix+of+collations+(utf8_unicode_ci,IMPLICIT)+and+
(utf8_general_ci,IMPLICIT)+for+operation+'='|code-block|syntax|javascript|307939|使用以下查询：|307940|mysql>+show+variables+like+"collation_database";
++++%2B--------------------%2B-----------------%2B
++++%7C+Variable_name++++++%7C+Value+++++++++++%7C
++++%2B--------------------%2B-----------------%2B
++++%7C+collation_database+%7C+utf8_general_ci+%7C
++++%2B--------------------%2B-----------------%2B|307941|虽然表是使用utf8_unicode_ci定义的，但我能够分辨出DB使用的是utf8_general_ci，|offset|length|style|BOLD|307942|mysql>+show+table+status;
++++%2B--------------%2B-----------------%2B
++++%7C+Name+++++++++%7C+Collation+++++++%7C
++++%2B--------------%2B-----------------%2B
++++%7C+my_view++++++%7C+NULL++++++++++++%7C
++++%7C+my_table+++++%7C+utf8_unicode_ci+%7C
++++...|307943|请注意，这些视图具有NULL排序规则。视图和函数似乎都有排序规则定义，即使此查询对一个视图显示为null。使用的排序规则是在创建视图/函数时定义的DB排序规则。|307944|可悲的解决方案是既更改数据库排序规则，又重新创建视图/函数，以强制它们使用当前排序规则。|307945|307946|更改数据库的排序规则：|unordered-list-item|307947|307948|ALTER+DATABASE+mydb+DEFAULT+COLLATE+utf8_unicode_ci;|307949|Changing表排序规则：|307950|307951|ALTER+TABLE+utf8_unicode_ci;+CONVERT+TO字符集utf8+COLLATE+mydb|307952|我希望这能对某些人有所帮助。|307953|entityMap^0|0|0|0|0|0|6|F|12|F|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1F|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1G|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1H|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|1I|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|1J|8|@$O|1K|P|1L|Q|R]|$O|1M|P|1N|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|F|7|1O|8|@]|9|@]|A|$G|H]]|$1|U|3|V|5|6|7|1P|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]|$1|Z|3|10|5|11|7|1S|8|@]|9|@]|A|$]]|$1|12|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]|$1|13|3|14|5|6|7|1U|8|@]|9|@]|A|$]]|$1|15|3|16|5|11|7|1V|8|@]|9|@]|A|$]]|$1|17|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]|$1|18|3|19|5|6|7|1X|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|1C|3|-4|5|6|7|1Z|8|@]|9|@]|A|$]]]|1D|$]]

<h2>Adding my 2c to the discussion for future googlers.</h2>

I was investigating a similar issue where I got the following error when using custom functions that recieved a varchar parameter:

<pre><code>Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and 
(utf8_general_ci,IMPLICIT) for operation '='
</code></pre>

Using the following query:

<pre><code>mysql&gt; show variables like "collation_database";
 +--------------------+-----------------+
 | Variable_name | Value |
 +--------------------+-----------------+
 | collation_database | utf8_general_ci |
 +--------------------+-----------------+
</code></pre>

I was able to tell that the DB was using utf8_general_ci, while the tables were defined using utf8_unicode_ci:

<pre><code>mysql&gt; show table status;
 +--------------+-----------------+
 | Name | Collation |
 +--------------+-----------------+
 | my_view | NULL |
 | my_table | utf8_unicode_ci |
 ...
</code></pre>

Notice that the views have NULL collation. It appears that views and functions have collation definitions even though this query shows null for one view. The collation used is the DB collation that was defined when the view/function were created.

The sad solution was to both change the db collation and recreate the views/functions to force them to use the current collation.

<ul>
<li>Changing the db's collation:

<pre><code>ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;
</code></pre></li>
<li>Changing the table collation:

<pre><code>ALTER TABLE mydb CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
</code></pre></li>
</ul>

I hope this will help someone.

blocks|key|307958|text|有时转换字符集可能是危险的，特别是在具有大量数据的数据库上。我认为最好的选择是使用“二元”运算符：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|307959|e.g+:+WHERE+binary+table1.column1+=+binary+table2.column1|code-block|syntax|javascript|307960|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Sometimes it can be dangerous to convert charsets, specially on databases with huge amounts of data. I think the best option is to use the "binary" operator:

<pre><code>e.g : WHERE binary table1.column1 = binary table2.column1
</code></pre>

blocks|key|3252861|text|我使用了ALTER+DATABASE+mydb+DEFAULT+COLLATE+utf8_unicode_ci;，但不起作用。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3252862|在此查询中：|3252863|Select+*+from+table1,+table2+where+table1.field+=+date_format(table2.field,'%25H');|code-block|syntax|javascript|3252864|这对我来说很有效：|3252865|Select+*+from+table1,+table2+where+concat(table1.field)+=+date_format(table2.field,'%25H');|3252866|是的，只有一个concat。|3252867|entityMap^0|4|1G|0|0|0|0|0|7|6|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|Z|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|10|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|S|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|T|$]]

I used <code>ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;</code>, but didn't work.

In this query:

<pre><code>Select * from table1, table2 where table1.field = date_format(table2.field,'%H');
</code></pre>

This work for me:

<pre><code>Select * from table1, table2 where concat(table1.field) = date_format(table2.field,'%H');
</code></pre>

Yes, only a <code>concat</code>.

blocks|key|3252892|text|一种可能的解决方案是convert+the+entire+database+to+UTF8+(另请参阅此question)。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3252893|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.moodle.org/23/en/Converting_your_MySQL_database_to_UTF8|1|https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8^0|A|Z|0|1G|8|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

A possible solution is to <a href="http://docs.moodle.org/23/en/Converting_your_MySQL_database_to_UTF8" rel="nofollow noreferrer">convert the entire database to UTF8</a> (see also this <a href="https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8">question</a>).

blocks|key|3252920|text|如果涉及文字，则解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3252921|我使用的是Pentaho+Data+Integration，并没有指定sql语法。使用非常简单的DB查找会产生错误“非法混合排序规则(cp850_general_ci，强制)和(latin1_swedish_ci，强制)+for+operation+'='”|3252922|生成的代码是"SELECT+DATA_DATE+AS+latest_DATA_DATE+FROM+hr_cc_normalised_data_date_v+WHERE+PSEUDO_KEY+=+?“|3252923|长话短说，查找是到一个视图，当我发出|3252924|mysql>+show+full+columns+from+hr_cc_normalised_data_date_v;
%2B------------%2B------------%2B-------------------%2B------%2B-----%2B
%7C+Field++++++%7C+Type+++++++%7C+Collation+++++++++%7C+Null+%7C+Key+%7C
%2B------------%2B------------%2B-------------------%2B------%2B-----%2B
%7C+PSEUDO_KEY+%7C+varchar(1)+%7C+cp850_general_ci++%7C+NO+++%7C+++++%7C
%7C+DATA_DATE++%7C+varchar(8)+%7C+latin1_general_cs+%7C+YES++%7C+++++%7C
%2B------------%2B------------%2B-------------------%2B------%2B-----%2B|code-block|syntax|javascript|3252925|这就解释了“cp850_general_ci”从何而来。|3252926|这个视图是用'SELECT+'X'，......‘创建的根据手册，像这样的文字应该从服务器设置继承它们的字符集和排序规则，这些设置被正确地定义为'latin1‘和'latin1_general_cs’，因为这显然没有发生，我在创建视图时强制这样做|3252927|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;|3252928|现在，它为两列都显示了latin1_general_cs，错误也消失了。:)|3252929|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Z|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|10|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|J|7|13|8|@]|9|@]|A|$K|L]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

Solution if literals are involved.

I am using Pentaho Data Integration and dont get to specify the sql syntax.
Using a very simple DB lookup gave the error
"Illegal mix of collations (cp850_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '='"

The generated code was 
"SELECT DATA_DATE AS latest_DATA_DATE FROM hr_cc_normalised_data_date_v WHERE PSEUDO_KEY = ?"

Cutting the story short the lookup was to a view and when I issued 

<pre><code>mysql&gt; 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 | |
+------------+------------+-------------------+------+-----+
</code></pre>

which explains where the 'cp850_general_ci' comes from.

The view was simply created with 'SELECT 'X',......'
According to the manual literals like this should inherit their character set and collation from server settings which were correctly defined as 'latin1' and 'latin1_general_cs'
as this clearly did not happen I forced it in the creation of the view

<pre><code>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;
</code></pre>

now it shows latin1_general_cs for both columns and the error has gone away. :)

blocks|key|308031|text|如果您遇到问题的列是“散列”，那么请考虑以下内容……|type|unstyled|depth|inlineStyleRanges|entityRanges|data|308032|如果“散列”是二进制字符串，那么您应该真正使用BINARY(...)数据类型。|offset|length|style|CODE|308033|如果"hash“是十六进制字符串，则不需要utf8，并且由于字符检查等原因应避免这种情况。例如，MySQL的MD5(...)生成一个固定长度的32字节十六进制字符串。SHA1(...)给出了一个40字节的十六进制字符串。这可以存储到CHAR(32)+CHARACTER+SET+ascii中(对于sha1，可以存储在40中)。|308034|或者，更好的方法是将UNHEX(MD5(...))存储到BINARY(16)中。这会将列的大小减半。(但是，它确实使它相当不可打印。)如果您希望它可读，请使用SELECT+HEX(hash)+...。|308035|比较两个BINARY列没有排序规则问题。|308036|entityMap^0|0|N|B|0|1I|8|2B|9|38|S|0|A|F|S|A|27|K|0|4|6|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@$D|U|E|V|F|G]|$D|W|E|X|F|G]|$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|10|8|@$D|11|E|12|F|G]|$D|13|E|14|F|G]|$D|15|E|16|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|17|8|@$D|18|E|19|F|G]]|9|@]|A|$]]|$1|N|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|O|$]]

If the columns that you are having trouble with are "hashes", then consider the following...

If the "hash" is a binary string, you should really use <code>BINARY(...)</code> datatype.

If the "hash" is a hex string, you do not need utf8, and should avoid such because of character checks, etc. For example, MySQL's <code>MD5(...)</code> yields a fixed-length 32-byte hex string. <code>SHA1(...)</code> gives a 40-byte hex string. This could be stored into <code>CHAR(32) CHARACTER SET ascii</code> (or 40 for sha1).

Or, better yet, store <code>UNHEX(MD5(...))</code> into <code>BINARY(16)</code>. This cuts in half the size of the column. (It does, however, make it rather unprintable.) <code>SELECT HEX(hash) ...</code> if you want it readable.

Comparing two <code>BINARY</code> columns has no collation issues.

blocks|key|3038764|text|排序问题的另一个来源是mysql.proc表。检查存储过程和函数的排序规则：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3038765|SELECT
++p.db,+p.db_collation,+p.type,+COUNT(*)+cnt
FROM+mysql.proc+p
GROUP+BY+p.db,+p.db_collation,+p.type;|code-block|syntax|javascript|3038766|还要注意mysql.proc.collation_connection和mysql.proc.character_set_client列。|3038767|entityMap^0|B|A|0|0|4|V|10|V|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

One another source of the issue with collations is <code>mysql.proc</code> table. Check collations of your storage procedures and functions:

<pre><code>SELECT
 p.db, p.db_collation, p.type, COUNT(*) cnt
FROM mysql.proc p
GROUP BY p.db, p.db_collation, p.type;
</code></pre>

Also pay attention to <code>mysql.proc.collation_connection</code> and <code>mysql.proc.character_set_client</code> columns.

blocks|key|3038769|text|我遇到了一个类似的问题，我试图使用带有字符串变量的FIND_IN_SET过程。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3038770|SET+@my_var+=+'string1,string2';
SELECT+*+from+my_table+WHERE+FIND_IN_SET(column_name,@my_var);|code-block|syntax|javascript|3038771|并接收到错误|3038772|3038773|错误代码:+1267。操作'find_in_set‘的排序规则(utf8_unicode_ci，IMPLICIT)和(utf8_general_ci，IMPLICIT)的混合非法|blockquote|3038774|3038775|3038776|简短回答：|3038777|不需要更改任何collation_YYYY变量，只需在变量声明旁边添加正确的排序规则即可。|3038778|SET+@my_var+=+'string1,string2'+COLLATE+utf8_unicode_ci;
SELECT+*+from+my_table+WHERE+FIND_IN_SET(column_name,@my_var);|3038779|长篇答案：|3038780|我首先检查了排序规则变量：|3038781|mysql>+SHOW+VARIABLES+LIKE+'collation%25';
++++%2B----------------------%2B-----------------%2B
++++%7C+Variable_name++++++++%7C+Value+++++++++++%7C
++++%2B----------------------%2B-----------------%2B
++++%7C+collation_connection+%7C+utf8_general_ci+%7C
++++%2B----------------------%2B-----------------%2B
++++%7C+collation_database+++%7C+utf8_general_ci+%7C
++++%2B----------------------%2B-----------------%2B
++++%7C+collation_server+++++%7C+utf8_general_ci+%7C
++++%2B----------------------%2B-----------------%2B|3038782|然后我检查了表排序规则：|3038783|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;|3038784|这意味着我的变量被配置为缺省归类utf8_general_ci，而我的表被配置为utf8_unicode_ci.|3038785|通过在变量声明旁边添加COLLATE命令，变量collation匹配为表配置的COLLATE。|3038786|entityMap^0|M|2|0|0|0|0|0|0|0|0|5|0|16|3|0|0|0|5|0|0|D|0|0|0|C|0|0|0|1K|0|0|1B|0^^$0|@$1|2|3|4|5|6|7|1E|8|@$9|1F|A|1G|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|1H|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1I|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|1J|8|@]|D|@]|E|$]]|$1|N|3|O|5|P|7|1K|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1L|8|@]|D|@]|E|$]]|$1|R|3|-4|5|6|7|1M|8|@]|D|@]|E|$]]|$1|S|3|T|5|6|7|1N|8|@$9|1O|A|1P|B|C]]|D|@]|E|$]]|$1|U|3|V|5|6|7|1Q|8|@$9|1R|A|1S|B|C]]|D|@]|E|$]]|$1|W|3|X|5|H|7|1T|8|@]|D|@]|E|$I|J]]|$1|Y|3|Z|5|6|7|1U|8|@$9|1V|A|1W|B|C]]|D|@]|E|$]]|$1|10|3|11|5|6|7|1X|8|@$9|1Y|A|1Z|B|C]]|D|@]|E|$]]|$1|12|3|13|5|H|7|20|8|@]|D|@]|E|$I|J]]|$1|14|3|15|5|6|7|21|8|@$9|22|A|23|B|C]]|D|@]|E|$]]|$1|16|3|17|5|H|7|24|8|@]|D|@]|E|$I|J]]|$1|18|3|19|5|6|7|25|8|@$9|26|A|27|B|C]]|D|@]|E|$]]|$1|1A|3|1B|5|6|7|28|8|@$9|29|A|2A|B|C]]|D|@]|E|$]]|$1|1C|3|-4|5|6|7|2B|8|@]|D|@]|E|$]]]|1D|$]]

I had a similar problem, was trying to use the FIND_IN_SET procedure with a string variable.

<pre><code>SET @my_var = 'string1,string2';
SELECT * from my_table WHERE FIND_IN_SET(column_name,@my_var);
</code></pre>

and was receiving the error 

<blockquote>
 Error Code: 1267. Illegal mix of collations (utf8_unicode_ci,IMPLICIT)
 and (utf8_general_ci,IMPLICIT) for operation 'find_in_set'
</blockquote>

Short answer:

No need to change any collation_YYYY variables, just add the correct collation next to your variable declaration, i.e.

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

Long answer:

I first checked the collation variables:

<pre><code>mysql&gt; SHOW VARIABLES LIKE 'collation%';
 +----------------------+-----------------+
 | Variable_name | Value |
 +----------------------+-----------------+
 | collation_connection | utf8_general_ci |
 +----------------------+-----------------+
 | collation_database | utf8_general_ci |
 +----------------------+-----------------+
 | collation_server | utf8_general_ci |
 +----------------------+-----------------+
</code></pre>

Then I checked the table collation:

<pre><code>mysql&gt; 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;
</code></pre>

This means that my variable was configured with the default collation of utf8_general_ci while my table was configured as utf8_unicode_ci.

By adding the COLLATE command next to the variable declaration, the variable collation matched the collation configured for the table.

blocks|key|305983|text|这段代码需要放在Run+SQL+query/queries+on+database中|type|unstyled|depth|inlineStyleRanges|entityRanges|data|305984|SQL+QUERY+WINDOW|offset|length|305985|ALTER+TABLE+`table_name`+CHANGE+`column_name`+`column_name`+++VARCHAR(128)+CHARACTER+SET+utf8+COLLATE+utf8_unicode_ci+NULL+DEFAULT+NULL;|code-block|syntax|javascript|305986|请将table_name和column_name替换为适当的名称。|305987|entityMap|0|LINK|mutability|MUTABLE|url|https://i.stack.imgur.com/gjGq7.gif^0|0|0|G|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@$D|W|E|X|1|Y]]|A|$]]|$1|F|3|G|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

This code needs to be put inside Run SQL query/queries on database

<a href="https://i.stack.imgur.com/gjGq7.gif" rel="nofollow noreferrer">SQL QUERY WINDOW</a>

<pre><code>ALTER TABLE `table_name` CHANGE `column_name` `column_name` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
</code></pre>

Please replace table_name and column_name with appropriate name.

blocks|key|305090|text|如果您安装了phpMyAdmin，则可以按照以下链接中的说明进行操作：https://mediatemple.net/community/products/dv/204403914/default-mysql-character-set-and-collation您必须将数据库的排序规则与所有表的排序规则以及表中的字段进行匹配，然后重新编译所有存储过程和函数。有了这些，一切都应该可以正常工作了。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|305091|entityMap|0|LINK|mutability|MUTABLE|url|https://mediatemple.net/community/products/dv/204403914/default-mysql-character-set-and-collation^0|Z|2P|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

If you have phpMyAdmin installed, you can follow the instructions given in the following link: <a href="https://mediatemple.net/community/products/dv/204403914/default-mysql-character-set-and-collation" rel="nofollow noreferrer">https://mediatemple.net/community/products/dv/204403914/default-mysql-character-set-and-collation</a> You have to match the collate of the database with that of all the tables, as well as the fields of the tables and then recompile all the stored procedures and functions. With that everything should work again.

blocks|key|302877|text|非常有趣..。现在，准备好。我看了所有的“添加整理”解决方案，对我来说，这些都是创可贴的修复。事实上，数据库的设计是“糟糕的”。是的，标准的改变和新的东西被添加了，诸如此类，但这并不能改变糟糕的数据库设计事实。我拒绝在SQL语句中添加"collate“，仅仅是为了让我的查询正常工作。对于我来说，唯一有效的解决方案是重新设计数据库/表，以匹配我将长期使用和接受的字符集，并且几乎消除了将来调整代码的需要。在本例中，我选择使用字符集"utf8mb4“。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|302878|因此，当您遇到“非法”错误消息时，这里的解决方案是重新设计您的数据库和表。这比听起来要容易得多，也更快。从CSV导出数据并重新导入可能甚至不是必需的。更改数据库的字符集，并确保表的所有字符集都匹配。|302879|使用以下命令可指导您：|302880|SHOW+VARIABLES+LIKE+"collation_database";
SHOW+TABLE+STATUS;|code-block|syntax|javascript|302881|现在，如果你喜欢在这里和那里添加“校对”，并使用forces+fulls+"overrides“来增强你的代码，那就是我的猜测。|302882|entityMap^0|60|7|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|W|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|X|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|Y|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|R|$]]

Very interesting... Now, be ready. I looked at all of the "add collate" solutions and to me, those are band aid fixes. The reality is the database design was "bad". Yes, standard changes and new things gets added, blah blah, but it does not change the bad database design fact. I refuse to go with the route of adding "collate" all over the SQL statements just to get my query to work. The only solution that works for me and will virtually eliminate the need to tweak my code in the future is to re-design the database/tables to match the character set that I will live with and embrace for the long term future. In this case, I choose to go with the character set "utf8mb4".

So the solution here when you encounter that "illegal" error message is to re-design your database and tables. It is much easier and quicker then it sounds. Exporting your data and re-importing it from a CSV may not even be required. Change the character set of the database and make sure all the character set of your tables matches.

Use these commands to guide you:

<pre><code>SHOW VARIABLES LIKE "collation_database";
SHOW TABLE STATUS;
</code></pre>

Now, if you enjoy adding "collate" here and there and beef up your code with forces fulls "overrides", be my guess.

blocks|key|3253148|text|下面的解决方案对我很有效。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3253149|CONVERT(+Table1.FromColumn+USING+utf8)++++=++CONVERT(Table2.ToColumn+USING+utf8)+|code-block|syntax|javascript|3253150|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Below solution worked for me.
<pre><code>CONVERT( Table1.FromColumn USING utf8) = CONVERT(Table2.ToColumn USING utf8) 
</code></pre>

blocks|key|306054|text|我个人在一次手术中遇到了这个问题。如果你不想使用alter+table，你可以试着把你的参数转换成过程。我已经尝试了几种collate的用法(在select中设置了一个集合)，但对我都不起作用。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|306055|CONVERT(my_param+USING+utf32)做到了这一点。|306056|entityMap^0|O|B|0|0|T|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

I personnaly had this problem in a procedure.
If you dont want to <code>alter table</code> you can try to convert your parameter into the procedure .
I've try sevral use of collate (with a set into the select) but none works for me.
<code>CONVERT(my_param USING utf32) </code> did the trick.

blocks|key|305200|text|在我的例子中，函数的默认返回类型是type/collation+from+database+(utf8mb4_general_ci)，但是数据库列是ascii。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|305201|WHERE+ascii_col+=+md5(concat_ws(',',+a,b,c))|code-block|syntax|javascript|305202|快速解决方案是|305203|WHERE+ascii_col+=+BINARY+md5(concat_ws(',',+a,b,c))|305204|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

In my case the default return type of a function was the type/collation from database (utf8mb4_general_ci) but database column was ascii.
<pre><code>WHERE ascii_col = md5(concat_ws(',', a,b,c))
</code></pre>
Quick fix was
<pre><code>WHERE ascii_col = BINARY md5(concat_ws(',', a,b,c))
</code></pre>

Am getting the below error when trying to do a select through a stored procedure in MySQL.

<blockquote>
 Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='
</blockquote>

Any idea on what might be going wrong here?

The collation of the table is <code>latin1_general_ci</code> and that of the column in the where clause is <code>latin1_general_cs</code>.

Troubleshooting "Illegal mix of collations" error in mysql

数据库

服务器

MySQL

尝试通过MySQL中的存储过程执行select操作时出现以下错误。对于操作'=‘，排序规则(latin1_general_cs，IMPLICIT)和(latin1_general_ci，IMPLICIT)的非法混合你知道这里可能出了什么问题吗？表的排序规则为latin1_general_ci，where子句中列的排序规...

问排除mysql中的“非法混合排序规则”错误
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问排除mysql中的“非法混合排序规则”错误EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问排除mysql中的“非法混合排序规则”错误
EN