非法混合排序规则 MySQL 错误

Question

我在处理大量数据时遇到这个奇怪的错误...

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 昔 ã‹ã‚‰ ã‚ã‚‹ å ´æ‰€'

我该怎么解决这个问题？ 我可以以某种方式转义字符串，这样就不会发生此错误，或者我是否需要以某种方式更改我的表编码，如果是这样，我应该将其更改为什么？

Answer 1

将表的字符集改为utf8

更改表 your_table_name 转换为字符集 utf8

Answer 2

进行最上面答案中列出的更正后，更改服务器的默认设置。

在“/etc/my.cnf.d/server.cnf”或其所在位置将默认值添加到 [mysqld] 部分，如下所示：

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

来源： https://dev.mysql.com/doc/ refman/5.7/en/charset-applications.html

Answer 3

SET collation_connection = 'utf8_general_ci';

然后，对于您的数据库，

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 有时会无缘无故地偷偷地在其中添加瑞典语。

Answer 4

CONVERT(column1 USING utf8)

解决了我的问题。 其中column1是给我这个错误的列。

Answer 5

您应该将表编码和连接编码设置为 UTF-8

ALTER TABLE keywords CHARACTER SET UTF8; -- run once

：

SET NAMES 'UTF8';
SET CHARACTER SET 'UTF8';

Answer 6

使用以下语句来解决错误，

如果数据存在于表中，请小心您的数据并进行备份。

 ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Answer 7

我发现使用 cast() 对我来说是最好的解决方案：

cast(Format(amount, "Standard") AS CHAR CHARACTER SET utf8) AS Amount

还有一个 convert() 函数。 更多详细信息此处

另一个资源此处

Answer 8

一般来说，最好的方法是更改​​表排序规则。 然而，我有一个旧的应用程序，并不能真正估计结果是否有副作用。 因此，我尝试以某种方式将字符串转换为解决排序问题的其他格式。
我发现有效的方法是通过将字符串转换为其字符的十六进制表示来进行字符串比较。 在数据库上，这是通过 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);
}

在进行数据库查询时，必须首先将原始 UTF8 字符串转换为 iso 字符串（例如使用 utf8_decode()（PHP 中）），然后在数据库中使用它。 由于排序规则类型，数据库内部不能包含 UTF8 字符，因此比较应该起作用，尽管这会更改原始字符串（转换 ISO 字符集中不存在的 UTF8 字符会导致 ? 或这些字符被完全删除）。 只需确保将数据写入数据库时​​，使用相同的 UTF8 到 ISO 转换即可。

Answer 9

我的表格最初是用 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;

Answer 10

我的用户帐户没有更改数据库和表的权限，如本解决方案中的建议。

如果像我一样，您不关心 字符排序规则（您正在使用 ' =' 运算符），您可以应用反向修复。 在 SELECT 之前运行此命令：

SET collation_connection = 'latin1_swedish_ci';