MySQL将latin1数据转换为UTF8 [英] MySQL Convert latin1 data to UTF8

问题描述

我使用LOAD DATA INFILE将一些数据导入到MySQL数据库中.表本身和列使用的是UTF8字符集，但是数据库的默认字符集是latin1.由于数据库的默认字符集是latin1，并且我使用LOAD DATA INFILE时未指定字符集，因此它解释为即使文件中的数据为UTF8，文件也仍为latin1.现在，我的UTF8列中有一堆编码错误的数据.我发现了本文解决类似的问题，即在cp1251中插入了UTF8"，但是我的问题是在UTF8中插入了Latin1".我尝试在其中编辑查询以将latin1数据转换为UTF8，但无法使其正常工作.数据要么输出得一样，要么比以前更加混乱.举一个例子，魁北克(Québec)一词显示为魁北克(Québec).

I imported some data using LOAD DATA INFILE into a MySQL Database. The table itself and the columns are using the UTF8 character set, but the default character set of the database is latin 1. Because the default character type of the database is latin1, and I used LOAD DATA INFILE without specifying a character set, it interpreted the file as latin1, even though the data in the file was UTF8. Now I have a bunch of badly encoded data in my UTF8 colum. I found this article which seems to address a similar problem, which is "UTF8 inserted in cp1251", but my problem is "Latin1 inserted in UTF8". I've tried editing the queries there to convert the latin1 data to UTF8, but can't get it to work. Either the data comes out the same, or even more mangled than before. Just as an example, the word Québec is showing as Québec.

[附加信息]

选择包装在HEX()中的数据时，魁北克的值为5175C383C2A9626563.

When Selecting the data wrapped in HEX(), Québec has the value 5175C383C2A9626563.

此表的创建表(简称)是.

The Create Table (shortened) of this table is.

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

推荐答案

在旧的wordpress安装中，我遇到过类似情况，问题是数据本身已经在Latin1数据库中的UTF-8中(由于WP)默认字符集).这意味着除了ddbb和表格式外，没有真正的数据转换需求. 根据我的经验，转储时会搞砸，因为我了解MySQL将使用客户端的默认字符集，在许多情况下，该字符集现在为UTF-8. 因此，确保以相同的数据编码进行导出非常重要.如果采用UTF-8编码的Latin1 DDBB:

I've had cases like this in old wordpress installations with the problem being that the data itself was already in UTF-8 within a Latin1 database (due to WP default charset). This means there was no real need for conversion of the data but the ddbb and table formats. In my experience things get messed up when doing the dump as I understand MySQL will use the client's default character set which in many cases is now UTF-8. Therefore making sure that exporting with the same coding of the data is very important. In case of Latin1 DDBB with UTF-8 coding:

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

然后在重新导入到UTF-8中的新数据库之前，在导出的转储中替换Latin1引用.种类:

Then replace the Latin1 references within the exported dump before reimporting to a new database in UTF-8. Sort of:

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

对于我来说此链接很有帮助. 此处为西班牙语.

In my case this link was of great help. Commented here in spanish.

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