MySQL 非法混合排序规则

One should pause for a moment to consider how the column is currently encoded: you are attempting to filter for records where fos_user.username is equal to a string that contains a character which cannot exist in that column!

如果您认为该列确实包含这样的字符，那么您可能在连接字符编码设置为某些(例如 latin1)时写入该列，导致MySQL 将接收到的字节序列解释为 Windows-1252 字符集中的字符.

If you believe that the column does contain such characters, then you probably wrote to the column whilst the connection character encoding was set to something (e.g. latin1) that caused MySQL to interpret the received byte sequence as characters which are all in the Windows-1252 character set.

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

If this is the case, before continuing any further you should fix your data!

1. 将此类列转换为用于数据插入的字符编码(如果与现有编码不同):

1. 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;

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

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

请注意，如果从多字节编码转换，您可能需要增加列的大小(甚至更改其类型)以适应转换后字符串的最大可能长度.

Note that, if converting from a multi-byte encoding, you may need to increase the size of the column (or even change its type) in order to accomodate the maximum possible length of the converted string.

一旦确定列的编码正确，就可以强制使用 Unicode 排序规则进行比较—

Once one is certain that the columns are correctly encoded, one could force the comparison to be conducted using a Unicode collation by either—

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

• explicitly converting the value fos_user.username to a Unicode character set:

WHERE CONVERT(fos_user.username USING utf8) = ?

WHERE fos_user.username = ? COLLATE utf8_general_ci

或者，如您所说，可以将列永久转换为 Unicode 编码并适当设置其排序规则.

Or one could, as you say, permanently convert the column(s) to a Unicode encoding and set its collation appropriately.

我可以手动将所有表的排序规则更改为 utf8_general_ci 而不需要任何复杂性/预防措施吗?

Can I manually change the collation to utf8_general_ci for all my tables without any complications/precautions ?

主要考虑是Unicode编码比单字节字符集占用更多空间，因此:

The principle consideration is that Unicode encodings take up more space than single-byte character sets, so:

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

• more storage may be required;

比较可能会更慢；和

索引前缀长度可能需要调整(注意最大值以字节为单位，因此可能比以前表示的字符更少).

index prefix lengths may need to be adjusted (note that the maximum is in bytes, so may represent fewer characters than previously).

另外，请注意，如ALTER TABLE语法:

Also, be aware that, as documented under ALTER TABLE Syntax:

更改表默认字符集和所有字符列(CHAR、VARCHAR、TEXT) 到一个新的字符集，使用这样的语句:

To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET 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.

For a column that has a data type of VARCHAR or one of the TEXT types, CONVERT TO CHARACTER SET will change the data type as necessary to ensure that the new column is long enough to store as many characters as the original column. For example, a TEXT column has two length bytes, which store the byte-length of values in the column, up to a maximum of 65,535. For a latin1 TEXT column, each character requires a single byte, so the column can store up to 65,535 characters. If the column is converted to utf8, each character might require up to three bytes, for a maximum possible length of 3 × 65,535 = 196,605 bytes. That length will not fit in a TEXT column's length bytes, so MySQL will convert the data type to MEDIUMTEXT, which is the smallest string type for which the length bytes can record a value of 196,605. Similarly, a VARCHAR column might be converted to MEDIUMTEXT.

为了避免刚刚描述的类型的数据类型更改，不要使用CONVERT TO CHARACTER SET.相反，使用 MODIFY 来更改单个列.

To avoid data type changes of the type just described, do not use CONVERT TO CHARACTER SET. Instead, use MODIFY to change individual columns.

这篇关于MySQL 非法混合排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！