Thursday, March 8, 2018

Backslash escape in Ubuntu and MySQL export data from command line

I need write a bash script to export data from mysql.
The below command ran got good results in mysql shell.

Select [datacolum] from [table] into outfile 'outputfile'  FIELDS TERMINATED BY ',' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';

And I ran it in command line:

mysql -u user -ppassword database <<EOF
Select [datacolum] from [table] into outfile 'outputfile'  FIELDS TERMINATED BY ',' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';
EOF

I got exception :

ERROR 1049 (42000) at line 1: Unknown database 'n';'

Finally I realized:

The backslash escape in Ubuntu:

root@ubuntu:~# echo '\'
\
root@ubuntu:~# echo "'\'"
'\'
root@ubuntu:~# echo '\\'
\\
root@ubuntu:~# echo "'\\'"
'\'

So the correct ran the above sql in command line should be:

mysql -u user -ppassword database <<EOF
Select [datacolum] from [table] into outfile 'outputfile'  FIELDS TERMINATED BY ',' ESCAPED BY '\\\\' LINES TERMINATED BY '\r\n';
EOF