How to convert all tables in database to one collation?

Question

I'm getting error:

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

I tried changing both tables manually to utf8_general_ci,IMPLICIT but I'm still getting the error.

Is there a way to convert all tables to utf8_general_ci,IMPLICIT and be done with it?

Answer 1

You need to execute a alter table statement for each table. The statement would follow this form:

ALTER TABLE tbl_name [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name] 

Now to get all the tables in the database you would need to execute the following query:

SELECT *  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="YourDataBaseName" AND TABLE_TYPE="BASE TABLE"; 

So now let MySQL write the code for you:

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE your_collation_name_here;") AS    ExecuteTheString FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="YourDatabaseName" AND TABLE_TYPE="BASE TABLE"; 

You can copy the results and execute them. I have not tested the syntax but you should be able to figure out the rest. Think of it as a little exercise.

Hope That Helps!

Answer 2

Better option to change also collation of varchar columns inside table also

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

Additionnaly if you have data with forein key on non utf8 column before launch the bunch script use

SET foreign_key_checks = 0; 

It means global SQL will be for mySQL :

SET foreign_key_checks = 0; ALTER TABLE `table1` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `table2` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE `tableXXX` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; SET foreign_key_checks = 1; 

But take care if according mysql documentation http://dev.mysql.com/doc/refman/5.1/en/charset-column.html,

If you use ALTER TABLE to convert a column from one character set to another, MySQL attempts to map the data values, but if the character sets are incompatible, there may be data loss. "

EDIT: Specially with column type enum, it just crash completly enums set (even if there is no special caracters) https://bugs.mysql.com/bug.php?id=26731