mysql-5.7

How to output `\N` in MySQL 5.7 when redirecting from a SQL script?

I have the following example.sql file:

SELECT '\N', '\\N', SUBSTRING('\\N', 1, 2), SUBSTRING('\\N', 2, 2), CONCAT('\\', 'N');

Executing it with mysql <example.sql or cat example.sql | mysql outputs:

N   \N  SUBSTRING('\\N', 1, 2)  SUBSTRING('\\N', 2, 2)  CONCAT('\\', 'N')
N   \\N \\N N   \\N

Creating example-2.sql:

source example.sql

And mysql <example-2.sql has the same output.

But executing via source in the mysql shell outputs:

+---+----+------------------------+------------------------+-------------------+
| N | \N | SUBSTRING('\\N', 1, 2) | SUBSTRING('\\N', 2, 2) | CONCAT('\\', 'N') |
+---+----+------------------------+------------------------+-------------------+
| N | \N | \N                     | N                      | \N                |
+---+----+------------------------+------------------------+-------------------+

Using mysql -e "SELECT '\\N', '\\\\N', SUBSTRING('\\\\N', 1, 2), SUBSTRING('\\\\N', 2, 2), CONCAT('\\\\', 'N');" has the same output.

The salient difference in output is the \N vs the \\N. Is there a way to get these to be consistent (preferably having \\N output \N) or at least get the first one to output \N somehow?

What I'm really trying to do is have IFNULL output \N for null values (and from what I've seen, using OUTFILE doesn't work across remote connections).

Solution

  • The following outputs what I would like:

    $ mysql -e "$(cat example.sql)"
+---+----+------------------------+------------------------+-------------------+
| N | \N | SUBSTRING('\\N', 1, 2) | SUBSTRING('\\N', 2, 2) | CONCAT('\\', 'N') |
+---+----+------------------------+------------------------+-------------------+
| N | \N | \N                     | N                      | \N                |
+---+----+------------------------+------------------------+-------------------+

    However, when redirecting the output to a file, the contents again have the double-backslash:

    $ mysql -e "$(cat example.sql)" >example.out && cat example.out
N   \N  SUBSTRING('\\N', 1, 2)  SUBSTRING('\\N', 2, 2)  CONCAT('\\', 'N')
N   \\N \\N N   \\N

    At this point, this question is related to How to pipe to "null object" in bash?