多条sql执行时如果在中间的语句出现错误,后续会不会直接执行,如何进行设定,这篇文章将会进行简单的整理和说明。
环境准备
环境准备可参看:
注:已有MySQL的可以跳过此步骤。
多行语句的正常执行
比如执行多条正常执行的语句,示例命令如下所示:
select version();
select "Hello LiuMiao" as "Greetings";
select 20+22 as Result;
使用HereDocument方式执行如下所示:
liumiaocn:~ liumiao$ mysql -uroot -proot <<EOF
> select version();
> select "Hello LiuMiao" as "Greetings";
> select 20+22 as Result;
> EOF
mysql: [Warning] Using a password on the command line interface can be insecure.
version()
8.0.11
Greetings
Hello LiuMiao
Result
42
liumiaocn:~ liumiao$
多行语句中间出错时的缺省动作
Oracle多行语句执行出错时在sqlplus中会继续执行,而在mysql控制台中会怎样呢?我们可以在上述语句中添加一行错误的语法或者命令来进行验证:
mysql -uroot -proot <<EOF
select version();
errorcommand;
select "Hello LiuMiao" as "Greetings";
select 20+22 as Result;
EOF
执行结果如下所示
liumiaocn:~ liumiao$ mysql -uroot -proot <<EOF
> select version();
> errorcommand;
> select "Hello LiuMiao" as "Greetings";
> select 20+22 as Result;
> EOF
mysql: [Warning] Using a password on the command line interface can be insecure.
version()
8.0.11
ERROR 1064 (42000) at line 2: 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 'errorcommand' at line 1
liumiaocn:~ liumiao$
可以看到,缺省情况下碰到错误就停下来了。
- WHENEVER SQLERROR
在oracle中通过WHENEVER SQLERROR来进行控制。语法如下所示
WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}
在2014年7月,mysql中使用source是否提供相关的类似机制的问题中,最终引入了Oracle此项功能在mysql中引入的建议,详细请参看:是否引入WHENNEVER SQLERROR
- force选项
在mysql中可以使用force选项来一定程度可以实现出错仍然继续的控制,此处加上force选项之后,执行日志如下所示
liumiaocn:~ liumiao$ mysql -uroot -proot --force <<EOF
> select version();
> errorcommand;
> select "Hello LiuMiao" as "Greetings";
> select 20+22 as Result;
> EOF
mysql: [Warning] Using a password on the command line interface can be insecure.
version()
8.0.11
ERROR 1064 (42000) at line 2: 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 'errorcommand' at line 1
Greetings
Hello LiuMiao
Result
42
liumiaocn:~ liumiao$
总结
MySQL缺省情况下多行语句执行,碰到错误会立即停止,如果添上force选项则会报错之后继续执行。