try....catch 在 mysql 中进行交易？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30973002/
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
try....catch in mysql for transaction?
提问by Michael Phelps
How can I start a transaction when there is any error in the SQL statements the system will rollback the changes automatically?
当SQL语句出现错误时，系统会自动回滚更改，如何启动事务？
PHP + MySQL transactions examples
in PHP
在 PHP 中
try {
// First of all, let's begin a transaction
$db->beginTransaction();
// A set of queries; if one fails, an exception should be thrown
$db->query('first query');
$db->query('second query');
$db->query('third query');
// If we arrive here, it means that no exception was thrown
// i.e. no query has failed, and we can commit the transaction
$db->commit();
} catch (Exception $e) {
// An exception has been thrown
// We must rollback the transaction
$db->rollback();
}
How to repeat the logic without PHP,only MYSQL
如何在没有PHP，只有MYSQL的情况下重复逻辑
回答by Rakesh Soni
We can write the multiple queries into the MySQLprocedure/function and can maintain the transaction. Please refer the sample given below. Basically, you should declare an error handlerwhich will call rollback.
我们可以将多个查询写入MySQL过程/函数并可以维护事务。请参考下面给出的示例。基本上，您应该声明一个将调用回滚的错误处理程序。
PROCEDURE `myprocedure`()
BEGIN
.. Declare statements ..
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
.. set any flags etc eg. SET @flag = 0; ..
ROLLBACK;
END;
START TRANSACTION;
.. Query 1 ..
.. Query 2 ..
.. Query 3 ..
COMMIT;
.. eg. SET @flag = 1; ..
END
Please see the links below for more details
请参阅以下链接了解更多详情
MySQL : transaction within a stored procedure
回答by Dika Arta Karunia
This is my Last work related to transaction in SQL, maybe the code sample below can help you. The code was developed for MS SQL server. Note that you can not use it in MySQL servers, because MySQL does not have that functionality.
这是我在 SQL 中与事务相关的最后一项工作，也许下面的代码示例可以帮助您。该代码是为MS SQL 服务器开发的。请注意，您不能在 MySQL 服务器中使用它，因为 MySQL 没有该功能。
The Main idea is to place the main query (which can be more than one) inside "try" and "transaction" clauses, then if the query executes successfully, hence the query will be committed in the database, otherwise in case of failure, an error will be raised in the "catch" section before the transaction gets rollbacked entirely.
主要思想是将主要查询（可以是多个）放在“try”和“transaction”子句中，然后如果查询执行成功，则查询将在数据库中提交，否则在失败的情况下，在事务完全回滚之前，“catch”部分将引发错误。
BEGIN TRY
BEGIN TRANSACTION
--Insert Your Queries Here--
COMMIT
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
IF @@TRANCOUNT > 0
ROLLBACK
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
