如何在mysql中回滚失败的查询？ [英] How to rollback a failed query in 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.
推荐答案
- 此程序用于输入sql命令
DROP PROCEDURE IF EXISTS` procedure`
-- This procedure is used to enter the sql commands DROP PROCEDURE IF EXISTS `procedure`
CREATE PROCEDURE`procedation`（）
BEGIN
INSERT INTO`mschema``table1`
（`maxbudget`，
`closed`，
`d_percentage`，
`max discount`）
VALUES
（'2250'，
'0'，
'。 9'，
'.99'）;
设置@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（））;
如果@ x = 0则
commit;
else
rollback;
结束如果;
else rollback;
结束如果;
END
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 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中回滚失败的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
