在shell脚本中执行多行的mysql命令

20

当我尝试通过shell脚本在mysql中执行多行SQL语句时:

mysql -uroot -ppass mydb <<<EOF
SELECT * INTO OUTFILE 'table.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
FROM mytable limit 1;
EOF

我遇到了语法错误:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'EOF' at line 1

如何正确地编写脚本?

2个回答

22

Bash Here Document 的语法如下:

COMMAND <<InputComesFromHERE
...
...
...
InputComesFromHERE

你的代码中多了一个额外的 <

为了准备和测试,可以将COMMAND(在这种情况下是 mysql -uroot -ppass mydb)替换为 cat,以查看将要执行的确切 SQL 代码。


4

对于未来的读者,如果他们希望使用bash批量导出,可以采用以下简单方法,该方法在mariadb上进行过测试,同样适用于mysql。

akshay@ideapad:/tmp$ mysql -u someuser -p test -e "select * from offices"
Enter password: 
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| officeCode | city          | phone            | addressLine1             | addressLine2 | state      | country   | postalCode | territory |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| 1          | San Francisco | +1 650 219 4782  | 100 Market Street        | Suite 300    | CA         | USA       | 94080      | NA        |
| 2          | Boston        | +1 215 837 0825  | 1550 Court Place         | Suite 102    | MA         | USA       | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000  | 523 East 53rd Street     | apt. 5A      | NY         | USA       | 10022      | NA        |
| 4          | Paris         | +33 14 723 4404  | 43 Rue Jouffroy D'abbans | NULL         | NULL       | France    | 75017      | EMEA      |
| 5          | Tokyo         | +81 33 224 5000  | 4-1 Kioicho              | NULL         | Chiyoda-Ku | Japan     | 102-8578   | Japan     |
| 6          | Sydney        | +61 2 9264 2451  | 5-11 Wentworth Avenue    | Floor #2     | NULL       | Australia | NSW 2010   | APAC      |
| 7          | London        | +44 20 7877 2041 | 25 Old Broad Street      | Level 7      | NULL       | UK        | EC2N 1HN   | EMEA      |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+

如果您是以非root用户身份进行导出,则请按以下方式设置权限

root@ideapad:/tmp# mysql -u root -p
MariaDB[(none)]> UPDATE mysql.user SET File_priv = 'Y' WHERE user='someuser' AND host='localhost';

重新启动或重新加载mysqld

akshay@ideapad:/tmp$ sudo su
root@ideapad:/tmp#  systemctl restart mariadb

示例代码片段

akshay@ideapad:/tmp$ cat test.sh 
#!/usr/bin/env bash

user="someuser"
password="password"
database="test"

mysql -u"$user" -p"$password" "$database" <<EOF
SELECT * 
INTO OUTFILE '/tmp/csvs/offices.csv' 
FIELDS TERMINATED BY '|' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
FROM offices;
EOF

执行

akshay@ideapad:/tmp$ mkdir -p /tmp/csvs
akshay@ideapad:/tmp$ chmod +x test.sh
akshay@ideapad:/tmp$ ./test.sh 
akshay@ideapad:/tmp$ cat /tmp/csvs/offices.csv 
"1"|"San Francisco"|"+1 650 219 4782"|"100 Market Street"|"Suite 300"|"CA"|"USA"|"94080"|"NA"
"2"|"Boston"|"+1 215 837 0825"|"1550 Court Place"|"Suite 102"|"MA"|"USA"|"02107"|"NA"
"3"|"NYC"|"+1 212 555 3000"|"523 East 53rd Street"|"apt. 5A"|"NY"|"USA"|"10022"|"NA"
"4"|"Paris"|"+33 14 723 4404"|"43 Rue Jouffroy D'abbans"|\N|\N|"France"|"75017"|"EMEA"
"5"|"Tokyo"|"+81 33 224 5000"|"4-1 Kioicho"|\N|"Chiyoda-Ku"|"Japan"|"102-8578"|"Japan"
"6"|"Sydney"|"+61 2 9264 2451"|"5-11 Wentworth Avenue"|"Floor #2"|\N|"Australia"|"NSW 2010"|"APAC"
"7"|"London"|"+44 20 7877 2041"|"25 Old Broad Street"|"Level 7"|\N|"UK"|"EC2N 1HN"|"EMEA"


网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接