【整理】MySQL之autocommit(二)
1
+---------------------+
42
1 row in set (0.00 sec)
43
44
mysql> select @@session.autocommit;
45
+----------------------+
46
| @@session.autocommit |
47
+----------------------+
48
| 0 |
49
+----------------------+
50
1 row in set (0.00 sec)
51
52
mysql>
b. 创建一个测试表
01
mysql> create table t_autocommit(
02
-> id int not null auto_increment,
03
-> amount int not null default '0',
04
-> primary key(id)
05
-> )engine=innodb;
06
Query OK, 0 rows affected (0.01 sec)
07
08
mysql>
09
mysql> show tables;
10
+----------------+
11
| Tables_in_test |
12
+----------------+
13
| t_autocommit |
14
+----------------+
15
1 row in set (0.00 sec)
16
17
mysql>
18
mysql> describe t_autocommit;
19
+--------+---------+------+-----+---------+----------------+
20
| Field | Type | Null | Key | Default | Extra |
21
+--------+---------+------+-----+---------+----------------+
22
| id | int(11) | NO | PRI | NULL | auto_increment |
23
| amount | int(11) | NO | | 0 | |
24
+--------+---------+------+-----+---------+----------------+
25
2 rows in set (0.00 sec)
26
27
mysql>
28
mysql> select * from t_autocommit;
29
Empty set (0.00 sec)
30
31
mysql>
c. 插入数据
01
mysql>
02
mysql> insert into t_autocommit set amount=1;
03
Query OK, 1 row affected (0.00 sec)
04
05
mysql>
06
mysql> select * from t_autocommit;
07
+----+--------+
08
| id | amount |
09
+----+--------+
10
| 1 | 1 |
11
+----+--------+
12
1 row in set (0.00 sec)
13
14
mysql>
15
mysql> update t_autocommit set amount=amount+10;
16
Query OK, 1 row affected (0.00 sec)
17
Rows matched: 1 Changed: 1 Warnings: 0
18
19
mysql>
20
mysql> select * from t_autocommit;
21
+----+--------+
22
| id | amount |
23
+----+--------+
24
| 1 | 11 |
25
+----+--------+
26
1 row in set (0.00 sec)
27
28
mysql>
29
mysql> show binlog events;
30
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+
31
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
32
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+
33
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.10-log, Binlog ver: 4 |
34
| mysql-bin.000001 | 120 | Query | 1 | 316 | use `test`; create table t_autocommit(
35
id int not null auto_increment,
36
amount int not null default '0',
37
primary key(id)
38
)engine=innodb |
39
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------+
40
2 rows in set (0.00 sec)
41
42
mysql>
发现 binlog 中仅记录了 create table 动作，insert 和 update 由于 autocommit 为 0 的缘故没有被记录到 binlog 中。
d.断开 mysql ，再重新连接。
01
mysql>
02
mysql> quit
03
Bye
04
[root@Betty ~]#
05
[root@Betty ~]# mysql -u root -p
06
Enter password:
07
Welcome to the MySQL monitor. Commands end with ; or \g.
08