Convert output of MySQL query to utf8

I have a table in my database and I want run a query like

SELECT column1, column2 FROM my_table WHERE my_condition;

but I want the mysql to return the column2 in utf8 encoding. Is it any function in mysql to do such task? What is that?

Solution 1:

You can use CAST and CONVERT to switch between different types of encodings. See: http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

SELECT column1, CONVERT(column2 USING utf8)
FROM my_table 
WHERE my_condition;

Solution 2:

SELECT CONVERT(CAST(column as BINARY) USING utf8) as column FROM table 

Solution 3:

Addition:

When using the MySQL client library, then you should prevent a conversion back to your connection's default charset. (see mysql_set_character_set()[1])

In this case, use an additional cast to binary:

SELECT column1, CAST(CONVERT(column2 USING utf8) AS binary)
FROM my_table
WHERE my_condition;

Otherwise, the SELECT statement converts to utf-8, but your client library converts it back to a (potentially different) default connection charset.

Solution 4:

I have used next code for LATIN1 (or your db's charset) to UTF8 problems :

SELECT column1,
       CONVERT(CAST(CONVERT(column2 USING LATIN1) AS BINARY) USING UTF8) AS column2
  FROM my_table
 WHERE my_condition

It converts from LATIN1 to BINARY, then to UTF8, because straight from LATIN1 to UTF8 might not work.