MySQL 'concat' 操作的排序规则的非法混合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7753608/
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 for operation 'concat'
提问by ahmed saud
I'm trying to execute this concat query in mysql
我正在尝试在 mysql 中执行这个 concat 查询
SELECT CONCAT(if(fName,fName,''),Name)
From Student
Error:
错误：
#1271 - Illegal mix of collations for operation 'concat'
#1271 - 'concat' 操作的排序规则的非法混合
回答by Johan
The charsets and/or collations you use in your connection do not match the charset/collation in your table.
您在连接中使用的字符集和/或排序规则与表中的字符集/排序规则不匹配。
There are 4 solutions:
有4种解决方案：
1- Change the charset in your connection:
1- 更改连接中的字符集：
//find out the charset used in your table.
SHOW TABLES LIKE 'student'
//set the server charset to match
SET NAMES 'charset_name' [COLLATE 'collation_name']
2- Change the charset used in your table to match the server charset:
2- 更改表中使用的字符集以匹配服务器字符集：
//find out the charset used in the server
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
//Change the charset used in the table
ALTER TABLE student ......
3- Change the default charset settings and restart MySQL
3-更改默认字符集设置并重新启动MySQL
Edit My.ini and replace the character_set_*options, so they match your tables.
编辑 My.ini 并替换character_set_*选项，使它们与您的表相匹配。
4- Change the charset settings for your connection
4- 更改连接的字符集设置
You client can override the charset and collation settings.
If it does not option 1 or 3 should fix your issue, but if the connection overrides these settings, you need to check the connection-string and edit the charset/collation settings to match your database.
您的客户端可以覆盖字符集和排序规则设置。
如果选项 1 或 3 不能解决您的问题，但如果连接覆盖了这些设置，您需要检查连接字符串并编辑字符集/排序规则设置以匹配您的数据库。
Some advice:
一些忠告：
Find a charset. I recommend UTF8and a collation: I recommend utf8_general_ci. And use those consistantly everywhere.
查找字符集。我推荐UTF8和整理：我推荐utf8_general_ci. 并在任何地方始终如一地使用它们。
回答by Anas Naguib
This is due to collections difference, you can solve by converting the two strings or columns to one collection say UTF8
这是由于集合的差异，您可以通过将两个字符串或列转换为一个集合来解决，例如UTF8
CONCAT(CAST(fName AS CHAR CHARACTER SET utf8),CAST('' AS CHAR CHARACTER SET utf8))
This will solve :)
这将解决:)
you can check more about casting in MySQL here MySQL Casting
您可以在此处查看有关MySQL 中铸造的更多信息MySQL Casting
回答by Gajahlemu
Look like you have a miss use on the ifstatement there because it will resulting an undefineddata type so the concatoperation will fail as it different in data type. Try change the query by use ifnullinstead.
看起来你对if那里的语句有一个错误的使用，因为它会产生一个undefined数据类型，所以concat操作将失败，因为它的数据类型不同。尝试改为使用更改查询ifnull。
Try this query instead:
试试这个查询：
SELECT concat(ifnull(fName,''),Name) From Student
see the demo here http://www.sqlize.com/kfy85j8f1e
在此处查看演示http://www.sqlize.com/kfy85j8f1e
for another reference read also http://forums.mysql.com/read.php?10,225982,225982#msg-225982
另请参阅http://forums.mysql.com/read.php?10,225982,225982#msg-225982
回答by 0xCAFEBABE
A concatenation can only work if the collation of all used values matches OR you use a collation that all collations are a subset of (from a logical standpoint).
只有当所有使用的值的排序规则匹配或者您使用所有排序规则都是其子集的排序规则（从逻辑的角度来看）时，串联才能工作。
If you want to concatenate text, each text should be the same collation. Take a look at the collation the database uses, then take a look at the collation that your connection uses:
如果要连接文本，每个文本应该是相同的排序规则。查看数据库使用的排序规则，然后查看您的连接使用的排序规则：
show variables like '%coll%'
The collation_connection should match the collation of the table you try to concatenate. If it doesn't, the error message will arise.
collation_connection 应该与您尝试连接的表的排序规则相匹配。如果没有，则会出现错误消息。
You can then change the connection collation to match the one of the table.
然后，您可以更改连接排序规则以匹配表之一。
回答by sztanpet
It can also be an error with your client library being too old for the mysql server.
We had a similar problem with LIKE and the character "?" and using PHP MySQL library version 5.1.52 but MySQL server version 5.5.22.
The problem has gone away upon upgrading the client library.
这也可能是因为您的客户端库对于 mysql 服务器来说太旧了。
我们对 LIKE 和字符“？”有类似的问题。并使用 PHP MySQL 库版本 5.1.52 但 MySQL 服务器版本 5.5.22。
升级客户端库后问题就消失了。
