mysql savepoint 示例

最新推荐文章于 2024-06-20 16:01:27 发布

原创最新推荐文章于 2024-06-20 16:01:27 发布 · 9k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #books #warnings #query #sql

杂七杂八专栏收录该内容

68 篇文章

订阅专栏

本文演示了MySQL中如何使用事务进行数据更新操作，并通过SAVEPOINT和ROLLBACK实现部分或全部更改的撤销，展示了如何在数据库操作中有效利用事务确保数据一致性。

SET SQL_SAFE_UPDATES=0;
update books set free =1, new=1;
commit;
SET AUTOCOMMIT=1;
begin; 
select free, new from books limit 1;
update books set free = 0;
select free, new from books limit 1;
SAVEPOINT book1;
update books set new = 0;
select free, new from books limit 1;
rollback to book1;
select free, new from books limit 1;
rollback;
select free, new from books limit 1;

mysql> update books set free =1, new=1;
Query OK, 0 rows affected (0.06 sec)
Rows matched: 79  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> SET AUTOCOMMIT=1;
Query OK, 0 rows affected (0.00 sec)

mysql> begin; 
Query OK, 0 rows affected (0.00 sec)

mysql> select free, new from books limit 1;
+------+-----+
| free | new |
+------+-----+
|    1 |   1 |
+------+-----+
1 row in set (0.00 sec)

mysql> update books set free = 0;
Query OK, 79 rows affected (0.01 sec)
Rows matched: 79  Changed: 79  Warnings: 0

mysql> select free, new from books limit 1;
+------+-----+
| free | new |
+------+-----+
|    0 |   1 |
+------+-----+
1 row in set (0.00 sec)

mysql> SAVEPOINT book1;
Query OK, 0 rows affected (0.00 sec)

mysql> update books set new = 0;
Query OK, 79 rows affected (0.00 sec)
Rows matched: 79  Changed: 79  Warnings: 0

mysql> select free, new from books limit 1;
+------+-----+
| free | new |
+------+-----+
|    0 |   0 |
+------+-----+
1 row in set (0.00 sec)

mysql> rollback to book1;
Query OK, 0 rows affected (0.00 sec)

mysql> select free, new from books limit 1;
+------+-----+
| free | new |
+------+-----+
|    0 |   1 |
+------+-----+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.05 sec)

mysql> select free, new from books limit 1;
+------+-----+
| free | new |
+------+-----+
|    1 |   1 |
+------+-----+
1 row in set (0.00 sec)