事务控制出问题了,ROW_COUNT()会出问题
czb 2015-11-11 05:59:33 DROP TABLE IF EXISTS t_test001;
DROP TABLE IF EXISTS t_test002;
DROP TABLE IF EXISTS t_test003;
CREATE TABLE t_test001(`id` INT UNSIGNED,`name` VARCHAR(8),`value` INT);
CREATE TABLE t_test002(`id` INT UNSIGNED,`name` VARCHAR(6),`value` INT);
INSERT INTO t_test001(`id`,`name`,`value`) VALUES(1,'name1',100);
INSERT INTO t_test001(`id`,`name`,`value`) VALUES(2,'name1',120);
INSERT INTO t_test001(`id`,`name`,`value`) VALUES(3,'name1',130);
INSERT INTO t_test002(`id`,`name`,`value`) VALUES(1,'name1',200);
INSERT INTO t_test002(`id`,`name`,`value`) VALUES(2,'name1',220);
INSERT INTO t_test002(`id`,`name`,`value`) VALUES(3,'name1',230);
DROP PROCEDURE IF EXISTS `p_test`;
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `p_test`(
p_id INT UNSIGNED,
p_value INT UNSIGNED,
OUT p_result TINYINT -- 0 表示没错误,-1表示
)
label_flag:
BEGIN
DECLARE l_isExists INT;
DECLARE l_is_error INTEGER DEFAULT 0;
DECLARE l_value INT;
DECLARE l_count INT;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET l_is_error=1;
SET @@autocommit = 0;
SET SESSION sql_mode= 'TRADITIONAL';
START TRANSACTION;
UPDATE t_test002 SET `value`=`value`+p_value WHERE id = p_id;
SET l_count = ROW_COUNT();
IF(l_count<>1)THEN
SET p_result = -1;
ROLLBACK;
SET @@autocommit = 1;
LEAVE label_flag;
END IF;
UPDATE t_test001 SET `value`=`value`-p_value WHERE id = p_id;
SET l_count = ROW_COUNT();
IF(l_count<>1)THEN
SET p_result = -2;
ROLLBACK;
SET @@autocommit = 1;
LEAVE label_flag;
END IF;
SELECT `value` INTO l_value FROM t_test001 WHERE id = p_id;
IF(l_value IS NULL)THEN
SET p_result = -3;
ROLLBACK;
SET @@autocommit = 1;
LEAVE label_flag;
END IF;
IF(l_value <0 )THEN
SET p_result = -4;
ROLLBACK;
SET @@autocommit = 1;
LEAVE label_flag;
END IF;
COMMIT;
SET @@autocommit = 1;
SET p_result = 0;
END$$
DELIMITER ;
上面的表和存储过程都都建好,执行mysql> CALL `p_test`(1,60,@p_result);
第一次执行的时候,没有问题,事务可以正常结束。
第二次和以后的执行 ,@p_result总是等于-2,即下面的语句处出了问题
UPDATE t_test001 SET `value`=`value`-p_value WHERE id = p_id;
SET l_count = ROW_COUNT();
l_count的值总为-1,而不是理想值1,所以走进去了if语句块。
操作系统为64位win8,mysql的版本为
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.22 |
+-----------+
1 row in set (0.00 sec)
到底哪里出了问题啊,请各位专家指导下。