MySQL非法混合排序规则

Question

查看我的产品日志后，我提到了一些错误：

[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 '='

Alghout我在cfg教义下拥有UTF-8默认值：

doctrine:
    dbal:
        charset:  UTF8

看来我所有的MySQL表都在其中latin1_swedish_ci，所以我的问题是：

我可以utf8_general_ci为所有表手动将排序规则更改为，而没有任何复杂性/注意事项吗？

Answer 1

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

• 甲 字符编码 信息的每个符号是如何以二进制表示（并因此存储在计算机）。例如，符号é（U + 00E9，拉丁小字母E急性）被编码为0xc3a9在UTF-8 （它的MySQL呼叫utf8）和0xe9在窗口1252（其MySQL调用latin1）。

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

• 甲 核对 是在一个字符集的排序，以使字符串进行比较。例如：MySQL的latin1_swedish_ci排序规则将字符的大多数重音变体视为与基本字符等效，而它的latin1_general_ci归类将在下一个基本字符之前对它们进行排序，但不等效（还有其他更重要的区别：例如字符的顺序）喜欢å，ä，ö和ß）。

MySQL将决定应将哪种排序规则应用于给定的表达式，如在“
排序规则的表达式”中所述：特别是，列的排序规则优先于字符串文字的排序规则。

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

1. 中的一个值fos_user.username，以列的字符集（Windows-1252）编码，并表示对其排序规则的偏爱latin1_swedish_ci（强制性值为2）；与

2. 字符串文字'Nrv⧧Kasi'，编码为连接的字符集（UTF-8，由Doctrine配置），并表示对连接的排序规则的偏爱utf8_general_ci（强制性值为4）。

由于这些字符串中的第一个比第二个具有较低的矫顽力值，因此MySQL尝试使用该字符串的排序规则执行比较latin1_swedish_ci。为此，MySQL尝试将第二个字符串转换为latin1-但由于该⧧字符集中不存在该字符，因此比较失败。

警告

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

如果您认为该列 确实
包含此类字符，那么您可能在连接字符编码设置为某种字符（例如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语法所示：

要更改默认的表字符集和所有字符列（CHAR，VARCHAR，TEXT）到一个新的字符集，可使用如下语句：

ALTER TABLE **_tbl_name_** 转换为字符集 ** _charset_name_** ;

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

为避免数据类型更改为刚刚描述的类型，请勿使用CONVERT TO CHARACTER SET。而是使用MODIFY更改单个列。