将 mysql 表从 latin1 转换为 utf8

发布于 09-30 07:15 字数 719 浏览 7 评论 0原文

我正在尝试将一些 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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

月朦胧2024-10-07 07:15:13

字符串 '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 the utf8_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 issuing SHOW COLLATION LIKE 'utf8%'. There are a bunch of collations intended for text that is mostly in a specific language; there is also the utf8_bin collation which compares all strings as binary strings (i.e. compares them as sequences of 0s and 1s).

献世佛2024-10-07 07:15:13

UTF8_GENERAL_CI 不区分重音。

使用 UTF8_BIN 或特定于语言的排序规则。

UTF8_GENERAL_CI is accent insensitive.

Use UTF8_BIN or a language-specific collation.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文