# 事務保存點 SAVEPOINT 語句可以在事務過程中標記一個“保存點”，事務可以選擇回滾到這個點。保存點是可選的，一個事務過程中也可以有多個保存點。 ## 示例：將一個事務回滾到一個保存點 下面示例展示了一個事務中包含多個 DML 語句和多個保存點，然后回滾到其中一個保存點，只丟棄了保存點后面的那部份修改。 * 查看表當前記錄 ~~~ obclient> select * from t_insert; +----+------+-------+---------------------+ | id | name | value | gmt_create | +----+------+-------+---------------------+ | 1 | CN | 10001 | 2020-04-03 16:05:45 | | 2 | US | 10002 | 2020-04-03 16:05:54 | | 3 | UK | 10003 | 2020-04-03 16:05:54 | +----+------+-------+---------------------+ 3 rows in set (0.00 sec) ~~~ * 開啟一個事務，設置多個保存點信息。 ~~~ obclient> set session autocommit=off; Query OK, 0 rows affected (0.00 sec) obclient> begin; Query OK, 0 rows affected (0.00 sec) obclient> insert into t_insert(id, name) values(6,'FR'); Query OK, 1 row affected (0.00 sec) obclient> savepoint fr; Query OK, 0 rows affected (0.00 sec) obclient> insert into t_insert(id, name) values(7,'RU'); Query OK, 1 row affected (0.00 sec) obclient> savepoint ru; Query OK, 0 rows affected (0.00 sec) obclient> insert into t_insert(id, name) values(8,'CA'); Query OK, 1 row affected (0.00 sec) obclient> savepoint ca; Query OK, 0 rows affected (0.00 sec) ~~~ * 當前會話能看到事務未提交的所有修改。 ~~~ obclient> select * from t_insert; +----+------+-------+---------------------+ | id | name | value | gmt_create | +----+------+-------+---------------------+ | 1 | CN | 10001 | 2020-04-03 16:05:45 | | 2 | US | 10002 | 2020-04-03 16:05:54 | | 3 | UK | 10003 | 2020-04-03 16:05:54 | | 6 | FR | NULL | 2020-04-03 16:26:22 | | 7 | RU | NULL | 2020-04-03 16:26:32 | | 8 | CA | NULL | 2020-04-03 16:26:42 | +----+------+-------+---------------------+ 6 rows in set (0.00 sec) ~~~ * 回滾事務到其中一個保存點。 ~~~ obclient> rollback to savepoint ru; Query OK, 0 rows affected (0.00 sec) obclient> select * from t_insert; +----+------+-------+---------------------+ | id | name | value | gmt_create | +----+------+-------+---------------------+ | 1 | CN | 10001 | 2020-04-03 16:05:45 | | 2 | US | 10002 | 2020-04-03 16:05:54 | | 3 | UK | 10003 | 2020-04-03 16:05:54 | | 6 | FR | NULL | 2020-04-03 16:26:22 | | 7 | RU | NULL | 2020-04-03 16:26:32 | +----+------+-------+---------------------+ 5 rows in set (0.01 sec) ~~~ * 提交事務，確認表最新修改包含保存點之前的修改。 ~~~ obclient> commit; Query OK, 0 rows affected (0.00 sec) obclient> select * from t_insert; +----+------+-------+---------------------+ | id | name | value | gmt_create | +----+------+-------+---------------------+ | 1 | CN | 10001 | 2020-04-03 16:05:45 | | 2 | US | 10002 | 2020-04-03 16:05:54 | | 3 | UK | 10003 | 2020-04-03 16:05:54 | | 6 | FR | NULL | 2020-04-03 16:26:22 | | 7 | RU | NULL | 2020-04-03 16:26:32 | +----+------+-------+---------------------+ 5 rows in set (0.00 sec) obclient> ~~~