Skip to main content

Recover messed up/lost UTF-8 characters in MySQL db

Hi friends,

This was my first story on medium, now copying as it is on blogger.

In this note, I’m going to explain you what are the messed up/lost/bad utf-8 characters and how to recover them to original in MySQL db.

What is messed up/lost/bad utf-8 characters?

The messed up or lost or bad characters are those characters which are normal if we convert it to UTF-8, but due to mistakes while import/export, that characters not display properly. Consider following example:

很好的产品。就是没货了

The above sentence is actually a Chinese sentence, but while import/export from db, it’s get messed up.

How to recover them?

To recover the messed up characters, we should convert them first into binary form, then convert that binary string to UTF-8 string, that’s it!

To do this, use following SQL:



In above SQL, we firstly converted data of varcharColto binary form using MySQL’s CAST function. Then converted that binary string to UTF-8 using CONVERT function.

After executing above SQL, we get following normal string:

很好的产品。就是没货了

Caution

Before doing this, take Backup of your db, because while doing this, normal UTF-8 characters may get messed up 😈

Note

I’ve tested it on table having latin_swedish collation.
Labels:

Comments

Post a Comment

Want to share something? Add here...

Popular posts from this blog

Initialization blocks in Java

Hello friends, Welcome back. Today in this article, I'm going to explain you about the static block and initializer block. As the Java language is pure OOP language, whatever the code we write is only in classes. So whenever we use class to represent a real life entity, we use members in the class as the attributes or properties of that entity. To initialize those properties or members, we can use one of the way i.e. Constructor Setter method Inlinie Blocks I'm assuming that you are familiar with all methods. Here about first three methods, I'm just giving a short description. The first three methods are : we initialize member in constructor by using a parameterized constructor. Setter methods is a java standard which denotes that class's member should be private and it should have setter method so that the member can be initialized. Third method is inline i.e. when we declare a member or variable, at the same time, we set its value. In this article, we...
Post a Comment
Read more

The final keyword in java

Hello friends, Welcome back 🙂. In this article, I'm going to give a short introduction to final keyword which would be helpful to know more about it. The final keyword in java has multiple uses. The main purpose of the final keyword is to add some restrictions on java elements (class, member and method). The restrictions have been described as below. Use of final keyword: 1. The final class : We can say the class is final when it declared as final. The restriction on final class is that the final class cannot be extended by any class. Rest of the behavior of the class remains same as any normal class. 2. The final method : The method declared using final keyword is to be said as final method. The restriction is that the final method cannot be overridden by derived class. This restriction applies to static method also. 3. The final member : The final member is the java class's member which is declared using final keyword. The restriction is that it can be initial...
Post a Comment
Read more