Question

mysql 存储过程中update影响行数为0，回滚

请高手指教一个问题，在mysql的存储过程中update更新影响行数为0,怎么获取到这个0，并回滚！
谁帮忙指教一下，急求

-- 开始事务
start transaction;
call setCoin(zjAmount, 0, `uid`, liqType, `type`, info, _betId, serializeId, '');
update blast_bets set lotteryNo=_kjData, zjCount=_zjCount, bonus=zjAmount, fanDianAmount=_fanDianAmountSelf where id=_betId;
-- 提交事务
commit;

Answer 1

使用FOUND_ROWS() 获得影响的行数，再用IF判断是否等于0就行了。

-- 开始事务
start transaction;

call setCoin(zjAmount, 0, `uid`, liqType, `type`, info, _betId, serializeId, '');
update blast_bets set lotteryNo=_kjData, zjCount=_zjCount, bonus=zjAmount, fanDianAmount=_fanDianAmountSelf where id=_betId;
IF FOUND_ROWS() = 0 THEN  
   ROLLBACK;  
ELSE  
   COMMIT;  
END IF;      
commit;

Answer 2

在存储过程中使用ROW_COUNT()
mysql> select * from a1;
-> //
+------+-------+
| id | name |
+------+-------+
| 1 | timac |
+------+-------+
1 row in set (0.00 sec)
mysql> \d ;
mysql> update a1 set name='king' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> insert into a1 values(2,'K1'),(3,'K2');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)

mysql> update a1 set name='kings' where id=4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)

只有返回的row_count()值大小还判断是否成功