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

casting, mysql

Solution

The error message is rather confusing as it specifies `operation =` -- which is not obvious from the query you posted but is caused by the `UNION` query which select only distinct values. So using implicitly an equality comparaison.

Anyway, you can always force the collation of a column using the `COLLATE` clause. Here is a an example, assuming you want to change the collation of the column `tipo`:

SELECT id, titulo, 'foto' COLLATE utf8_general_ci AS tipo

... 
UNION SELECT id, titulo, 'video' COLLATE utf8_general_ci AS tipo, ...

Problem

When I run the following query: ``` CREATE ALGORITHM = UNDEFINED VIEW d_view_galerias AS ( SELECT id, titulo, 'foto' AS tipo, '' AS embed FROM d_galeria_fotos ) UNION ( SELECT id, titulo, 'video' AS tipo, embed FROM d_galeria_videos ) ``` I get the error: Illegal mix of collations (utf8_unicode_ci,COERCIBLE) and (utf8_general_ci,COERCIBLE) for operation '=' "tipo" is getting as utf8_unicode, but the other fields are as utf8_general ... how to make a cast, convert?

Original source