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

I had the same problem, and I found out (after importing the csv file into a spreadsheet) that there were line breaks in some varchar fields in the MySQL table. After deleting the line breaks, the export worked correctly.

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'

Try this:

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

I think the issue is that MySQL is trying to escape newline ('\n') in your text fields because it's your line terminator.

FIELDS ESCAPED BY controls how to write special characters. If the FIELDS ESCAPED BY character is not empty, it is used as a prefix that precedes following characters on output:

The FIELDS ESCAPED BY character

The FIELDS [OPTIONALLY] ENCLOSED BY character

The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values

ASCII NUL (the zero-valued byte; what is actually written following the escape character is ASCII “0”, not a zero-valued byte)

(MySQL)

I don't really understand why it's doing what it's doing in your case, but I was able to get something like that on my Mac and the query above seemed to fix the output in my case.

Hope that helps!