MyBatis一次执行多条sql语句，这可能吗? [英] MyBatis executing multiple sql statements in one go, is that possible?

问题描述

我想知道是否可以一次执行多个 sql 语句.例如，我想从多个表中删除行的场景，有没有一种方法可以做..

i was wondering if it is possible to execute multiple sql statements in 1 go. For example the scenario that i want to delete rows from multiple tables, is there a way i can do things like..

<delete id="delete" parameterType="String">
    DELETE FROM DUMMYTABLE_A where X=${value}
    DELETE FROM DUMMYTABLE_B where X=${value}
</delete>

推荐答案

是的，大多数数据库都允许这样做.通常你必须用一些东西来分隔你的 SQL 语句.在 PostGRES 和 MySQL 中，它是一个分号 (;).在 Microsoft SQL 服务器中，您应该使用关键字 GO.[ 2013 年 5 月更新:从 SQL Server 2012 开始，您可以并且应该使用分号来分隔您的语句.在 SQL Server 2012(即下一版本及更高版本)之后，这些将是强制性的.使用 GO 现在是在 SQL2012 及更高版本中不推荐使用的方式).]

Yes, most databases allow this. Usually you have to delimit your SQL statements with something. In PostGRES and MySQL it's a semicolon (;). In Microsoft SQL server you should use the keyword GO. [ May 2013 Update: As of SQL Server 2012, you can and should use semicolons to delimit your statements. After SQL Server 2012 (ie. the next version and beyond) these will be mandatory. Using GO is now the deprecated way to do things in SQL2012 and beyond). ]

MySQL/PostGRES 示例:

MySQL / PostGRES example:

 DELETE FROM DUMMYTABLE_A where X=${value};
 DELETE FROM DUMMYTABLE_B where X=${value};
 DELETE FROM DUMMYTABLE_C where X=${value};

MS-SQL 示例:

 DELETE FROM DUMMYTABLE_A where X=${value}
 GO
 DELETE FROM DUMMYTABLE_B where X=${value}
 GO
 DELETE FROM DUMMYTABLE_C where X=${value}

更好的数据库(即不是 MySQL)还将支持 BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN 的事务.使用事务，您实际上可以将所有语句批处理为一个原子操作，如果其中一部分失败，则所有三个语句都将回滚.有关更多信息，请参阅 http://www.sqlteam.com/article/introduction-to-transactions关于那些.

Better databases (ie. not MySQL) will also support transactions with BEGIN TRAN / COMMIT TRAN / ROLLBACK TRAN. Using transactions you can actually batch all the statements into one atomic operation, where if part of it failed, all three would be rolled back. See http://www.sqlteam.com/article/introduction-to-transactions for some more information about those.

最有可能的只是你的 SQL 语句之间的分号！

Most likely all you need is the semicolons between your SQL statements though!

这篇关于MyBatis一次执行多条sql语句，这可能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！