php - 如何从mysql utf8导出csv

Question

我想用命令直接从mysql导出csv

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

完美运行，但编码不是utf8。如何使导出的内容为utf8编码？

Answer 1

根据 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.

语法记录在 SELECT Syntax 下:

    [INTO OUTFILE '<strong><em>file_name</em></strong>'
      [CHARACTER SET <strong><em>charset_name</em></strong>]

因此:

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