MySQL:如何在输出文件中转义反斜杠? [英] MySQL: How to escape backslashes in outfile?

问题描述

我想使用这个查询将一些字段输出到文件中:

SELECTCONCAT('[',GROUP_CONCAT(CONCAT(CHAR(13), CHAR(9), '{"name":"', name, '",'),CONCAT('"id":', CAST(rid AS UNSIGNED), '}')),字符(13), ']')AS json FROM `role`INTO OUTFILE '/tmp/roles.json'

在输出文件中，我得到如下内容:

<预><代码>[\ {"name":"匿名用户","rid":1},\ {"name":"经过身份验证的用户","rid":2},\ {"name":"admin","rid":3},\ {"name":"moderator","rid":4}]

如您所见，换行符 (char(13)) 没有反斜杠，但制表符 (char(9)) 有.我怎样才能摆脱它们?

更新Sundar G 给了我一个提示，所以我将查询修改为:

SELECTCONCAT('"姓名":', 姓名),CONCAT('摆脱":'，摆脱)INTO输出文件'/tmp/roles.json'以 ',' 结尾的字段行开始于 '\t{' 终止于 '},\n'从`角色`

我不知道为什么，但是这个语法从输出文件中去除了反斜杠:

 {"name":"anonymous user","rid":1},{"name":"经过身份验证的用户","rid":2},{"name":"admin","rid":3},{"name":"moderator","rid":4}

这已经是相当不错的输出了，但我还想在文件的开头和结尾添加左方括号和右方括号.我可以通过 MySQL 语法来执行此操作还是必须手动执行此操作?

解决方案

如 SELECT ... INTO 语法:

<块引用>

语句的 export_options 部分的语法由相同的 FIELDS 和 LINES 组成 与 LOAD DATA INFILE 声明.参见 第 13.2.6 节，LOAD DATA INFILE 语法"，有关 FIELDS 和 LINES 子句的信息，包括它们的默认值和允许值.

那个被引用的页面说:

<块引用>

如果你没有指定 FIELDS 或 LINES 子句，默认值和你写的一样:

由 '\t' 终止的字段由 '' 包围，由 '\\' 转义由 '\n' 终止的行 由 '' 开始

然后解释:

<块引用>

对于输出，如果FIELDS ESCAPED BY字符不为空，则用于在输出中作为以下字符的前缀:

• FIELDS ESCAPED BY 字符

• 字段[可选]由字符包围

• FIELDS TERMINATED BY 和 LINES TERMINATED BY 值的第一个字符

• ASCII 0(转义符后面实际写的是ASCII0"，不是零值字节)

如果FIELDS ESCAPED BY字符为空，则不转义任何字符，NULL输出为NULL，而不是\N.指定一个空的转义字符可能不是一个好主意，特别是如果数据中的字段值包含刚刚给出的列表中的任何字符.

因此，由于您没有明确指定 FIELDS 子句，字段中出现的任何默认 TERMINATED BY 字符(即制表符)将默认转义ESCAPED BY 字符(即反斜杠):因此您正在创建的制表符被转义了.为避免这种情况，请明确指定不同的字段终止字符或使用空字符串作为转义字符.

但是，您还应该注意，结果的大小将受到 group_concat_max_len.也许更好的选择是:

SELECT json FROM (SELECT 1 AS sort_col, '[' AS json联合所有SELECT 2, CONCAT('\t{"name":', QUOTE(name), ',"id":', CAST(rid AS UNSIGNED), '}')FROM 角色联合所有选择 3, ']') t按 sort_col 排序INTO OUTFILE '/tmp/roles.json' 字段由 '' 转义

I want to output some fields into file using this query:

SELECT
    CONCAT('[',
        GROUP_CONCAT(
            CONCAT(CHAR(13), CHAR(9), '{"name":"', name, '",'),
            CONCAT('"id":', CAST(rid AS UNSIGNED), '}')
        ),
    CHAR(13), ']')
AS json FROM `role`
INTO OUTFILE '/tmp/roles.json'

In output file I'm getting something like this:

[
\       {"name":"anonymous user","rid":1},
\       {"name":"authenticated user","rid":2},
\       {"name":"admin","rid":3},
\       {"name":"moderator","rid":4}
]

As you can see, newlines (char(13)) has no backslashes, but tab characters (char(9)) has. How can I get rid of them?

UPDATE Sundar G gave me a cue, so I modified the query to this:

SELECT
    CONCAT('"name":', name),
    CONCAT('"rid":', rid)

INTO outfile '/tmp/roles.json'

FIELDS TERMINATED BY ','
LINES STARTING BY '\t{' TERMINATED BY '},\n'

FROM `role`

I don't know why, but this syntax strips backslashes from output file:

       {"name":"anonymous user","rid":1},
       {"name":"authenticated user","rid":2},
       {"name":"admin","rid":3},
       {"name":"moderator","rid":4}

This is already pretty nice output, but I also would like to add opening and closing square brackets at the beginning and at the end of the file. Can I do this by means of MySQL syntax or I have to do that manually?

解决方案

As described in SELECT ... INTO Syntax:

The syntax for the export_options part of the statement consists of the same FIELDS and LINES clauses that are used with the LOAD DATA INFILE statement. See Section 13.2.6, "LOAD DATA INFILE Syntax", for information about the FIELDS and LINES clauses, including their default values and permissible values.

That referenced page says:

If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

and later explains:

For output, if the FIELDS ESCAPED BY character is not empty, it is used to prefix the following characters on output:

• The FIELDS ESCAPED BY character

• The FIELDS [OPTIONALLY] ENCLOSED BY character

• The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values

• ASCII 0 (what is actually written following the escape character is ASCII "0", not a zero-valued byte)

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

Therefore, since you have not explicitly specifying a FIELDS clause, any occurrences of the default TERMINATED BY character (i.e. tab) within a field will be escaped by the default ESCAPED BY character (i.e. backslash): so the tab character that you are creating gets so escaped. To avoid that, explicitly specify either a different field termination character or use the empty string as the escape character.

However, you should also note that the size of your results will be limited by group_concat_max_len. Perhaps a better option would be:

SELECT json FROM (
  SELECT 1 AS sort_col, '[' AS json
UNION ALL
  SELECT 2, CONCAT('\t{"name":', QUOTE(name), ',"id":', CAST(rid AS UNSIGNED), '}')
  FROM   role
UNION ALL
  SELECT 3, ']'
) t
ORDER BY sort_col
INTO OUTFILE '/tmp/roles.json' FIELDS ESCAPED BY ''

这篇关于MySQL:如何在输出文件中转义反斜杠?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！