MySQL views comprising tables with different(?) character encodings

I'm building a cross-database view that consolidates data from two different subsystems for reporting.

Both tables are using UTF8 encoding with utf8_general_cl collation.

The problem is that one database is utf8, whereas the other is latin1 with latin1_swedish_cl set.

The result is that accented characters and the like are coming through corrupted from the table in the latin1 databa开发者_高级运维se, in spite of the fact that both tables are utf8.

I don't think changing character sets for the database as a whole is an option.

Can I convert the strings on the fly? I tried convert() but it didn't seem to have any effect.

Try this

convert(convert(convert(COL using latin1) using binary) using utf8)