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