Special characters in Mysql fields

You should use the following syntax to create a CSV file in the format expected by Microsoft Excel:

... INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

However fields with carriage returns may break the CSV as MySQL will automatically close a field when the \r\n line break is found. To work around this, replace all \r\n breaks with \n. The field does not close on \n breaks and it will be read into a single cell in Excel. You can do this in the same SQL statement, for example:

SELECT REPLACE(field_with_line_breaks, '\r\n', '\n') FROM table INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

I also found that null values could break the CSV. These can be handled in a similar way:

SELECT IFNULL(possible_null_field, "") FROM table INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Note: this replaces NULL values with an empty string which is technically not the same thing but it will give you an empty cell in Excel instead of breaking the CSV structure and shifting the following cells to the left.