MySql 中的非法混合排序规则错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1241856/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Illegal mix of collations error in MySql
提问by Oliver
Just got this answer from a previous question and it works a treat!
刚刚从上一个问题中得到了这个答案，它很有用！
SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount
FROM ratings WHERE month='Aug' GROUP BY username HAVING TheCount > 4
ORDER BY TheAverage DESC, TheCount DESC
But when I stick this extra bit in it gives this error:
但是当我把这个额外的位插入它时会出现这个错误：
Documentation #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='
文档 #1267 - 操作 '=' 的排序规则 (latin1_swedish_ci,IMPLICIT) 和 (latin1_general_ci,IMPLICIT) 的非法混合
SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount FROM
ratings WHERE month='Aug'
**AND username IN (SELECT username FROM users WHERE gender =1)**
GROUP BY username HAVING TheCount > 4 ORDER BY TheAverage DESC, TheCount DESC
The table is:
表是：
id, username, rating, month
id, username, rating, month
采纳答案by Oliver
Check the collation type of each table, and make sure that they have the same collation.
检查每个表的排序规则类型，并确保它们具有相同的排序规则。
After that check also the collation type of each table field that you have use in operation.
之后还要检查您在操作中使用的每个表字段的排序规则类型。
I had encountered the same error, and that tricks works on me.
我遇到了同样的错误，这个技巧对我有用。
回答by Dean Rather
Here's how to check which columns are the wrong collation:
以下是检查哪些列的排序规则错误的方法：
SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE collation_name = 'latin1_general_ci'
ORDER BY table_schema, table_name,ordinal_position;
And here's the query to fix it:
这是修复它的查询：
ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin1 COLLATE 'latin1_swedish_ci';
回答by RotS
[MySQL]
[MySQL]
In these (very rare) cases:
在这些（非常罕见的）情况下：
- two tables that really need different collation types
values not coming from a table, but from an explicit enumeration, for instance:
SELECT 1 AS numbers UNION ALL SELECT 2 UNION ALL SELECT 3
- 两个真正需要不同归类类型的表
值不是来自表，而是来自显式枚举，例如：
选择 1 作为数字 UNION ALL SELECT 2 UNION ALL SELECT 3
you can compare the values between the different tables by using CAST or CONVERT:
您可以使用 CAST 或 CONVERT 比较不同表之间的值：
CAST('my text' AS CHAR CHARACTER SET utf8)
CONVERT('my text' USING utf8)
See CONVERT and CAST documentationon MySQL website.
请参阅MySQL 网站上的CONVERT 和 CAST 文档。
回答by pal4life
I was getting this same error on PhpMyadmin and did the solution indicated here which worked for me
我在 PhpMyadmin 上遇到了同样的错误，并执行了此处指出的对我有用的解决方案
ALTER TABLE table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
Illegal mix of collations MySQL ErrorAlso I would recommend going with General instead of swedish since that one is default and not to use the language unless your application is using Swedish.
排序规则的非法混合 MySQL 错误另外，我建议使用 General 而不是 swedish，因为这是默认设置，除非您的应用程序使用瑞典语，否则不要使用该语言。
回答by Quy Le
I think you should convert to utf8
我认为你应该转换为 utf8
--set utf8 for connection
SET collation_connection = 'utf8_general_ci'
--change CHARACTER SET of DB to utf8
ALTER DATABASE dbName CHARACTER SET utf8 COLLATE utf8_general_ci
--change CHARACTER SET of table to utf8
ALTER TABLE tableName CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
回答by Roberto Camargo
I got this same error inside a stored procedure, in the where clause. i discovered that the problem ocurred with a local declared variable, previously loaded by the same table/column.
我在存储过程中的 where 子句中遇到了同样的错误。我发现问题出在本地声明的变量上，该变量以前由同一个表/列加载。
I resolved it casting the data to single char type.
我解决了将数据转换为单字符类型的问题。
回答by Pinkeye
In short, this error is caused by MySQL trying to do an operation on two things which have different collation settings. If you make the settings match, the error will go away. Of course, you need to choose the right setting for your database, depending on what it is going to be used for.
简而言之，此错误是由于 MySQL 尝试对具有不同排序规则设置的两个事物执行操作引起的。如果您使设置匹配，错误就会消失。当然，您需要为您的数据库选择正确的设置，这取决于它的用途。
Here's some good advice on choosing between two very common utf8 collations: What's the difference between utf8_general_ci and utf8_unicode_ci
这里有一些关于在两种非常常见的 utf8 排序规则之间进行选择的好建议：utf8_general_ci 和 utf8_unicode_ci 之间有什么区别
If you are using phpMyAdmin you can do this systematically by working through the tables mentioned in your error message, and checking the collation type for each column. First you should check which is the overall collation setting for your database - phpMyAdmin can tell you this and change it if necessary. But each column in each table can have its own setting. Normally you will want all these to match.
如果您正在使用 phpMyAdmin，您可以通过查看错误消息中提到的表并检查每列的排序规则类型来系统地执行此操作。首先，您应该检查数据库的总体排序规则设置 - phpMyAdmin 可以告诉您这一点，并在必要时进行更改。但是每个表中的每一列都可以有自己的设置。通常你会希望所有这些都匹配。
In a small database this is easy enough to do by hand, and in any case if you read the error message in full it will usually point you to the right place. Don't forget to look at the 'structure' settings for columns with subtables in as well. When you find a collation that does not match you can change it using phpMyAdmin directly, no need to use the query window. Then try your operation again. If the error persists, keep looking!
在小型数据库中，这很容易手动完成，并且在任何情况下，如果您完整阅读错误消息，它通常会将您指向正确的位置。不要忘记查看包含子表的列的“结构”设置。当您发现不匹配的排序规则时，您可以直接使用 phpMyAdmin 更改它，无需使用查询窗口。然后再次尝试您的操作。如果错误仍然存在，请继续查找！
回答by kaushi
I also got same error, but in my case main problem was in wherecondition the parameter that i'm checking was having some unknown hidden character (+%A0)
我也得到了同样的错误，但对我来说主要的问题是在哪里条件是我检查了有一些未知的隐藏字符的参数（+％A0）
When A0convert I got 160 but 160 was out of the range of the character that db knows, that's why database cannot recognize it as character other thing is my table column is varchar
当A0转换时，我得到了 160，但 160 超出了 db 知道的字符范围，这就是为什么数据库无法将其识别为字符的原因是我的表列是 varchar
the solution that I did was I checked there is some characters like that and remove those before run the sql command
ex:- preg_replace('/\D/', '', $myParameter);
我所做的解决方案是我检查了一些类似的字符并在运行 sql 命令之前删除它们
例如：- preg_replace('/\D/', '', $myParameter);
回答by reds
The problem here mainly, just Cast the field like this cast(field as varchar) or cast(fields as date)
这里的问题主要是，只需像这样 cast(field as varchar) 或 cast(fields as date) 一样投射字段
回答by Swadesh
You need to change each column Collationfrom latin1_general_ci to latin1_swedish_ci
您需要将每一列排序规则从 latin1_general_ci更改为 latin1_swedish_ci
