MySQL – How to convert all Database Tables and Columns 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:

1	ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;

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

Convert the Collation of all Tables

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME,' COLLATE utf8_general_ci;')  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='DBNAME' AND TABLE_TYPE = 'BASE TABLE';

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:

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') AS    mySQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= "DBNAME" AND TABLE_TYPE="BASE TABLE"

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:

1	SET foreign_key_checks = 0;

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.