mysql导出数据换行符_将MySQL数据导出到CSV文件时如何保留换行符？

I need to export some data from mysql to a csv file. But one of the columns have line breaks and I need to export the data to a csv file saving break lines.

At this moment I am using the following sql query:

select username, description from users into outfile '/tmp/test.csv' FIELDS ESCAPED BY '"' TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

But that sql query doesn't save break lines in the csv file. I am not an expert with MySQL, if someone could help me I'd be grateful

解决方案

You seem to be hitting this bug: https://bugs.mysql.com/bug.php?id=40320

The bug has been reported since 2008. I just tested and found it is still a bug in MySQL 8.0.3.

I can work around the bug this way:

SELECT username, REPLACE(description, '\n', '\\n') FROM users

INTO OUTFILE '/tmp/test.csv' FIELDS ESCAPED BY '"' TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

The file is exported with literal \n text (that is two characters). So when the data is loaded, the replacement must be reversed:

LOAD DATA INFILE '/tmp/test.csv' INTO TABLE users

FIELDS ESCAPED BY '"' TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

SET description = REPLACE(description, '\\n', '\n');

This is not very graceful, of course, but it's a workaround.

The other option is to write an application to do the export and import, and refrain from using SELECT...INTO OUTFILE or LOAD DATA INFILE.