How export csv from mysql utf8

Question

I want export csv directly from mysql with command

SELECT .... 
FROM ...
INTO OUTFILE '/tmp/export.csv' 
FIELDS TERMINATED BY ',' 
ESCAPED BY '\\'
LINES TERMINATED BY '\n' ;

This work perfectly, but encode not is utf8.How make the content exported utf8 encoding?

Please take a look at this u will get some idea. stackoverflow.com/questions/17108249/… — piyush pankaj
– piyush pankaj, Commented Sep 6, 2013 at 10:56

eggyal · Accepted Answer · 2013-09-06 11:56:47Z

23

As documented under SELECT ... INTO Syntax:

SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE. Column values are written converted to the character set specified in the CHARACTER SET clause. If no such clause is present, values are dumped using the binary character set. In effect, there is no character set conversion. If a result set contains columns in several character sets, the output data file will as well and you may not be able to reload the file correctly.

The grammar is documented under SELECT Syntax:

    [INTO OUTFILE 'file_name'
      [CHARACTER SET charset_name]

Therefore:

SELECT .... 
FROM ...
INTO OUTFILE '/tmp/export.csv'
CHARACTER SET utf8 
FIELDS TERMINATED BY ',' 
ESCAPED BY '\\'
LINES TERMINATED BY '\n' ;

answered Sep 6, 2013 at 11:56

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

cawecoy Over a year ago

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8

eggyal Over a year ago

@cawecoy: See How can I fix MySQL error #1064?

barlop Over a year ago

@eggyal the point is that there seems to be a syntax error issue with what you wrote because the utf8 option isn't coming up in phpmyadmin i.imgur.com/jYKZqjI.png

Dor Cohen Over a year ago

For windows user, write the path in the following way: c:/temp/export.csv

Balmipour Over a year ago

Also note that MySQL's "utf8" charset doesn't handle 4 bytes unicode characters. If your utf8 export causes errors with some characters, use CHARACTER SET utf8mb4 instead.

|

Collectives™ on Stack Overflow

How export csv from mysql utf8

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related