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:
|
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
|
1 2 |
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'; |
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:
|
1 2 |
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.
Thank you, this worked very well for my case.
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.