11

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?

BlackWhite
  • 814
  • 2
  • 12
  • 26
  • Please take a look at this u will get some idea. http://stackoverflow.com/questions/17108249/export-data-from-mysql-to-excel-with-utf-8-encoding – piyush pankaj Sep 06 '13 at 10:56

1 Answers1

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' ;
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • #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 – cawecoy Jun 23 '14 at 02:17
  • @cawecoy: See [How can I fix MySQL error #1064?](http://stackoverflow.com/q/23515347) – eggyal Jun 23 '14 at 06:24
  • @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 http://i.imgur.com/jYKZqjI.png – barlop Oct 23 '16 at 04:28
  • For windows user, write the path in the following way: c:/temp/export.csv – Dor Cohen Dec 05 '16 at 13:15
  • Note : use **`SHOW CHARACTER SET;`** to get the list of available charsets. – Balmipour Oct 09 '17 at 09:59
  • 2
    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. – Balmipour Jan 04 '18 at 14:19