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

XML tools on linux

Hello friends, Welcome back 🙂 In this article, we're going through XML tools which are useful for manipulation of XML. Recently I've worked on 3 tools so for now, adding those 3 tool's information, but as I get more, I'll update the article. These tools useful for validation and split/merge the XML. These tools available on both RHEL family and Debian family  linux which are same in use for both debian and RHEL. The tools are: 1. xmllint: This tool is used for xml validation. For some reason, we need to validate XML tags with its proper openings and closing i.e. syntactic validation, also we need to validate the data in the XML. The data can be different by its type which is defined in the XML's DTD, to detect this also, xmllint is useful. With this tool, there are multiple options we can use i.e. only syntactic validation, schema validation, DTD validation etc. When I was facing issue for validating the XML which having schema, I found this tool. H...
Post a Comment
Read more

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

Static Keyword in Java

Hello friends, Welcome back 🙂 In this blog, I'm going to cover shortly about static keyword in Java. I'm assuming that you're familiar with the static keyword in java. Because I've written this blog in such a way that here only revision of the use of static keyword and the important points about static keyword which are rarely disclosed. So the first part will just be short description of static keyword, and second part will be the points about which, most of the new-bee or intermediate developers not familiar with. Please comment if anything is missing or not covered in this blog. 1. static member :     All the non-static variables/member objects of a class are scoped at object level i.e. we can access a class's variable by using instance of that class. But in Java, we can create a class level scoped variable by using static keyword. The static variables can be accessed by using class name i.e. ClassName.staticVar . To declare variable as static, we ju...
Post a Comment
Read more