【整理】MySQL之autocommit(四)
Your MySQL connection id is 3
09
Server version: 5.6.10-log Source distribution
10
11
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
12
13
Oracle is a registered trademark of Oracle Corporation and/or its
14
affiliates. Other names may be trademarks of their respective
15
owners.
16
17
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
18
19
mysql>
20
mysql> use test;
21
Reading table information for completion of table and column names
22
You can turn off this feature to get a quicker startup with -A
23
24
Database changed
25
mysql>
26
mysql> show tables;
27
+----------------+
28
| Tables_in_test |
29
+----------------+
30
| t_autocommit |
31
+----------------+
32
1 row in set (0.00 sec)
33
34
mysql>
35
mysql> select * from t_autocommit;
36
Empty set (0.00 sec)
37
38
mysql>
发现什么数据都没有。为什么呢？因为 SQL 语句并没有被自己（当前 session）提交给 server 端去处理，只是在当前连接中做了相应处理。
重复上面的实验，但是保持 autocommit 的默认值（1）。
001
mysql>
002
mysql> show binlog events;
003
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
004
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
005
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
006
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.10-log, Binlog ver: 4 |
007
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
008
1 row in set (0.00 sec)
009
010
mysql>
011
mysql> show tables;
012
Empty set (0.01 sec)
013
014
mysql>
015
mysql> select @@global.autocommit;
016
+---------------------+
017
| @@global.autocommit |
018
+---------------------+
019
| 1 |
020
+---------------------+
021
1 row in set (0.00 sec)
022
023
mysql>
024
mysql> select @@session.autocommit;
025
+----------------------+
026
| @@session.autocommit |
027
+----------------------+
028
| 1 |
029
+----------------------+
030
1 row in set (0.00 sec)
031
032
mysql>
033
mysql> create table t_autocommit(
034
-> id int not null auto_increment,
035
-> amount int not null default '0',
036
-> primary key(id)
037
-> )engine=innodb;
038
Query OK, 0 rows affected (0.01 sec)
039
040
mysql>
041
mysql> show tables;
042
+----------------+
043
| Tables_in_test |
044
+----------------+
045
| t_autocommit |
046
+----------------+
047
1 row in set (0.00 sec)
048
049
mysql>
050
mysql> describe t_autocommit;
051
+--------+---------+------+-----+---------+----------------+
052
| Field | Type | Null | Key | Default | Extra |
053
+--------+---------+------+-----+---------+----------------+
054
| id | int(11) | NO | PRI | NULL | auto_increment |
055
| amount | int(11) | NO | | 0 | |
056
+--------+---------+------+-----+---------+----------------+
057
2 rows in set (0.00 sec)
058
059
mysql>
060
mysql> insert into t_autocommit set amount=1;
061
Query OK, 1 row affected (0.00 sec)
062
063
mysql>
064
mysql> select * from t_autocommit;
065
+----+--------+
066
| id | amount |
067
+----+--------+
068
| 1 | 1 |