将 mysql 表从 latin1 转换为 utf8
我正在尝试将一些 mysql 表从 latin1 转换为 utf8。我正在使用以下命令,该命令似乎大部分有效。
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
但是,在一张表上,我收到有关重复键条目的错误。这是由“名称”字段上的唯一索引引起的。似乎在转换为 utf8 时,任何“特殊”字符都会被索引为其直接的英语等效字符。例如,已经存在一条名称字段值为“Dru”的记录。转换为 utf8 时,带有“Drü”的记录被视为重复。 “Patrick”和“Påtrìçk”也是如此。
以下是重现该问题的方法:
CREATE TABLE `example` ( `name` char(20) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO example (name) VALUES ('Drü'),('Dru'),('Patrick'),('Påtrìçk');
ALTER TABLE example convert to character set utf8 collate utf8_general_ci;
ERROR 1062 (23000): Duplicate entry 'Dru' for key 1
I'm trying to convert some mysql tables from latin1 to utf8. I'm using the following command, which seems to mostly work.
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
However, on one table I get an error about a duplicate key entry. This is caused by a unique index on a "name" field. It seems when converting to utf8, any "special" characters are indexed as their straight english equivalent. For example, there is already a record with a name field value of "Dru". When converting to utf8, a record with "Drü" is considered a duplicate. The same with "Patrick" and "Påtrìçk".
Here is how to reproduce the issue:
CREATE TABLE `example` ( `name` char(20) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO example (name) VALUES ('Drü'),('Dru'),('Patrick'),('Påtrìçk');
ALTER TABLE example convert to character set utf8 collate utf8_general_ci;
ERROR 1062 (23000): Duplicate entry 'Dru' for key 1
字符串
'Drü'
和'Dru'
计算结果相同的原因是,在utf8_general_ci
排序规则中,它们算作“相同” ”。字符集排序规则的目的是提供一组规则,例如字符串何时相同、何时一个字符串排在另一个字符串之前等等。如果您想要一组不同的比较规则,则需要选择不同的排序规则。您可以通过发出
SHOW COLLATION LIKE 'utf8%'
来查看utf8
字符集的可用排序规则。有很多用于主要使用特定语言的文本的排序规则;还有utf8_bin
排序规则,它将所有字符串作为二进制字符串进行比较(即将它们作为 0 和 1 的序列进行比较)。The reason why the strings
'Drü'
and'Dru'
evaluate as the same is that in theutf8_general_ci
collation, they count as "the same". The purpose of a collation for a character set is to provide a set of rules as to when strings are the same, when one sorts before the other, and so on.If you want a different set of comparison rules, you need to choose a different collation. You can see the available collations for the
utf8
character set by issuingSHOW COLLATION LIKE 'utf8%'
. There are a bunch of collations intended for text that is mostly in a specific language; there is also theutf8_bin
collation which compares all strings as binary strings (i.e. compares them as sequences of 0s and 1s).