How to preserve line breaks when exporting MySQL data to a CSV file?

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

Solution

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.