Mariadb – Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_turkish_ci,IMPLICIT) for operation ‘=’ and why

collationmariadb

Hello I got error in mariadb. In my command two columns have different collate. one is utf8_unicode_ci (a) other is utf8_turkish_ci (b). Why I got error when I compare these columns ( where a = b ) both of them utf8.
Error:
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_turkish_ci,IMPLICIT) for operation '='

Best Answer

It would be useful if you can provide your tables definition. However, your error suggests that your are trying to check equality between two columns having different collation.

One solution is to change collation of one of the columns to match the other collation. Check here - Column Level.

Other solution is to use COLLATE clause (I do not know if this works in mariadb, but it should be there).

Personally, I prefer to avoid COLLATE and harmonize string columns as collation mismatch tend to force lots of COLLATEs (maintenance problems).

Related Solutions

MySQL: Illegal mix of collations

After some trial and error, I've learned how and where to apply COLLATE:

Converted lines like:

SELECT SOMETHING
FROM SOMEWHERE
WHERE table_schema = given_database
AND   table_name   = given_table
AND   index_name   = given_index;

To:

SELECT SOMETHING
FROM SOMEWHERE
WHERE table_schema COLLATE utf8_unicode_ci = given_database
AND   table_name   COLLATE utf8_unicode_ci = given_table
AND   index_name   COLLATE utf8_unicode_ci = given_index;

SQL Server Collation – Using DB Collation for Comparison

would this script use a temporary table? Then the table will have the server default collation.

If it just a quick one-time solution, then I would modify the source query to provide the data in the correct collation, here Hebrew_CI_AS

Best Answer

Related Solutions

MySQL: Illegal mix of collations

SQL Server Collation – Using DB Collation for Comparison

Related Question