如何与 FIELDS ESCAPED BY 一起处理 mysql SELECT ... OUTFILE 语句中的 NULL 值？ NULL 值当前被截断

发布于 2024-10-10 08:36:47 字数 917 浏览 3 评论 0原文

我在结果集上使用 MySQL 的 SELECT ... OUTFILE 时遇到一些困难，这些结果集包括空值和需要双引号转义的列（即包含 '"' 字符的列）。这是我正在使用的 outfile 语法

INTO OUTFILE '$csv_file' 
FIELDS ESCAPED BY '""' TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

：空值将正确导出（...，“\N”，...是 csv 中的样子）。

问题与查询的“FIELDS ESCAPED BY”部分有关 - 如果省略此部分，则包含双引号的字符将在 Excel 中拆分为多行/列，这是因为 Excel 要求将列内的 '"' 字符写为 '""' 进行转义。

包含 FIELDS ESCAPED BY 子句修复了包含双引号字符的列的 Excel 问题，但是，它破坏了 NULL 列。 NULL 列导出为 ( ..."N,... )，列上缺少反斜杠和尾部引号。在 Excel 中，由于缺少结束引号，这会导致多个列相互折叠。

我的目标是能够导出包含双引号和换行符的列，以及将空列导出为 \N，但是我似乎不知道如何执行此操作，MySQL 文档指出 FIELDS ESCAPED BY 如何影响 NULL。列已输出，但我无法弄清楚 '""' 的转义序列如何导致删除 NULL 列上的反斜杠和尾随引号

目前，我的解决方案是在输出时对每一行执行字符串替换通过使用 FIELDS ESCAPED BY 并将 '"N,' 替换为 '"\N",' 来向用户显示。这似乎可行，但感觉不太对劲，而且我担心它会在选择列上引起某种问题

IFNULL() 可能是一个选项，但我们在代码中使用它的方式，实际上执行起来相当困难。还需要对可能具有 NULL 值的每一列执行此操作，因此如果可以的话，我想避免这是一个解决方案

谢谢！

原文

I'm encountering some difficulties using MySQL's SELECT ... OUTFILE on result sets that include both null values and columns that require double quote escaping (ie, columns that contain '"' characters). This is the outfile syntax I am using:

INTO OUTFILE '$csv_file' 
FIELDS ESCAPED BY '""' TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

My problem is concerning the FIELDS ESCAPED BY portion of the query - if this portion is omitted, then null values will export properly (...,"\N",... is what it looks like in the csv).

However, columns that contain double quotes will get split across multiple lines/columns in excel. This is because excel requires that '"' characters inside columns to be escaped by writing them as '""'.

Including the FIELDS ESCAPED BY clause fixes the excel problem with columns containing double quote characters, however, it breaks NULL columns. NULL columns get exported as ( ..."N,... ) missing both the backslash and the trailing quotation mark on the column. In excel, this causes multiple columns to collapse into each other due to the lack of a closing quotation.

My goal is to be able to export columns that contain double quotes and newlines, as well as export null columns as \N, however I can't seem to figure out how to do it. MySQL docs state that FIELDS ESCAPED BY affects how NULL columns are outputted, but I can't figure out how an escape sequence of '""' results in dropping the backslash and the trailing quote on a NULL column

Currently, my solution is to perform a string replace on each line as I output it to the user, by using FIELDS ESCAPED BY and replacing '"N,' with '"\N",'. This seems to work, but it doesn't feel right, and I'm afraid of it causing some sort of issues down the line

IFNULL( ) on the select columns is potentially an option, but the way we are using this in our code, is actually quite difficult to implement. It also needs to be done for each column that could potentially have NULL values, so it's a solution I'd like to avoid if I can

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

放低过去 2024-10-17 08:36:48

我能够成功地将 MySQL 查询结果保存为 CSV 并将其导入 Excel，如下所示：

使用表单...
```
IFNULL(ColumnA, "" ) AS "Column A",
```

，可能会返回 NULL (\N)。这将确保 CSV 文件中的 NULL 值显示为正确引用的空字符串，而不是错误引用的 \N。您可以指定一个值来表示 NULL，而不是空字符串，例如...

    IFNULL(ColumnA, "~NULL~" ) AS "Column A",

使用以下 OUTFILE 选项：

FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'

请注意，ESCAPED BY 指定一个双引号，ENCLOSED BY 也是如此。我还没有测试 OPTIONALLY ENCLOSED BY 是否会成功，所以我就将 OPTIONALLY 排除在外。

根据 CSV 规范 - RFC 4180 第 2.7 节，需要使用双引号来转义带引号的字段值中的另一个双引号。

I was able to successfully save MySQL query results as CSV and import them into Excel as follows:

Use the form...
```
IFNULL(ColumnA, "" ) AS "Column A",
```

...for each column or expression in your SELECT statement than can possibly return a NULL (\N). This will ensure NULL values in your CSV file appear as properly quoted empty strings rather than improperly quoted \N's. Instead of an empty string, you could possibly specify a value to represent a NULL, e.g...

    IFNULL(ColumnA, "~NULL~" ) AS "Column A",

Use the following OUTFILE options:

FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'

Note that ESCAPED BY specifies one double quote, as does ENCLOSED BY. I haven't tested whether OPTIONALLY ENCLOSED BY will be successful, so I just leave OPTIONALLY out.

Using a double-quote to escape another double-quote within a quoted field value is required per the CSV specification - RFC 4180, section 2.7.

回复收藏 0 原文