MySQL - 用于“UNION”操作的排序规则 (utf8_general_ci,COERCIBLE) 和 (latin1_swedish_ci,IMPLICIT) 的非法混合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/183446/
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
MySQL - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'
提问by Greg
How do I fix that error once and for all? I just want to be able to do unions in MySQL.
我该如何一劳永逸地修复该错误？我只想能够在 MySQL 中进行联合。
(I'm looking for a shortcut, like an option to make MySQL ignore that issue or take it's best guess, not looking to change collations on 100s of tables ... at least not today)
（我正在寻找一种快捷方式，例如让 MySQL 忽略该问题或进行最佳猜测的选项，而不是希望更改 100 个表的排序规则……至少今天不是）
采纳答案by kristof
Not sure about mySQL but in MSSQL you can change the collation in the query so for example if you have 2 tables with different collation and you want to join them or as in you situation crate UNION you can do
不确定 mySQL，但在 MSSQL 中，您可以更改查询中的排序规则，例如，如果您有 2 个具有不同排序规则的表并且您想加入它们，或者在您的情况下，您可以使用 crate UNION
select column1 from tableWithProperCollation
union all
select column1 COLLATE SQL_Latin1_General_CP1_CI_AS from tableWithDifferentCollation
Of course SQL_Latin1_General_CP1_CI_AS is just an example of collation you want to "convert" to
当然 SQL_Latin1_General_CP1_CI_AS 只是您要“转换”为的排序规则示例
回答by Greg
Thanks Kristof. In this case it was being caused by selecting a literal in the first select, and not from any different table collations.
谢谢克里斯托夫。在这种情况下，它是由在第一个选择中选择文字引起的，而不是来自任何不同的表排序规则。
Ironically I got it working by following this old blog postI made for that issue.
具有讽刺意味的是，我按照我为该问题制作的这篇旧博客文章使其工作。
回答by KevinR
A fix I found that seems to be an easy fix is to alter the entire database that's giving you problems. I'm thinking this might not be the best way to do it, but it works for me and it's easy. I rune this command in MySQL:
我发现一个看似简单的修复方法是更改给您带来问题的整个数据库。我认为这可能不是最好的方法，但它对我有用，而且很容易。我在 MySQL 中运行这个命令：
ALTER DATABASE databasename COLLATE utf8_unicode_ci;
