MySQL：查询中的回车
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1031716/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: carriage-return in query
提问by Daniel
I have a query that exports data from two columns of each row into a file. In the file data from each column should be separated by carriage-return, something like this:
我有一个查询，将每行的两列中的数据导出到一个文件中。在文件中，每一列的数据都应该用回车符分隔，如下所示：
row1column1
row1column2
row2column1
row2column2
row3column1
row3column2
row1column1
row1column2
row2column1
row2column2
row3column1
row3column2
I tried using char(13):
我尝试使用 char(13)：
SELECT CONCAT(column1, char(13), column2) FROM my_table INTO outfile 'my_file'
and the output file seemed perfectly fine (each column data was in another line), but when I used it as input to a program that should accept described format, it didn't recognize it. However, when I manually erased all carriage-returns in the file and added them again by pressing "enter" key, my program recognized the file without a problem. When I tried with char(13), char(10), my output file looked like this:
并且输出文件看起来非常好（每列数据都在另一行中），但是当我将它用作应该接受描述格式的程序的输入时，它无法识别它。但是，当我手动删除文件中的所有回车符并通过按“回车”键再次添加它们时，我的程序可以毫无问题地识别该文件。当我尝试使用 char(13)、char(10) 时，我的输出文件如下所示：
row1column1
\
row1column2
row2column1
\
row2column1
row1column1
\
row1column2
row2column1
\
row2column1
I'm sure I'm missing something obvious here :)
我确定我在这里遗漏了一些明显的东西:)
回答by Haim Evgi
i see it in mysql site, hope its help u.
我在 mysql 网站上看到它，希望它对你有帮助。
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.
您应该使用以下语法以 Microsoft Excel 所需的格式创建 CSV 文件：
... INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
然而，带有回车符的字段可能会破坏 CSV，因为当找到 \r\n 换行符时，MySQL 会自动关闭一个字段。要解决此问题，请将所有 \r\n 中断替换为 \n。该字段不会在 \n 中断时关闭，它将被读入 Excel 中的单个单元格。您可以在同一 SQL 语句中执行此操作，例如：
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';
我还发现空值可能会破坏 CSV。这些可以用类似的方式处理：
SELECT IFNULL(possible_null_field, "") FROM table INTO OUTFILE '/temp.csv' FIELDS ESCAPED BY '""' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
注意：这会用一个空字符串替换 NULL 值，这在技术上不是一回事，但它会在 Excel 中为您提供一个空单元格，而不是破坏 CSV 结构并将以下单元格向左移动。
回答by RandallK
Haim Evgi's answer works, but:
Haim Evgi 的回答有效，但是：
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 had to change to:
我不得不改为：
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';
There was a double quote in the FIELDS ESCAPED BY, which gave this error in mysql: "Error 1083: Field separator argument is not what is expected; check the manual".
FIELDS ESCAPED BY 中有一个双引号，它在 mysql 中给出了这个错误：“错误 1083：字段分隔符参数不是预期的；检查手册”。
Changing it to a single quote stopped the error and exported a file. I was able to then import it into excel successfully with the newlines formatted correctly.
将其更改为单引号停止错误并导出文件。然后我能够使用正确格式化的换行符将其成功导入到excel中。
回答by Greg
Try just char(10) - that's "\n" - the UNIX way.
Just char(13) "\r" is the (old) mac way and "\r\n" is the windows way, but I suspect MySQL just uses \n for each row, so you'll need to match that.
试试 char(10) - 那是 "\n" - UNIX 方式。
只是 char(13) "\r" 是（旧）mac 方式，而 "\r\n" 是 Windows 方式，但我怀疑 MySQL 只是对每一行使用 \n，所以你需要匹配它。
回答by Tometzky
You probably have problems with differences in interpretation of newline between operating systems.
您可能在操作系统之间对换行符的解释上存在差异。
If your MySQL database is on Unix/Linux, and a file will be read on Unix or database is on Windows and will be read on Windows then try this:
如果您的 MySQL 数据库在 Unix/Linux 上，并且文件将在 Unix 上读取或数据库在 Windows 上并将在 Windows 上读取，请尝试以下操作：
select * into outfile 'my_file'
fields terminated by '\n' lines terminated by '\n'
from my_table;
If your MySQL database is on Unix/Linux, and a file will be read on Windows then try this:
如果您的 MySQL 数据库在 Unix/Linux 上，并且将在 Windows 上读取文件，请尝试以下操作：
select * into outfile 'my_file'
fields terminated by '\r\n' lines terminated by '\r\n'
from my_table;
You can also convert a file between "CRLF" (Windows) and "LF" (Unix) newlines using a small, command line utility called dos2unix and unix2dos, which is included in most Linux distributions.
您还可以使用名为 dos2unix 和 unix2dos 的小型命令行实用程序在“CRLF”（Windows）和“LF”（Unix）换行符之间转换文件，大多数 Linux 发行版中都包含该实用程序。
