问一下,关于Mysql事务问题,1,testproc 这个表,我已设置为InnoDB类型2.使用以下测试语句,竟然,第一条语句还是执行成功,数据表还是有一条记录start transaction; -- 整个存储过程指定为一个事务 INSERT INTO testproc VALUES (1, '22'); -- 语句1
INSERT INTO testproc VALUES (1, '33'); -- 语句2
ROLLBACK;
END;表中第一字段ID,是主键,不能重复的请问一下,各位,这到底是什么回事?
INSERT INTO testproc VALUES (1, '33'); -- 语句2
ROLLBACK;
END;表中第一字段ID,是主键,不能重复的请问一下,各位,这到底是什么回事?
BEGIN
declare exit handler for sqlexception ; rollback;
INSERT INTO testproc VALUES (1, '22'); -- 语句1
INSERT INTO testproc VALUES (1, '33'); -- 语句2
COMMIT;
end;
//
create PROCEDURE aa(SqlCMD1 varchar(8000),SqlCMD2 varchar(8000),SqlCMD3 varchar(8000))
begin
declare exit handler for sqlexception rollback;
start TRANSACTION;
PREPARE stmt_name FROM SqlCMD1;
EXECUTE stmt_name;
DEALLOCATE stmt_name;
PREPARE stmt_name FROM SqlCMD2;
EXECUTE stmt_name;
DEALLOCATE stmt_name;
PREPARE stmt_name FROM SqlCMD3;
EXECUTE stmt_name;
DEALLOCATE stmt_name;
COMMIT;
end
用 declare exit handler for sqlexception rollback; 捕获错误
mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> insert into test5 values(1);
Query OK, 1 row affected (0.00 sec)mysql> select * from test5;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)mysql> insert into test5 values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from test5;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.00 sec)mysql> select * from test5;
Empty set (0.01 sec)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)mysql>
sqlexception 这个变量怎样搞出来还是不懂啊,有没有人可以说清楚一点
sqlexception 泛指所有的错误。只要出现任何错误,都执行后面的语句:rollback