MySQL 不合法的字符集混合

20 浏览
0 Comments

MySQL 不合法的字符集混合

在查看了我的生产日志后，我发现出现了一些错误提及：

[2012-08-31 15:56:43] request.CRITICAL: Doctrine\DBAL\DBALException: 
An exception occurred while executing 'SELECT t0.username ....... FROM fos_user t0 WHERE t0.username = ?'
with params {"1":"Nrv\u29e7Kasi"}:
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (utf8_general_ci,COERCIBLE) for operation '='

虽然在Doctrine配置中我使用了UTF-8默认值：

doctrine:
    dbal:
        charset:  UTF8

但是似乎我所有的MySQL表都使用的是latin1_swedish_ci编码，我的问题是：

我可以手动更改所有表的编码为utf8_general_ci吗，而不会引起任何问题或需要注意的事项吗？

admin 更改状态以发布 2023年5月21日
0

2 答案

0 Comments

没错。我也遇到过这个问题，最好的快速解决方案是：

         CONVERT(fos_user.username USING utf8)

0
0 Comments

理解以下定义是很有帮助的：

  • 字符编码详细说明每个符号在二进制中的表示方式（因此存储在计算机中）。例如，符号é(U+00E9，带有重音的小写拉丁字母 E)在UTF-8编码中（MySQL称之为utf8）被编码为0xc3a9，在Windows-1252编码中（MySQL称之为latin1）被编码为0xe9。

  • 字符集是使用给定字符编码可以表示的符号的字母表。令人困惑的是，该术语也被用来表示与字符编码相同的含义。

  • 排序规则是在一个字符集上对字符串进行排序的方法，以便可以比较字符串。例如：MySQL的latin1_swedish_ci排序规则将大多数音调变化的字符视为与基本字符等效，而其latin1_general_ci 排序规则将其排序在下一个基本字符之前但不等效（还有其他更重大的差异：例如å、ä、ö和ß等字符的顺序）。

如表达式的排序规则所述，MySQL将决定应用于特定表达式的排序规则：特别是，一个列的排序规则优先于一个字符串文字的排序规则。

查询语句的WHERE子句比较以下字符串：

  1. fos_user.username列中以该列字符集（Windows-1252）编码且表达其排序规则为latin1_swedish_ci（具有强制性值2）的值；以及

  2. 在连接的字符集（由Doctrine配置的UTF-8）中编码且表达其偏好排序规则为连接的排序规则utf8_general_ci（具有强制性值4）的字符串文字'Nrv⧧Kasi'。

由于第一个字符串的强制性值低于第二个字符串，MySQL尝试使用该字符串的排序规则：latin1_swedish_ci。为此，MySQL尝试将第二个字符串转换为latin1，但由于⧧字符不存在于该字符集中，因此比较失败。

警告

应该暂停一下，考虑列当前的编码方式：您尝试过滤包含在该列中无法存在的字符的记录！

如果您认为该列确实包含这样的字符，那么您可能是在设置连接字符编码为某些内容（例如latin1）的情况下写入了该列，导致MySQL将接收到的字节序列解释为Windows-1252字符集中的所有字符。

如果是这种情况，在继续之前，您应该修复数据！

  1. 将这些列转换为用于数据插入的字符编码（如果与现有编码不同）：

    ALTER TABLE fos_users MODIFY username VARCHAR(123) CHARACTER SET foo;

  2. 通过将它们转换为binary字符集来删除与这些列关联的编码信息：

    ALTER TABLE fos_users MODIFY username VARCHAR(123) CHARACTER SET binary;

  3. 与这些列关联实际传输数据的编码相对应，将它们转换为相关的字符集。

    ALTER TABLE fos_users MODIFY username VARCHAR(123) CHARACTER SET bar;

请注意，如果从多字节编码转换，您可能需要增加列的大小（甚至更改其类型），以容纳已转换字符串的最大可能长度。

一旦确定列已正确编码，您可以通过以下一种方式强制对比使用Unicode排序规则：

  • 显式将值fos_user.username转换为Unicode字符集：

    WHERE CONVERT(fos_user.username USING utf8) = ?

  • 强制字符串文本具有低于列的相互作用度值（会导致将列的值隐式转换为UTF-8）：

    WHERE fos_user.username = ? COLLATE utf8_general_ci

或者，正如您所说的那样，将列永久转换为Unicode编码并适当设置其排序规则。

我能手动将所有表的排序规则更改为utf8_general_ci而不会有任何问题/预防措施吗？

原则上，Unicode编码占用的空间比单字节字符集更多，因此：

  • 可能需要更多的存储空间；

  • 比较可能会更慢；和

  • 可能需要调整索引前缀长度（请注意，最大长度以字节为单位，因此可能表示更少的字符比以前）。

此外，请注意，正如在ALTER TABLE Syntax下记录的那样：

要更改表的默认字符集和所有字符列（CHAR，VARCHAR，TEXT）为新字符集，请使用以下语句：

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

对于数据类型为VARCHAR或其中之一的TEXT类型的列，CONVERT TO CHARACTER SET将根据需要更改数据类型，以确保新列足够长，以存储与原始列一样多的字符。例如，TEXT列有两个长度字节，可存储该列中值的字节长度，最多为65,535个字节。对于latin1TEXT列，每个字符需要一个字节，因此该列最多可以存储65,535个字符。如果将该列转换为utf8，则每个字符可能需要多达三个字节，最大可能长度为3×65,535=196，605个字节。长度将不适合TEXT列的长度字节中，因此MySQL将数据类型自动转换为MEDIUMTEXT，它是长度字节可以记录196,605个值的最小字符串类型。类似地，VARCHAR列可能会转换为MEDIUMTEXT。

为了避免数据类型的改变，不要使用 CONVERT TO CHARACTER SET。而是使用 MODIFY 来改变单个列。

0