MySQL Department rollback

We can define the SAVEPOINT during mysql transaction processing, and then roll back to the status before the specified save point. The syntax for defining the save point and rolling back to the status before the specified save point is as follows. Define the save point --- SAVEPOINT to save the name; roll back to the specified save point --- ROLLBACKTOSAVEPOINT to save the name:

We can define the SAVEPOINT during mysql transaction processing, and then roll back to the status before the specified save point. The syntax for defining the save point and rolling back to the status before the specified save point is as follows. Define the save point --- savepoint to save the name; roll back TO the specified save point --- rollback to savepoint to save the name: The following shows how TO save the name TO the table user

We can define the SAVEPOINT during mysql transaction processing, and then roll back to the status before the specified save point.

The syntax for defining the save point and rolling back to the status before the specified save point is as follows.

1. Define save point --- SAVEPOINT save name;
2. Roll back TO the specified storage point --- rollback to savepoint:

The following example inserts three data records into the table user, defines a save point after inserting 2nd data records, and finally checks whether the data can be rolled back to this save point.

1. View data in the user table

1. Mysql> select * from user;
2. + ----- + ---------- + ----- + ------ +
3. | Mid | name | scx | word |
4. + ----- + ---------- + ----- + ------ +
5. | 1 | zhangsan | 0 | NULL |
6. | 2 | wangwu | 1 | NULL |
7. + ----- + ---------- + ----- + ------ +
8. 2 rows in set (0.05 sec)

2. Start mysql transaction

1. Mysql> BEGIN;
2. Query OK, 0 rows affected (0.00 sec)

3. Insert two data entries to the table user

1. Mysql> insert into user VALUES ('3', 'one', '0 ','');
2. Query OK, 1 row affected (0.08 sec)
3. Mysql> insert into user VALUES ('4, 'two', '0 ','');
4. Query OK, 1 row affected (0.00 sec)
5. Mysql> select * from user;
6. + ----- + ---------- + ----- + ------ +
7. | Mid | name | scx | word |
8. + ----- + ---------- + ----- + ------ +
9. | 1 | zhangsan | 0 | NULL |
10. | 2 | wangwu | 1 | NULL |
11. | 3 | one | 0 |
12. | 4 | two | 0 |
13. + ----- + ---------- + ----- + ------ +
14. 4 rows in set (0.00 sec)

4. Specify the storage point and set the name to test.

1. Mysql> SAVEPOINT test;
2. Query OK, 0 rows affected (0.00 sec)

5. Insert 3rd data records into the table user

1. Mysql> insert into user VALUES ('5', 'three ', '0 ','');
2. Query OK, 1 row affected (0.00 sec)
3. Mysql> select * from user;
4. + ----- + ---------- + ----- + ------ +
5. | Mid | name | scx | word |
6. + ----- + ---------- + ----- + ------ +
7. | 1 | zhangsan | 0 | NULL |
8. | 2 | wangwu | 1 | NULL |
9. | 3 | one | 0 |
10. | 4 | two | 0 |
11. | 5 | three | 0 |
12. + ----- + ---------- + ----- + ------ +
13. 5 rows in set (0.02 sec)

6. Roll Back to the Save point test

1. Mysql> rollback to savepoint test;
2. Query OK, 0 rows affected (0.31 sec)
3. Mysql> select * from user;
4. + ----- + ---------- + ----- + ------ +
5. | Mid | name | scx | word |
6. + ----- + ---------- + ----- + ------ +
7. | 1 | zhangsan | 0 | NULL |
8. | 2 | wangwu | 1 | NULL |
9. | 3 | one | 0 |
10. | 4 | two | 0 |
11. + ----- + ---------- + ----- + ------ +
12. 4 rows in set (0.00 sec)

We can see that the records inserted after the save point test are not displayed, that is, the group is successfully rolled to the State before the definition save point test. You can use the save point to submit only partial transaction processing.