将MySQL表从latin1转换为utf8 [英] Converting mysql tables from latin1 to utf8

问题描述

我正在尝试将一些mysql表从latin1转换为utf8.我正在使用以下命令，该命令似乎可以正常工作.

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;

但是，在一张桌子上，我收到有关重复键输入的错误.这是由名称"字段上的唯一索引引起的.似乎在转换为utf8时，会将任何特殊"字符都索引为它们的等效英文正本.例如，已经有一个名称字段值为"Dru"的记录.转换为utf8时，带有Drü"的记录被视为重复记录. "Patrick"和Påtrìçk"也是如此.

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归类中，它们被视为相同". 排序规则用于字符集的目的是提供一组关于何时字符串相同，何时在另一字符串之前排序等等的规则.

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.

如果您需要不同的比较规则集，则需要选择其他排序规则.您可以通过发出SHOW COLLATION LIKE 'utf8%'来查看utf8字符集的可用归类.有大量的归类用于文本，主要是使用特定的语言. utf8_bin归类将所有字符串都比较为二进制字符串(即，将它们比较为0和1的序列).

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).

这篇关于将MySQL表从latin1转换为utf8的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！