MySQL: How to escape backslashes in outfile?

Question

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?

Answer 1

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 ''

Answer 2

Try this query like
SELECT your_fields INTO outfile '/path/file' fields enclosed by '"' terminated by ',' lines terminated by '\n' FROM table;
hope this works..