MySQL 导出到 outfile：CSV 转义字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1119312/
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 export into outfile : CSV escaping chars
提问by Derek Organ
I've a database table of timesheets with some common feilds.
我有一个包含一些常见字段的时间表数据库表。
id, client_id, project_id, task_id, description, time, date
There are more but thats the gist of it.
还有更多，但这就是它的要点。
I have an export running on that table to a CSV file overnight to give the user a backup of their data. It also is used as a data import for a macro Excel file with some custom reports.
我在一夜之间在该表上运行导出到 CSV 文件，以便为用户提供其数据的备份。它还用作带有一些自定义报告的宏 Excel 文件的数据导入。
This all works with me looping through the timesheets with php and printing the lines to a file.
这一切都适用于我使用 php 循环浏览时间表并将行打印到文件中。
The problem is with a big database it can take hours to run which isn't acceptable. So I rewrote it with the MySQL INTO OUTFILEcommand and it reduced it down to a few seconds to run which was great.
问题在于大型数据库可能需要数小时才能运行，这是不可接受的。所以我用 MySQLINTO OUTFILE命令重写了它，它将它减少到几秒钟来运行，这很棒。
The problem now is I can't seem to escape all the new line characters, etc., in the description field. Really, a user can type potentially any combination of characters in here including carriage returns/new lines.
现在的问题是我似乎无法转义描述字段中的所有换行符等。实际上，用户可以在此处键入任何字符组合，包括回车符/换行符。
This is a snippet of the MySQL code I have:
这是我拥有的 MySQL 代码片段：
SELECT id,
client,
project,
task,
REPLACE(REPLACE(ifnull(ts.description,''),'\n',' '),'\r',' ') AS description,
time,
date
INTO OUTFILE '/path/to/file.csv'
FIELDS ESCAPED BY '""'
TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM ....
But...
但...
When I try look at the source of the output file, newlines still exist in the file, therefore the CSV import for the Excel breaks all the fancy macros and pivot tables the Excel wizard has created.
当我尝试查看输出文件的源时，文件中仍然存在换行符，因此 Excel 的 CSV 导入破坏了 Excel 向导创建的所有花哨的宏和数据透视表。
Any thoughts on a best course of action?
关于最佳行动方案的任何想法？
回答by Question Mark
I think your statement should look like:
我认为你的陈述应该是这样的：
SELECT id,
client,
project,
task,
description,
time,
date
INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM ts
Mainly without the FIELDS ESCAPED BY '""'option, OPTIONALLY ENCLOSED BY '"'will do the trick for description fields etc and your numbers will be treated as numbers in Excel (not strings comprising of numerics)
主要是没有FIELDS ESCAPED BY '""'选项，OPTIONALLY ENCLOSED BY '"'会对描述字段等进行处理，并且您的数字将被视为 Excel 中的数字（不是由数字组成的字符串）
Also try calling:
也尝试调用：
SET NAMES utf8;
before your outfile select, that might help getting the character encodings inline (all UTF8)
在选择输出文件之前，这可能有助于获得内联字符编码（所有 UTF8）
Let us know how you get on.
让我们知道您的身体情况如何。
回答by Felix Z
Here is what worked here: Simulates Excel 2003 (Save as CSV format)
这是这里的工作：模拟 Excel 2003（另存为 CSV 格式）
SELECT
REPLACE( IFNULL(notes, ''), '\r\n' , '\n' ) AS notes
FROM sometables
INTO OUTFILE '/tmp/test.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
- Excel saves \r\n for line separators.
- Excel saves \n for newline characters within column data
- Have to replace \r\n inside your data first otherwise Excel will think its a start of the next line.
- Excel 将 \r\n 保存为行分隔符。
- Excel 为列数据中的换行符保存 \n
- 必须先替换数据中的 \r\n 否则 Excel 会认为它是下一行的开始。
回答by devuxer
What happens if you try the following?
如果您尝试以下操作会发生什么？
Instead of your double REPLACEstatement, try:
而不是你的双重REPLACE声明，试试：
REPLACE(IFNULL(ts.description, ''),'\r\n', '\n')
Also, I think it should be LINES TERMINATED BY '\r\n'instead of just '\n'
另外，我认为它应该LINES TERMINATED BY '\r\n'而不是仅仅'\n'
回答by John Y
Without actually seeing your output file for confirmation, my guess is that you've got to get rid of the FIELDS ESCAPED BYvalue.
没有实际看到您的输出文件进行确认，我的猜测是您必须摆脱该FIELDS ESCAPED BY值。
MySQL's FIELDS ESCAPED BYis probably behaving in two ways that you were not counting on: (1) it is only meant to be one character, so in your case it is probably equal to just one quotation mark; (2) it is used to precede eachcharacter that MySQL thinks needs escaping, including the FIELDS TERMINATED BYand LINES TERMINATED BYvalues. This makes sense to most of the computing world, but it isn't the way Excel does escaping.
MySQL 的FIELDS ESCAPED BY行为可能有两种你没有想到的方式：(1) 它只是一个字符，所以在你的情况下它可能只等于一个引号；(2) 用于MySQL认为需要转义的每个字符之前，包括FIELDS TERMINATED BY和LINES TERMINATED BY值。这对大多数计算世界来说都是有意义的，但这不是 Excel 转义的方式。
I think your double REPLACEis working, and that you are successfully replacing literal newlines with spaces (two spaces in the case of Windows-style newlines). But if you have any commas in your data (literals, not field separators), these are being preceded by quotation marks, which Excel treats much differently than MySQL. If that's the case, then the erroneous newlines that are tripping up Excel are actually newlines that MySQL had intended as line terminators.
我认为您的 doubleREPLACE正在工作，并且您成功地用空格替换了文字换行符（在 Windows 样式的换行符的情况下为两个空格）。但是，如果您的数据中有任何逗号（文字，而不是字段分隔符），则它们前面都带有引号，Excel 的处理方式与 MySQL 有很大不同。如果是这种情况，那么绊倒 Excel 的错误换行符实际上是 MySQL 打算作为行终止符的换行符。
回答by Jorge Bernal
Probably won't help but you could try creating a CSV table with that content:
可能无济于事，但您可以尝试使用该内容创建一个 CSV 表：
DROP TABLE IF EXISTS foo_export;
CREATE TABLE foo_export LIKE foo;
ALTER TABLE foo_export ENGINE=CSV;
INSERT INTO foo_export SELECT id,
client,
project,
task,
REPLACE(REPLACE(ifnull(ts.description,''),'\n',' '),'\r',' ') AS description,
time,
date
FROM ....
