将MySQL中的Latin1数据转换为UTF8

Question

我使用LOAD DATA INFILE将一些数据导入到MySQL数据库中。表本身和列使用UTF8字符集，但数据库的默认字符集是Latin1。因为数据库的默认字符类型是Latin1，并且我在没有指定字符集的情况下使用了LOAD DATA INFILE，它将文件解释为Latin1，即使文件中的数据是UTF8。现在我的UTF8列中有很多编码错误的数据。我找到了this article，它似乎解决了类似的问题，即“UTF8插入cp1251”，但我的问题是“Latin1插入UTF8”。我尝试编辑那里的查询以将Latin1数据转换为UTF8，但无法使其工作。要么数据保持不变，要么比以前更加混乱。例如，单词Québec显示为Québec。

[附加信息]

当选择HEX()包装的数据时，Québec的值为5175C383C2A9626563。

这个表的Create Table（缩短）如下：

CREATE TABLE MyDBName.`MyTableName`
(
`ID` INT NOT NULL AUTO_INCREMENT, 
.......
`City` CHAR(32) NULL, 
.......
`)) ENGINE InnoDB CHARACTER SET utf8;

- Kibbee

---

请提供问题表的CREATE TABLE语句以及一些错误行，但是将错误列用hex()包装起来，就像这样：SELECT HEX(name) FROM cities LIMIT 5。有了这些信息，我可以帮助您根据那篇文章找出正确的修复方法。（顺便说一下：我喜欢那篇文章！它已经救过我好几次了。） - longneck

Answer 1

在旧的 WordPress 安装中，我遇到过这样的情况：数据本身已经是 UTF-8 格式，但由于 WP 的默认字符集，它们被存储在 Latin1 数据库中。这意味着实际上不需要对数据进行转换，只需要对数据库和表格进行格式转换即可。

据我所知，在导出数据时会出现混乱，因为 MySQL 会使用客户端的默认字符集，而现在许多情况下默认字符集都是 UTF-8。

因此，确保以相同编码方式导出数据非常重要。如果是 Latin1 数据库，并且使用 UTF-8 编码，则需要特别注意：

$ mysqldump –default-character-set=latin1 –databases wordpress > m.sql

然后，在将导出的转储文件重新导入到新的UTF-8数据库之前，先替换其中的Latin1引用。类似于：

$ replace "CHARSET=latin1" "CHARSET=utf8" \
    "SET NAMES latin1" "SET NAMES utf8" < m.sql > m2.sql

在我的情况下，这个链接对我非常有帮助。 在这里用西班牙语发表评论。

- luison

---

我曾经看到过一些MySQL转储文件，其中这个替换命令不够充分，因为某些列被明确设置为latin1。我使用了 replace "latin1" "utf8mb4" <dump.latin1.sql >dump.utf8.sql 命令，使该表中的所有内容都使用UTF-8编码。但请注意，该转储文件中没有其他地方（字段内容）出现“latin1”，为了确保安全，我在导入之前进行了比较。 - basic6

Answer 2

虽然对于提问者来说已经不再适用，但我恰好在MySQL文档中找到了ALTER TABLE的解决方案。我在这里发布它只是为了以后参考：

警告

CONVERT TO操作在字符集之间转换列值。如果您有一个使用一个字符集（如latin1）的列，但存储的值实际上使用了其他不兼容的字符集（如utf8），那么这不是您想要的。在这种情况下，您必须针对每个这样的列执行以下操作：

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

这个方法有效的原因是在转换到或从BLOB列时没有进行任何转换。

- newtover

---

有混合内容时，这个不起作用：代码：1366 SQL状态：HY000 --- 列“kommentar”第1行的值“\xE4chste...”不正确。 - Wolfgang Fahl

Answer 3

我曾为拉丁数据库编写了一个转换工具，用于将数据库从拉丁字符集转换为UTF-8字符集。该工具可以更改所有表格和字段的字符集为UTF-8。你可以在这个链接中查看：http://code.google.com/p/mysqlutf8convertor/。

- saturngod

---

你可能想要导入到Github。Google Code现在是只读的。 - Otheus

Answer 4

LOAD DATA INFILE 允许您设置文件所使用的编码：

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

- Vladislav Rastrusny

---

是的，我希望我之前能意识到这个问题，但现在数据已经混乱了。我想知道是否可以在不重新导入数据的情况下修复它。 - Kibbee

是的！这就是选项：[字符集字符集名称] - Fernando Fabreti

Answer 5

我最近完成了一个自动化转换过程的Shell脚本。它还可以配置为编写自定义过滤器以替换或删除任何文本。例如：去除HTML字符等。表格白名单和黑名单也是可能的。您可以在sourceforge上下载它：https://sourceforge.net/projects/mysqltr/

- user2192857

Answer 6

将latin1转换为UTF8不是您想要做的事情，您需要相反的操作。

如果真正发生的是这样的：

1. UTF-8字符串被解释为Latin-1并转码为UTF-8，从而破坏了它们。
2. 现在，您正在读取UTF-8字符串而没有进一步的解释。

现在您必须做的是：

1. 使用无需转码的“UTF-8”进行阅读。
2. 将其转换为Latin-1。现在您应该实际上拥有原始的UTF-8。
3. 现在将其放入您的“UTF-8”列中，无需进一步转换。

- DigitalRoss

Answer 7

尝试这个：

1）转储您的数据库

mysqldump --default-character-set=latin1 -u username -p databasename < dump.sql

2) 在文本编辑器中打开dump.sql文件，并将所有出现的"SET NAMES latin1"替换为"SET NAMES utf8"

3) 创建一个新的数据库并恢复您的dump文件

cat dump.sql | mysql -u root -p newdbname

- François