如何在mysql中回滚失败的查询？

问题描述

我试图捕获在2个不同的表中插入2条记录时发生的错误。如果第二个查询失败，我想回滚已经存储的查询。



我尝试过：



我已经搜索了解决方案，我决定在程序中使用if编码来使用

I am trying to catch the error occurred while inserting 2 records in 2 different tables. I want to rollback the already stored queries if the second query failed.

What I have tried:

I have searched for the solution , and i decided to use an if codition inside a procedure to check the number of errors using

@@error_count

检查错误的数量，这是我的代码：

, here is my code:

DELIMITER $$

-- This procedure is used to enter the sql commands

DROP PROCEDURE IF EXISTS `procedure` $$
CREATE PROCEDURE `procedure`()
BEGIN
INSERT INTO `mschema`.`table1`
(`maxbudget`,
`blocked`,
`d_percentage`,
`max discount`)
VALUES
('2250',
'0',
'.9',
'.99');
set @x = @@error_count;
if @x= 0 then
	INSERT INTO `mschema`.`table2`
	 (`name`,`image`,`date`,`fKey_id`)
	 values
         ('jhon','jfdd', '2018-01-01 00:00:00', LAST_INSERT_ID());
      if @x= 0 then
	commit;
      else
	rollback;
      end if;
else rollback;
end if;

END $$

请注意

Note that

firstdate

表2中的值不重复。

当我执行它时，它成功执行并在MySQL工作台中给出结果如下：

1. - 此程序是用于输入sql命令DROP PROCEDURE如果EXISTS`过程`



2.第二个结果给了我第二次插入查询到表2我试图执行。

所有reult都有

value is not repetitive in table 2.
when i execute this, it executed successfully and gives me the result in MySQL workbench as follow:
1. -- This procedure is used to enter the sql commands DROP PROCEDURE IF EXISTS `procedure`

2. The second result gave me the second insertion query into table 2 i am trying to execute.
all reult has

0 row(s) affected.

说明插入的值也很重要2个表格不重复。所以我不知道出了什么问题。

It is also important to say that the inserted values in the 2 tables are not repeated. So i do not know what is wrong.

推荐答案

注意

Note that

firstdate

值在表2中没有重复。

当我执行它时，它成功执行并在MySQL工作台中给我结果如下：

1. - 此过程用于输入sql命令DROP PROCEDURE IF EXISTS`procedation`



2.第二个结果给了我第二个插入查询表2我正在尝试执行。

所有reult都有

value is not repetitive in table 2.
when i execute this, it executed successfully and gives me the result in MySQL workbench as follow:
1. -- This procedure is used to enter the sql commands DROP PROCEDURE IF EXISTS `procedure`

2. The second result gave me the second insertion query into table 2 i am trying to execute.
all reult has

0 row(s) affected.

它也是重要的是，不重复2个表中的插入值。所以我不知道出了什么问题。

It is also important to say that the inserted values in the 2 tables are not repeated. So i do not know what is wrong.

这篇关于如何在mysql中回滚失败的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！