如何修复mysql数据编码(How to fix mysql data encoding)

我正在使用latin1字符集编码（ latini_swedish_ci整理）保存名称的数据库，但名称是波斯语。 看起来有些机构将表格归类改为utf8 （ utf8_bin ），但数据仍然是这样的：

我想知道如何解决这个问题。 我改变了表格整理和DB整理，但仍然有这种值。 任何帮助将不胜感激。 提前致谢

I'm working on a database that the names was saved in using latin1 character set encoding (latini_swedish_ci collation) but the names were in Persian. It seems some body changed the table collation to utf8 (utf8_bin) but still the data is like this :

I'm wondering how can I fix this values. I changed table collation and DB collation but still I have this kind of values. Any help will be appreciate. Thank's in advance

最满意答案

它看起来像你

客户端中的utf8编码字节，以及 SET NAMES latin1 （或等效物），和 目标列上的CHARACTER SET latin1 。

清理表的“修复”是执行这里描述的两步ALTER，这涉及到

ALTER TABLE Tbl MODIFY COLUMN col VARBINARY(...) ...; ALTER TABLE Tbl MODIFY COLUMN col VARCHAR(...) ... CHARACTER SET utf8 ...;

长度足够大而另一个“...”具有其他任何内容（ NOT NULL等）已经在列上。

对不起，但修复1500000行需要很长时间。

我很确定这不会起作用：

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8; -- no

只有当表包含utf8字符的latin1（等）时，它才能工作。 对于阿拉伯字符，没有相当于拉丁文的1。

（我把它看成阿拉伯语：باسٓاÙ... - >باسلام）

It looks like you had

utf8-encoded bytes in the client, and SET NAMES latin1 (or equivalent), and CHARACTER SET latin1 on the target column.

The "fix" to clean up the table is to do the 2-step ALTER described here, which involves

ALTER TABLE Tbl MODIFY COLUMN col VARBINARY(...) ...; ALTER TABLE Tbl MODIFY COLUMN col VARCHAR(...) ... CHARACTER SET utf8 ...;

where the lengths are big enough and the other "..." have whatever else (NOT NULL, etc) was already on the column.

Sorry, but it will take a long time do fix 1500000 rows.

I am pretty sure this will not work:

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8; -- no

It would work only if the table currently contains the latin1 (etc) equivalent of the utf8 characters. There is no latin1 equivalent for Arabic characters.

(I see it as Arabic: باسلام --> باسلام)

如何修复mysql数据编码(How to fix mysql data encoding)

我正在使用latin1字符集编码（ latini_swedish_ci整理）保存名称的数据库，但名称是波斯语。 看起来有些机构将表格归类改为utf8 （ utf8_bin ），但数据仍然是这样的：

我想知道如何解决这个问题。 我改变了表格整理和DB整理，但仍然有这种值。 任何帮助将不胜感激。 提前致谢

I'm working on a database that the names was saved in using latin1 character set encoding (latini_swedish_ci collation) but the names were in Persian. It seems some body changed the table collation to utf8 (utf8_bin) but still the data is like this :

I'm wondering how can I fix this values. I changed table collation and DB collation but still I have this kind of values. Any help will be appreciate. Thank's in advance

最满意答案

它看起来像你

客户端中的utf8编码字节，以及 SET NAMES latin1 （或等效物），和 目标列上的CHARACTER SET latin1 。

清理表的“修复”是执行这里描述的两步ALTER，这涉及到

ALTER TABLE Tbl MODIFY COLUMN col VARBINARY(...) ...; ALTER TABLE Tbl MODIFY COLUMN col VARCHAR(...) ... CHARACTER SET utf8 ...;

长度足够大而另一个“...”具有其他任何内容（ NOT NULL等）已经在列上。

对不起，但修复1500000行需要很长时间。

我很确定这不会起作用：

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8; -- no

只有当表包含utf8字符的latin1（等）时，它才能工作。 对于阿拉伯字符，没有相当于拉丁文的1。

（我把它看成阿拉伯语：باسٓاÙ... - >باسلام）

It looks like you had

utf8-encoded bytes in the client, and SET NAMES latin1 (or equivalent), and CHARACTER SET latin1 on the target column.

The "fix" to clean up the table is to do the 2-step ALTER described here, which involves

ALTER TABLE Tbl MODIFY COLUMN col VARBINARY(...) ...; ALTER TABLE Tbl MODIFY COLUMN col VARCHAR(...) ... CHARACTER SET utf8 ...;

where the lengths are big enough and the other "..." have whatever else (NOT NULL, etc) was already on the column.

Sorry, but it will take a long time do fix 1500000 rows.

I am pretty sure this will not work:

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8; -- no

It would work only if the table currently contains the latin1 (etc) equivalent of the utf8 characters. There is no latin1 equivalent for Arabic characters.

(I see it as Arabic: باسلام --> باسلام)