我试图捕获在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中回滚失败的查询？
发布评论