CODEWITHSUNDEEP
phpmysqldatabaseutf-8
Ahmed Muhammad
Ahmed Muhammad

Reputation: 118

Updating data in MySQL database after inserting in the wrong encoding

I am using PHP and MySQL for saving Arabic data.

My database collation is : utf8_general_ci

My database Character set is : utf8

At first I did not use SET NAMES utf8 before insertion so the data was inserted in strange characters in the database but was displayed properly in my application. After using SET NAMES utf8, data is inserted properly but the old data is not displayed in Arabic characters even in my application (The new data is ok) .

How can I update all the data to be displayed in Arabic letters in both my application and MySQL database?

Update

When I check the encoding of the both of strings ( the one that is inserted and the one that I want to convert to ) using mb_detect_encoding function I get that both of the strings are UTF-8 .

Here is an example of the two strings :

the text inserted : الإسم بالعربية the text I need to convert to : الإسم بالعربية

Upvotes: 2

Views: 2575

Answers (2)

Rick James
Rick James

Reputation: 142278

You suffer from "double encoding".

Here's what happened.

  • The client had characters encoded as utf8; and
  • SET NAMES latin1 lied by claiming that the client had latin1 encoding; and
  • The column in the table declared CHARACTER SET utf8.

Let's walk through what happens to e-acute: é.

  1. The hex for that, in utf8 is 2 bytes: C3A9.
  2. SET NAMES latin1 saw it as 2 latin1-encoded characters à and © (hex: C3 and A9)
  3. Since the target was CHARACTER SET utf8, those 2 characters needed to be converted. Ã was converted to utf8 (hex C383) and © (hex C2A9)
  4. So, 4 bytes were stored (hex C383C2A9)

When reading it back out, the reverse steps were performed, and the end user possibly noticed nothing wrong. What is wrong:

  • The data stored is 2 times as big as it should be (3x for Asian languages).
  • Comparisions for equal, greater than, etc may not work as expected.
  • ORDER BY may not work as expected.

Something like this will repair your data:

UPDATE ... SET col = CONVERT(BINARY(CONVERT(
                         CONVERT(UNHEX(col) USING utf8)
                         USING latin1)) USING utf8);

More discussion and More examples of fixing it

Upvotes: 3

user2472312
user2472312

Reputation:

Maybe this is helpful [http://forums.mysql.com/read.php?103,209072,209072

*Same error and solution on MySQL forum. (since 2008)

Upvotes: 0

Related Questions