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

Question

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?

Answer 1

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, ...

Answer 2

I had a similar problem. What I did was to isolate the comparison in the where clause that's causing the error. I ran the CONVERT function on the value I to compare with into the collation of the table.

`field` = CONVERT(value USING charset_of_table)

See this post for more details and examples on using CONVERT