How to fix mysql data encoding

Question

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

Answer 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: باسلام --> باسلام)