排序规则的非法混合 MySQL 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1008287/
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 MySQL Error
提问by Click Upvote
I'm getting this strange error while processing a large number of data...
我在处理大量数据时遇到这个奇怪的错误......
Error Number: 1267
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
SELECT COUNT(*) as num from keywords WHERE campaignId='12' AND LCASE(keyword)='hello again ??” ????‰ ????? ? ′?‰'
What can I do to resolve this? Can I escape the string somehow so this error wouldn't occur, or do I need to change my table encoding somehow, and if so, what should I change it to?
我能做些什么来解决这个问题？我可以以某种方式转义字符串以便不会发生此错误，或者我是否需要以某种方式更改我的表编码，如果是这样，我应该将其更改为什么？
回答by Ben Hughes
SET collation_connection = 'utf8_general_ci';
then for your databases
那么对于你的数据库
ALTER DATABASE your_database_name CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
MySQL sneaks swedish in there sometimes for no sensible reason.
MySQL有时会无缘无故地偷偷溜进来。
回答by Quassnoi
You should set both your table encoding and connection encoding to UTF-8:
您应该将表编码和连接编码设置为UTF-8：
ALTER TABLE keywords CHARACTER SET UTF8; -- run once
and
和
SET NAMES 'UTF8';
SET CHARACTER SET 'UTF8';
回答by Binaya Shrestha
CONVERT(column1 USING utf8)
Solves my problem. Where column1 is the column which gives me this error.
解决了我的问题。其中 column1 是给我这个错误的列。
回答by vpgodara
Use following statement for error
错误使用以下语句
be careful about your data take backup if data have in table.
如果数据在表中，请小心您的数据备份。
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
回答by Stephan
In general the best way is to Change the table collation. However I have an old application and are not really able to estimate the outcome whether this has side effects. Therefore I tried somehow to convert the string into some other format that solved the collation problem.
What I found working is to do the string compare by converting the strings into a hexadecimal representation of it's characters. On the database this is done with HEX(column).For PHP you may use this function:
一般来说，最好的方法是更改表排序规则。但是，我有一个旧的应用程序，无法真正估计结果是否有副作用。因此，我尝试以某种方式将字符串转换为解决整理问题的其他格式。我发现的工作是通过将字符串转换为其字符的十六进制表示来进行字符串比较。在数据库上，这是使用HEX(column).PHP完成的，您可以使用此功能：
public static function strToHex($string)
{
$hex = '';
for ($i=0; $i<strlen($string); $i++){
$ord = ord($string[$i]);
$hexCode = dechex($ord);
$hex .= substr('0'.$hexCode, -2);
}
return strToUpper($hex);
}
When doing the database query, your original UTF8 string must be converted first into an iso string (e.g. using utf8_decode()in PHP) before using it in the DB. Because of the collation type the database cannot have UTF8 characters inside so the comparism should work event though this changes the original string (converting UTF8 characters that are not existend in the ISO charset result in a ? or these are removed entirely). Just make sure that when you write data into the database, that you use the same UTF8 to ISO conversion.
在进行数据库查询时，您的原始 UTF8 字符串必须先转换为 iso 字符串（例如utf8_decode()在 PHP 中使用），然后才能在数据库中使用。由于排序规则类型，数据库内部不能包含 UTF8 字符，因此比较应该起作用，尽管这会更改原始字符串（转换 ISO 字符集中不存在的 UTF8 字符会导致 ? 或这些字符被完全删除）。只要确保在将数据写入数据库时，使用相同的 UTF8 到 ISO 转换。
回答by A Kunin
I had my table originally created with CHARSET=latin1. After table conversion to utf8some columns were not converted, however that was not really obvious.
You can try to run SHOW CREATE TABLE my_table;and see which column was not converted or just fix incorrect character set on problematic column with query below (change varchar length and CHARSET and COLLATE according to your needs):
我的表最初是用CHARSET=latin1创建的。表转换为utf8 后，某些列未转换，但这并不是很明显。您可以尝试运行SHOW CREATE TABLE my_table;并查看哪一列未转换，或者仅使用下面的查询修复有问题的列上的错误字符集（根据您的需要更改 varchar 长度和 CHARSET 和 COLLATE）：
ALTER TABLE `my_table` CHANGE `my_column` `my_column` VARCHAR(10) CHARSET utf8
COLLATE utf8_general_ci NULL;
回答by ITI
After making your corrections listed in the top answer, change the default settings of your server.
在进行最重要的答案中列出的更正后，更改服务器的默认设置。
In your "/etc/my.cnf.d/server.cnf" or where ever it's located add the defaults to the [mysqld]section so it looks like this:
在您的“ /etc/my.cnf.d/server.cnf”或它所在的任何地方，将默认值添加到[mysqld]部分，如下所示：
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
Source: https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
来源：https: //dev.mysql.com/doc/refman/5.7/en/charset-applications.html
回答by Jamsheer Mohammed
Change the character set of the table to utf8
把表的字符集改成utf8
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8
ALTER TABLE your_table_name 转换为字符集 utf8
