使用shell脚本执行多个MySQL命令的更好方法

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”？

4 个解决方案

#1

I think you can execute MySQL statements from a text file, for example

我想你可以从文本文件中执行MySQL语句

here is the cmds.txt file which contains MySQL commands:

这是cmds.txt文件，其中包含MySQL命令：

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

#2

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 EOF in 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):

另请注意，在最终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文档。

#3

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

#4

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.

简单地写下您所分隔的所有查询;他们将一个接一个地运行。

#1