mysql之commit，transaction事物控制 - 标配的小号

mysql之commit，transaction事物控制

簡單來說，transaction就是用來恢複為以前的資料。

舉個例子，我想把今天輸入到資料庫裡的資料在晚上的時候全部删除，那麼我們就可以在今天早上的時候開始transaction事物，令autocommit關閉并且執行commit，然後再開始輸入資料，到晚上的時候，可以執行rollback恢複到今天沒輸入資料的狀态，也就是恢複到commit前的資料。

[root@localhost ~]# mysql -uroot -p              #登入資料庫
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type \'help;\' or \'\h\' for help. Type \'\c\' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> create database bp                #我自己建立一個資料庫用來做這個實驗
    -> ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> 



MariaDB [(none)]> use bp;
Database changed
MariaDB [bp]> create table test(id int,name varchar(20));               #建表
Query OK, 0 rows affected (0.08 sec)

MariaDB [bp]> insert into test values(1,\'123\');                         
Query OK, 1 row affected (0.06 sec)

MariaDB [bp]> insert into test values(2,\'323\');
Query OK, 1 row affected (0.01 sec)

MariaDB [bp]> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | 123  |
|    2 | 323  |
+------+------+
2 rows in set (0.00 sec)

MariaDB [bp]> show variables like \'%commit%\';                       #檢視autocommit是否關閉，可以看到現在開啟着
+-------------------------------------------+-------+
| Variable_name                             | Value |
+-------------------------------------------+-------+
| aria_group_commit                         | none  |
| aria_group_commit_interval                | 0     |
| autocommit                                | ON    |
| innodb_commit_concurrency                 | 0     |
| innodb_flush_log_at_trx_commit            | 1     |
| innodb_use_global_flush_log_at_trx_commit | ON    |
+-------------------------------------------+-------+
6 rows in set (0.00 sec)

MariaDB [bp]> set autocommit=0;                             #關閉autocommit
Query OK, 0 rows affected (0.00 sec)

MariaDB [bp]> show variables like \'%commit%\';
+-------------------------------------------+-------+
| Variable_name                             | Value |
+-------------------------------------------+-------+
| aria_group_commit                         | none  |
| aria_group_commit_interval                | 0     |
| autocommit                                | OFF   |
| innodb_commit_concurrency                 | 0     |
| innodb_flush_log_at_trx_commit            | 1     |
| innodb_use_global_flush_log_at_trx_commit | ON    |
+-------------------------------------------+-------+
6 rows in set (0.00 sec)

MariaDB [bp]> start transaction;                            #開始事物
Query OK, 0 rows affected (0.00 sec)

MariaDB [bp]> delete from test where id=1;
Query OK, 1 row affected (0.00 sec)

MariaDB [bp]> select * from test;
+------+------+
| id   | name |
+------+------+
|    2 | 323  |
+------+------+
1 row in set (0.00 sec)

MariaDB [bp]> commit;                                   #記錄前面的資料
Query OK, 0 rows affected (0.01 sec)

MariaDB [bp]> delete from test where id=2;              #删除資料
Query OK, 1 row affected (0.00 sec)

MariaDB [bp]> select * from test;
Empty set (0.00 sec)

MariaDB [bp]> rollback;                                 #復原到commit記錄的資料
Query OK, 0 rows affected (0.00 sec)

MariaDB [bp]> select * from test;                       #復原成功
+------+------+
| id   | name |
+------+------+
|    2 | 323  |
+------+------+
1 row in set (0.00 sec)

MariaDB [bp]>