Extra backslash \ when SELECT ... INTO OUTFILE ... in MySQL

Question

So I'm trying to export a MySQL table into CSV. I'm using this query:

SELECT * FROM business WHERE id > 0 AND id <= 20000 INTO OUTFILE "business.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n";

That output something like this:

http://postimage.org/image/2ghyenh5w/full/

The problem with this is that there's always an extra backslash \ where there's newline such as in the address field.

However, CSV exported from phpMyAdmin doesn't have it:

http://postimage.org/image/2gi026tno/full/

Any way to make the SELECT ... OUTFILE ... do the same?

The table I'm dealing with has 20 million records, phpMyAdmin can only handle about 500,000 records for every export action - or it will go blank or mysql server gone away, etc.

Answer 1

It looks like it's impossible for a MySQL export to correctly export both Newlines and Quotes.

When exporting, MySQL will automatically escape both

• Field delimiters, and
• Line delimiters

By default, the escape character is a backslash. You can override this by adding ESCAPED BY '' to your query.

Unfortunately, in a "normal" (Excel-compatible) CSV file, you probably want different encodings for newlines and quotes. Specifically, you want newlines to be unescaped, and quotes to be doubled.

E.g. If a value contains a newline like so:

This is line 1
And this is "Line 2", which contains quotes

it should become

"This is line 1
And this is ""Line 2"", which contains quotes"

The solution I found was to pre-escape the quotes, and add ESCAPED BY '' (an empty string) to my query.

SELECT REPLACE(field1, '"', '""'),  
       REPLACE(field2, '"', '""'),  
       ...  
FROM ...  
WHERE ...  
INTO OUTFILE '/someFile.csv'  
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''  
LINES TERMINATED BY '\n'