How to change Database Collation in MySQL Manually

• November 2, 2015
• Leave a comment

Sometimes, your Server doesn’t support latest Database Collation, for example; unicode collation. Let’s suppose that you want to develop a website and its database is based on unicode collation, then where you are going to deploy it as it has no support for unicode collation? In this case, you can not even transfer/migrate your website.

By using the following tricks, you can solve this problem in few seconds:

At first, you need to check your database collation from phpmyadmin. Here, we are taking an example of WordPress Database. In the screenshot below, you can see that the collation of database is utf8mb4_unicode_ci:

Now, export your Database file in .sql format from phpmyadmin:

As you have exported your database file and we are supposing that your database collation was utf8mb4_unicode_ci. Now, you have to covert your database into utf8_unicode_ci or utf8_general_ci. For this, you need to understand where database like MySQL stores the collation information from this link. Once you are done with it, then you will be able to change the collation of database.

Open your .sql file in any editor (notepad++ or any other). Search and replace the following instructions from this file:

Search: “utf8mb4” and replace it with “utf8”

You have to replace all strings in the file and click on ‘Save‘. By doing so, you have converted your database collation from utf8mb4_unicode_ci to utf8_unicode_ci.

Now search and replace again with the following instructions and click on ‘Save‘:

Search: “unicode_ci” and replace it with “general_ci”

You have finally converted your database collation from utf8mb4_unicode_ci to utf8_general_ci. Now you have to create a new database in phpmyadmin with collation utf8_general_ci and also import your database .sql file that you have converted. You will see that the collation of the database has been changed. See the screenshot below for reference:

By using the above mentioned simple tricks, you will be able to change the collation of database successfully.