MySQL字符集混乱的错误

Question

MySQL字符集混乱的错误

mysqlsymfonycollationsymfony-2.1fosuserbundle

19

在查看我的生产日志后，我发现有一些错误被提到：

[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 ，而无需任何复杂的操作或注意事项？

- sf_tristanb

Relevant - ChocoDeveloper

3个回答

9

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

         CONVERT(fos_user.username USING utf8)

- JGutierrezC

4

只需执行以下命令即可将表格字符集转换，

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;

- Marvin W

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- eggyal · Accepted Answer

了解以下定义会很有帮助：

字符编码详细说明了每个符号在二进制中的表示方式（因此存储在计算机中）。例如，符号é（U+00E9，带有重音的小写拉丁字母E）在UTF-8（MySQL称为utf8）中被encoded编码为0xc3a9，在Windows-1252（MySQL称为latin1）中编码为0xe9。
字符集是一组可以使用给定字符编码表示的符号字母表。令人困惑的是，该术语也用于与字符编码相同的含义。
排序规则是字符集上的一种排序方式，以便可以比较字符串。例如：MySQL的latin1_swedish_ci排序规则将大多数带重音符号的变体视为与基本字符等效，而其latin1_general_ci排序规则将它们排序到下一个基本字符之前但不是等效的（还有其他更重要的差异，例如字符å、ä、ö和ß的顺序）。

MySQL将根据表达式的排序规则中记录的方式决定应该应用哪种排序规则到给定的表达式：特别地，列的排序规则优先于字符串字面量的排序规则。

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

fos_user.username列中编码为列字符集（Windows-1252）并表示对其排序规则latin1_swedish_ci有偏好的值（具有强制转换值2）；与
字符串字面量'Nrv⧧Kasi'，使用连接的字符集（由Doctrine配置的UTF-8）进行编码，并表示对连接的排序规则utf8_general_ci有偏好（具有强制转换值4）。

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

警告

需要暂停一下，考虑一下当前列的编码方式：您正在尝试过滤记录，其中fos_user.username等于包含该列中不存在的字符的字符串！

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

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

convert such columns to the character encoding that was used on data insertion, if different to the incumbent encoding:
```
ALTER TABLE fos_users MODIFY username VARCHAR(123) CHARACTER SET foo;
```
drop the encoding information associated with such columns by converting them to the binary character set:
```
ALTER TABLE fos_users MODIFY username VARCHAR(123) CHARACTER SET binary;
```
associate with such columns the encoding in which data was actually transmitted by converting them to the relevant character set.
```
ALTER TABLE fos_users MODIFY username VARCHAR(123) CHARACTER SET bar;
```

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

如果确定列已经正确编码，可以通过以下方式强制使用Unicode排序来进行比较：

explicitly converting the value fos_user.username to a Unicode character set:
```
WHERE CONVERT(fos_user.username USING utf8) = ?
```
forcing the string literal to have a lower coercibility value than the column (will cause an implicit conversion of the column's value to 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来更改单个列。