使用 shell 脚本执行多个 MySQL 命令的更好方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32869275/
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
A better way to execute multiple MySQL commands using shell script
提问by Brian
I would like to write a *.sh script to execute multiple MySQL commands.
我想编写一个 *.sh 脚本来执行多个 MySQL 命令。
Currently, what I can do is something like the following
目前，我可以做的是如下
mysql -h$host -u$user -p$password -e "drop database $dbname;"
mysql -h$host -u$user -p$password -e "create database $dbname;"
mysql -h$host -u$user -p$password -e "another MySQL command"
...
Is there a way to avoid typing " mysql -h$host -u$user -p$password -e" every time I want to execute a MySQL command?
有没有办法避免每次我想执行 MySQL 命令时都输入“mysql -h$host -u$user -p$password -e”？
回答by EricWang
I think you can execute MySQL statements from a text file, for example
我认为您可以从文本文件中执行 MySQL 语句，例如
here is the cmds.txt file which contains MySQL commands:
这是包含 MySQL 命令的 cmds.txt 文件：
select colA from TableA;
select colB from TableB;
select colC from TableC;
To execute them using shell script, type
要使用 shell 脚本执行它们，请键入
mysql -h$host -u$user -p$password db_dbname < cmds.txt
This way, you separate your MySQL commands from your shell script.
通过这种方式，您可以将 MySQL 命令与 shell 脚本分开。
You may want your script to display progress information to you. For this you can invoke mysql with "--verbose" option.
您可能希望脚本向您显示进度信息。为此，您可以使用“--verbose”选项调用 mysql。
For more information, see https://dev.mysql.com/doc/refman/5.6/en/mysql-batch-commands.html
有关更多信息，请参阅https://dev.mysql.com/doc/refman/5.6/en/mysql-batch-commands.html
回答by baao
You can use a single multiquery:
您可以使用单个多查询：
mysql -h$host -u$user -p$password -e "drop database $dbname;create database $dbname;another MySQL command;"
Simply write all your queries seperated by ;. They will be run one after the other.
只需编写以;.分隔的所有查询。他们将一个接一个地运行。
回答by Erwin Wessels
Note that you can also use a HERE doc to have the queries within the same script:
请注意，您还可以使用 HERE 文档在同一脚本中进行查询：
mysql -h$host -u$user -p$password db_dbname <<'EOF'
select colA from TableA;
select colB from TableB;
select colC from TableC;
EOF
Note that I've used 'EOF'rather than EOFin the first line in order to prevent the contents of the script to disable parameter substitution (especially the `can be problematic)
请注意，我在第一行使用了'EOF'而不是EOF为了防止脚本的内容禁用参数替换（尤其是`可能有问题的）
Also note that there should not be any whitespace before the final EOF(except if you use <<-rather than <<-- in that case leading tab characters are stripped):
另请注意，final 之前EOF不应有任何空格（除非您使用<<-而不是<<-- 在这种情况下前导制表符被剥离）：
mysql -h$host -u$user -p$password db_dbname <<- 'EOF'
?select colA from TableA;
?select colB from TableB;
?select colC from TableC;
?EOF
(Replace the ?with a tab character).
（用?制表符替换）。
For more info on the HERE doc syntax, see the bash documentation.
有关 HERE doc 语法的更多信息，请参阅bash 文档。
回答by Dave
There are several ways, in linux you have:
有几种方法，在linux中你有：
From the mysql cli:
从 mysql cli：
mysql> source mycmds.sql
Using Pipes:
使用管道：
echo "SELECT ..; INSERT ..;" | mysql ...
Executing commands from a file using pipes or redirection:
使用管道或重定向从文件执行命令：
cat file.sql | mysql ... OR mysql .. < file.sql
