Search code examples
mysqlencodingutf-8latin1

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

Solution

  • Try this

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