mysql - 有没有办法将所有现有的表数据转换为 UTF8 排序规则？

Question

我正在协助将数据库从 MySQL 4 升级到 MySQL 5.5。我客户的应用服务器也从JDK 5升级到JDK 7。应用程序运行但是在执行数据库操作时抛出很多异常。

我发现升级后的数据库在表排序规则和/或表列排序规则中混合使用了 Latin1 generic、Latin1 Swedish 和 UTF8 generic，因此大多数 JOIN 查询都失败了。

有几百张表，几千个表字段，手工全部转换会很困难。

有没有更方便的方法将所有数据表和所有列转换为相同的排序规则？

谢谢。

编辑:显示 JOIN 查询失败的 SQLException 消息示例:

“非法混合排序规则 (latin1_general_ci,IMPLICIT) 和 (utf8_general_ci,COERCIBLE) 用于操作 '='”

Answer 1

混合使用字符集不应导致查询失败，因为 MySQL 应根据需要在字符集之间进行转换。

但是，如 ALTER TABLE Syntax 中所述:

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 <strong><em>tbl_name</em></strong> CONVERT TO CHARACTER SET <strong><em>charset_name</em></strong>;

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.

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

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(<strong><em>M</em></strong>) CHARACTER SET utf8;

If you specify CONVERT TO CHARACTER SET binary, the CHAR, VARCHAR, and TEXT columns are converted to their corresponding binary string types (BINARY, VARBINARY, BLOB). This means that the columns no longer will have a character set and a subsequent CONVERT TO operation will not apply to them.

If charset_name is DEFAULT, the database character set is used.

 Warning 

The CONVERT TO operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8). In this case, you have to do the following for each such column:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

The reason this works is that there is no conversion when you convert to or from BLOB columns.