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 :

enter image description here

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

You will need utf8_general_ci. It is like Arabic. and make sure that the database, the table and the field have the same collation. — SaidbakR
– SaidbakR, Commented Apr 17, 2015 at 22:46
What is the scripting language that you use in your application? — SaidbakR
– SaidbakR, Commented Apr 17, 2015 at 22:47
All the types have the same collation. I think this data was maded using phpNuke... I'm trying to write a search engine that will be work independently... — mrm
– mrm, Commented Apr 17, 2015 at 22:55
Could you maybe add a name for us with the characters - it's really hard to try different things with the names only in the image. — DominikAngerer
– DominikAngerer, Commented Apr 17, 2015 at 22:58

Rick James · Accepted Answer · 2015-04-17 23:25:09Z

5

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: Ø¨Ø§Ø³Ù„Ø§Ù… --> باسلام)

answered Apr 17, 2015 at 23:25

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DominikAngerer Over a year ago

+1 was looking for a good description + example which should do the trick - another +1 for the hit that this will working for all character with latin1 equivalent.

mrm Over a year ago

@Rick James Thanks for your time. I followed your steps but still the data is like before. Am I doing something wrong?

Rick James Over a year ago

Let's verify something else. Please provide SELECT col, HEX(col) ... for some small amount of text in the table. And, if possible, tell me what it should look like. (If you have "double encoding", the cure is even worse.)

DominikAngerer · Accepted Answer · 2015-04-17 22:58:03Z

1

Change to utf8_unicode_ci like (To change the default character set and collation of a table including those of existing columns - convert to - is here the key part)

alter table <some_table> convert to character set utf8 collate utf8_unicode_ci;

The _ci suffix means sorting and comparison happens case insensitive. so that shouldn't be a problem.

UTF-8 is an encoding for the Unicode character set, which should support pretty much every language in the world.

The only difference comes with sorting your results, different letters might come in a different order in other languages (accents, umlauts, etc.). For example comparing a to ä might behave differently in another collation.

Could you maybe add a name for us with the characters - it's really hard to try different things with the names only in the image.

answered Apr 17, 2015 at 22:58

DominikAngerer

6,5415 gold badges35 silver badges60 bronze badges

15 Comments

mrm Over a year ago

Thanks for your answer. I used your query but the problem still remains...!!! Both table and columns have the same encoding---> utf8_unicode_ci

DominikAngerer Over a year ago

Could you dump us the table with some rows? - would like to try it directly so i can provide you a real solution.

mrm Over a year ago

let me try to... it's a huge database with more than 1500000 record.... I'll try to upload dump file some where..

DominikAngerer Over a year ago

simply copy & paste the table create statement & 10 - 20 insert statements - should be enough. Maybe the problem is not coming from the mysql part - could you also show us how you add these data?

Rick James Over a year ago

I do understand that my layout is lame. If the content is mangled, please provide some details.

|

Collectives™ on Stack Overflow

How to fix mysql data encoding

2 Answers 2

3 Comments

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related