bash 在shellscript中执行多行mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8802131/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute multiline mysql in shellscript
提问by user121196
When I attempt to execute multi-line mysqlin shellscript
当我尝试在shellscript 中执行多行mysql时
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
is getting syntax error.
正在收到语法错误。
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
what's the right way to script it?
编写脚本的正确方法是什么？
采纳答案by álvaro González
The syntax for bash heredocis:
bash heredoc的语法是：
COMMAND <<InputComesFromHERE
...
...
...
InputComesFromHERE
So you have an extra <.
所以你有一个额外的<.
In order to test, you should be able to replace mysql -uroot -ppass mydb SQLwith echoand obtain the exact SQL code as intended.
为了测试，你应该能够取代mysql -uroot -ppass mydb SQL以echo并获取准确的SQL代码如预期。
回答by Akshay Hegde
For future readers, one easy way is as follows if they wish to export in bulk using bash, tested on mariadb, same should work in mysql too.
对于未来的读者，如果他们希望使用 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 |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
If you're exporting by non-root user then set permission like below
如果您是由非 root 用户导出，则设置如下权限
root@ideapad:/tmp# mysql -u root -p
MariaDB[(none)]> UPDATE mysql.user SET File_priv = 'Y' WHERE user='someuser' AND host='localhost';
Restart or Reload mysqld
重新启动或重新加载 mysqld
akshay@ideapad:/tmp$ sudo su
root@ideapad:/tmp# systemctl restart mariadb
Sample code snippet
示例代码片段
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
Execute
执行
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"
