前提:在数据库连接文件中打开批量操作,否则报错 BadSqlGrammarException
- 开启批量操作语句
allowMultiQueries = true
- 开启批量操作案例
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db.nuc?useSSL=true&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
jdbc.username=root
jdbc.password=root
(1)场景一:数据表之间不存在外键联系下同时删除多表数据
- 语法:
delete 数据表一,数据表二 from 数据表一,数据表二 where 条件
- 案例:
delete student,studentquery
from student,studentquery
where student.studentid = 1813004 AND studentquery.studentid=1813004;
(2)场景二:数据表之间存在外键联系下同时删除多表数据
- 语法:
// 关闭数据库外键约束,否则无法删除
set foreign_key_checks = 0;
delete 数据表一,数据表二 from 数据表一,数据表二 where 条件
// 开启数据库外键约束
set foreign_key_checks = 1
- 案例:
set foreign_key_checks = 0;
delete student,studentquery
from student,studentquery
where student.studentid = studentquery.studentid AND student.studentid=1813004;
set foreign_key_checks = 1