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 ServerCopyright (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]>