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

Import multiple db files from folder into mysql db

Hello friends, Welcome back In the previous article, we've seen how to export database by table files, not the entire db file. Now in this article, here we're going to see the sample code for importing multiple sql files into same db. This also has been achieved by using a simple shell script. In this script, applied a for loop on list of files in a directory and by using that file name, mysql's import command is executed. The code is simple, you'll learn in just few minutes. Linux shell script: Execute above code in linux terminal to import all files into the given db. Hope this would help you. Thanks.
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

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