测试数据库表结构:
CREATE TABLE `my_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`age` tinyint(2) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
先自行插入几条数据 ,
测试savepoint代码:
第一次执行如下:
BEGIN;
UPDATE `my_user` SET `name`='abc1' WHERE id=1;
SAVEPOINT identifier;
UPDATE `my_user` SET `name`='abc2' WHERE id=1;
ROLLBACK TO identifier;
RELEASE SAVEPOINT identifier;
COMMIT;
执行结果:
第二次执行:
BEGIN;
SAVEPOINT identifier;
UPDATE `my_user` SET `name`='abc2' WHERE id=1;
ROLLBACK TO identifier;
RELEASE SAVEPOINT identifier;
UPDATE `my_user` SET `name`='abc3' WHERE id=1;
COMMIT;
执行结果:
第三次执行:
BEGIN;
SAVEPOINT identifier;
UPDATE `my_user` SET `name`='abc2' WHERE id=1;
ROLLBACK TO identifier;
RELEASE SAVEPOINT identifier;
COMMIT;
执行结果:
第三次没有发生变化,因为回滚至identifier保存点之前的数据了
sql变量赋值测试(与本章主题无关):
BEGIN;
SET @avgage:=(SELECT AVG(age) FROM `my_user` WHERE age>=7);
SELECT *,@avgage FROM `my_user` WHERE age>=@avgage;
COMMIT;
执行结果: