MySQL - How to convert all Database Tables and Columns to a specific Collation

MySQL - How to convert all Database Tables and Columns to a specific Collation

A few months ago I wrote an article explaining how to convert all MySQL tables belonging to one or more Database(s) from MyISAM to InnoDB and vice-versa with a simple, yet effective CONCAT-based query. That same approach can be used if we need to convert all the tables to a specific Collation.

In case you don't know what the term Collation actually stands for, you can get the proper definition from the official MySQL docs:

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.

When working with MySQL database it's a good practice to use the same Collation on tables which we need to JOIN or UNION during FULL-TEXT and/or LIKE based searches, to avoid errors such as the following:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation

However, there are some scenarios - for example when switching from MyISAM default to InnoDB default, or when moving a DB between different servers with different settings - where you need to normalize the Collation among multiple tables belonging to one (or more) Database(s). When this happens, we can convert one or more Databases and/or all their Tables and/or all their table Columns into a specific Collation using the following sets of commands.

Convert the Collation of a Database

Let's start with the easy task:

Needless to say, replace DBNAME with your actual Database name and utf8_general_ci  with the desired Collation.

Convert the Collation of all Tables

If you need to perform this task on a single Database, replace DBNAME with the name of your Database; if you need to do that on multiple Databases, add one or more additional OR conditions to the WHERE and let MySQL do the rest.

Please notice that this command will build a list of queries you will have to execute to actually perform the conversion, meaning that you'll have to manually execute them afterwards.

Convert the Collation of Table Columns

The above command will change the Collation of all the tables, yet it won't change the character set used to store the value of any text-value column (varchar, text & the likes) within the tables. If you also want to do that, you need to use the following command instead:

Again, this command will build a list of queries you will have to execute to actually perform the conversion.

Also notice that you might need to prepend the following to the resulting list of queries:

If you do that, remember to re-enable it when you're done with the Collation change.

Before using these commands it's wise to execute a full backup of the Database. Whenever you use ALTER TABLE to convert a column from one character set to another, MySQL attempts to map the data values: if the character sets are incompatible, there might be data loss.

For further info about that, read carefully the following advice coming from the official MySQL docs.

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

2 Comments on “MySQL - How to convert all Database Tables and Columns to a specific Collation”

  1. Thank you a lot!

    That saved my life migrating from OJS 2.4.2 to OJS 3.1.2-1.
    The old database/table/columns collation was latin1_general_ci and needed to be utf8_general_ci.

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.