| Bug #46434 | SELECT... INTO OUTFILE adds backslashes | ||
|---|---|---|---|
| Submitted: | 28 Jul 2009 15:13 | Modified: | 8 Aug 2009 10:04 |
| Reporter: | Luca Zavarella | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.1.34-community | OS: | Any (Vista SP2, Mac OS X) |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
| Tags: | backslash, carriage return, into outfile, line feed | ||
[28 Jul 2009 15:25]
Luca Zavarella
I got the same error type in the case of the carriage return char too:
--
-- This works fine...
--
SELECT CONCAT('Bye!', CHAR(13), 'Bye!') AS Field1
INTO OUTFILE '\\test.txt';
--
-- but this adds a backslash too!!
--
SELECT CONCAT('Bye!', CHAR(13), 'Bye!') AS Field1
INTO OUTFILE '\\test.txt'
FIELDS TERMINATED BY '\r\n';
[28 Jul 2009 15:36]
Valeriy Kravchuk
I think this is expected behavior actually (at least this format is described at http://dev.mysql.com/doc/refman/5.1/en/load-data.html, and INTO OUTFILE is intended to produce results ready to load by LOAD DATA). But it definitely should be described in more details at http://dev.mysql.com/doc/refman/5.1/en/select.html. Maybe with example that will show when escape characters are introduced.
[28 Jul 2009 21:23]
Luca Zavarella
I use the INTO OUTFILE feature to send emails from MySQL by the "pickup" folder in an Exchange Server. So I need chars like \r or \n in order to format mail messages.
[7 Aug 2009 19:42]
Paul DuBois
The default for ESCAPED BY is '\', which is why you see the observed behavior. To disable escaping, use ESCAPED BY ''. This is explained at http://dev.mysql.com/doc/refman/5.1/en/load-data.html, which is cross referenced from http://dev.mysql.com/doc/refman/5.1/en/select.html.
[8 Aug 2009 10:04]
Luca Zavarella
Thank you so much for the suggestion. It wasn't so clear to me in the documentation. Problem solved!
Description: If a string contains line feed chars, the INTO OUTFILE option adds a backslash for each of those chars in the output file. How to repeat: SELECT CONCAT('Bye!', CHAR(10), 'Bye!') INTO OUTFILE '\\test.txt'; Result: Bye!\ Bye! Expected: Bye! Bye! Suggested fix: I can't fix it!