Convert output of MySQL query to utf8

Question

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?

Try `SELECT column1, CONVERT(column2 USING utf8)...` – Explosion Pills Apr 17 '13 at 04:18 — Explosion Pills, Apr 17 '13 at 04:18

score 81 · Accepted Answer · answered Apr 17 '13 at 04:21

81

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;

answered Apr 17 '13 at 04:21

josh-fuggle

3,097
2
26
27

2

how to do this for `select *` as this is an syntax error `select convert(* using utf8)` – Gaurav Aggarwal Oct 22 '18 at 12:51
2

Hey mate, I believe you can't use the wildcard character and have to convert columns individually: `SELECT CONVERT(mycol1 USING utf8), CONVERT(mycol2 USING utf8)` – josh-fuggle Oct 22 '18 at 22:31

score 48 · Answer 2 · edited Dec 01 '16 at 14:01

48

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

edited Dec 01 '16 at 14:01

Petter Friberg

21,252
9
60
109

answered Dec 01 '16 at 14:01

Waldeyr Mendes da Silva

1,027
9
6

1

Thanks you saved my day :) – Mohammed Tawfik Jan 05 '17 at 21:21
Thanks ! Save my day to ! – Portekoi Nov 02 '17 at 12:14
CAST to BINARY saved me! You rock! – amitdar Jan 07 '19 at 15:24
Casting the column to binary before converting to UTF-8 solved it for me too. – Diego Victor de Jesus Apr 28 '19 at 03:35
1

this did not work for me. I had to do it the other way, CAST on the outside – agent provocateur Sep 30 '19 at 23:43

score 28 · Answer 3 · answered Jan 22 '18 at 07:14

28

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.

answered Jan 22 '18 at 07:14

ManuelAtWork

2,198
29
34

3

This worked for me. Thank you. CONVERT(CAST( did not work for me. – agent provocateur Sep 30 '19 at 23:43
Same there, the accepted answer with convert only didn't work for me, this one did, thanks. – Alex May 30 '20 at 21:58

score 13 · Answer 4 · answered Jul 15 '21 at 19:41

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.

Convert output of MySQL query to utf8

4 Answers4

Linked