如何与FIELDS ESCAPED BY一起在mysql SELECT ... OUTFILE语句中处理NULL值? NULL值当前被截断 [英] How do I handle NULL values in a mysql SELECT ... OUTFILE statement in conjunction with FIELDS ESCAPED BY? NULL values are currently being truncated
问题描述
我在结果集上使用MySQL的SELECT ... OUTFILE遇到一些困难，结果集包括空值和需要双引号转义的列(即，包含''字符的列).这就是我的outfile语法使用:
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'
我的问题与查询的FIELDS ESCAPED BY部分有关-如果省略此部分，则null值将正确导出(...，"\ N"，...在csv中是这样的)
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).
但是，在Excel中，包含双引号的列将被拆分为多行/列.这是因为excel要求通过将列中的''"字符写为""来对其进行转义.
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 '""'.
包含FIELDS ESCAPED BY子句可解决包含双引号字符的列的excel问题，但是，它会破坏NULL列. NULL列导出为(..."N，...)，同时缺少该列上的反斜杠和尾引号.在excel中，由于缺少右引号，这导致多个列相互折叠.
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.
我的目标是能够导出包含双引号和换行符的列，以及将空列导出为\ N，但是我似乎不知道该怎么做. MySQL文档指出，FIELDS ESCAPED BY影响了NULL列的输出方式，但我无法弄清楚'"'的转义序列如何导致在NULL列上加上反斜杠和尾引号
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
当前，我的解决方案是使用FIELDS ESCAPED BY将字符串输出到用户时，在每行上执行字符串替换，并将"N，"替换为" \ N，".这似乎可行，但这感觉不对，我担心它会引起一些问题
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()可能是一个选项，但是我们在代码中使用它的方式实际上很难实现.还需要为每个可能具有NULL值的列完成此操作，因此我想避免这种解决方案
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
谢谢！
推荐答案
我能够成功将MySQL查询结果保存为CSV并将其导入到Excel中，如下所示:
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",
...对于SELECT语句中的每个列或表达式，可能会返回NULL(\ N).这将确保CSV文件中的NULL值显示为正确引用的空字符串，而不是被错误引用的\ N.除了一个空字符串，您还可以指定一个表示NULL的值，例如...
...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",
- 使用以下OUTFILE选项:
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
请注意，ESCAPED BY和ENCLOSED BY都指定了一个双引号.我尚未测试OPTIONALLY ENCLOSED BY是否会成功，所以我将OPTIONALLY排除在外.
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.
根据CSV规范-RFC 4180，第2.7节，需要使用双引号将带引号的字段值内的另一个双引号转义.
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.
这篇关于如何与FIELDS ESCAPED BY一起在mysql SELECT ... OUTFILE语句中处理NULL值? NULL值当前被截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
