Illegal mix of collations

Published by

Posted on October 07, 2019

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

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";

Categories: MySQL