Windows下关闭MySQL的自动提交(autocommit)功能

• 一种是通过set命令修改会话级别或者数据库级别的参数，但是数据库重启后参数会恢复默认值；
• 第二种方法是修改mysql的配置文件my.ini，一劳永逸。

1.测试环境 Windows Server 2008 r2+MySQL Community Server (GPL) 5.7.16

点击(此处)折叠或打开

1. mysql> status
   
2. --------------
   
3. mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64)
   
4. 
   
5. Connection id: 2
   
6. Current database:
   
7. Current user: root@localhost
   
8. SSL: Not in use
   
9. Using delimiter: ;
   
10. Server version: 5.7.16 MySQL Community Server (GPL)
    
11. Protocol version: 10
    
12. Connection: localhost via TCP/IP
    
13. Server characterset: latin1
    
14. Db characterset: latin1
    
15. Client characterset: utf8
    
16. Conn. characterset: utf8
    
17. TCP port: 3306
    
18. Uptime: 9 min 5 sec
    
19. 
    
20. Threads: 1 Questions: 7 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.012
    
21. --------------
    
22. 
    
23. mysql>

2.通过set来关闭autocommit，重启后恢复默认值

首先，我们通过set命令来修改autocommit参数。

点击(此处)折叠或打开

1. mysql>
   
2. mysql> show global variables like '%commit%';
   
3. +-----------------------------------------+-------+
   
4. | Variable_name | Value |
   
5. +-----------------------------------------+-------+
   
6. | autocommit | ON |
   
7. | binlog_group_commit_sync_delay | 0 |
   
8. | binlog_group_commit_sync_no_delay_count | 0 |
   
9. | binlog_order_commits | ON |
   
10. | innodb_api_bk_commit_interval | 5 |
    
11. | innodb_commit_concurrency | 0 |
    
12. | innodb_flush_log_at_trx_commit | 1 |
    
13. | slave_preserve_commit_order | OFF |
    
14. +-----------------------------------------+-------+
    
15. 8 rows in set, 1 warning (0.00 sec)
    
16. 
    
17. mysql> set autocommit=0;
    
18. Query OK, 0 rows affected (0.00 sec)
    
19. mysql>
    
20. mysql> set global autocommit=0;
    
21. Query OK, 0 rows affected (0.00 sec)
    
22. 
    
23. mysql> show global variables like '%commit%';
    
24. +-----------------------------------------+-------+
    
25. | Variable_name | Value |
    
26. +-----------------------------------------+-------+
    
27. | autocommit | OFF |
    
28. | binlog_group_commit_sync_delay | 0 |
    
29. | binlog_group_commit_sync_no_delay_count | 0 |
    
30. | binlog_order_commits | ON |
    
31. | innodb_api_bk_commit_interval | 5 |
    
32. | innodb_commit_concurrency | 0 |
    
33. | innodb_flush_log_at_trx_commit | 1 |
    
34. | slave_preserve_commit_order | OFF |
    
35. +-----------------------------------------+-------+
    
36. 8 rows in set, 1 warning (0.02 sec)
    
37. 
    
38. mysql>

接下来，我们重启数据库，发现autocommit参数已经恢复默认值。

点击(此处)折叠或打开

1. E:\mysql-5.7.16-winx64\bin>net stop mysql
   
2. MySQL 服务正在停止.
   
3. MySQL 服务已成功停止。
   
4. 
   
5. 
   
6. E:\mysql-5.7.16-winx64\bin>net start mysql
   
7. MySQL 服务正在启动 .
   
8. MySQL 服务已经启动成功。
   
9. 
   
10. 
    
11. E:\mysql-5.7.16-winx64\bin>mysql -u root -proot
    
12. mysql: [Warning] Using a password on the command line interface can be insecure.
    
13. Welcome to the MySQL monitor. Commands end with ; or \g.
    
14. Your MySQL connection id is 2
    
15. Server version: 5.7.16 MySQL Community Server (GPL)
    
16. 
    
17. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
    
18. 
    
19. Oracle is a registered trademark of Oracle Corporation and/or its
    
20. affiliates. Other names may be trademarks of their respective
    
21. owners.
    
22. 
    
23. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
24. 
    
25. mysql>
26. mysql> show global variables like '%commit%';
    
27. +-----------------------------------------+-------+
    
28. | Variable_name | Value |
    
29. +-----------------------------------------+-------+
    
30. | autocommit | ON |
    
31. | binlog_group_commit_sync_delay | 0 |
    
32. | binlog_group_commit_sync_no_delay_count | 0 |
    
33. | binlog_order_commits | ON |
    
34. | innodb_api_bk_commit_interval | 5 |
    
35. | innodb_commit_concurrency | 0 |
    
36. | innodb_flush_log_at_trx_commit | 1 |
    
37. | slave_preserve_commit_order | OFF |
    
38. +-----------------------------------------+-------+
    
39. 8 rows in set, 1 warning (0.00 sec)
    
40. 
    
41. mysql>

3.修改mysql的配置文件my.ini

我们找到mysql的配置文件my.ini，在里面添加一行记录"autocommit=0"。

点击(此处)折叠或打开

1. [mysql]
   
2. default-character-set=utf8
   
3. [mysqld]
   
4. max_connections=200
   
5. default-storage-engine=INNODB
   
6. basedir =E:\mysql-5.7.16-winx64\bin
   
7. datadir =E:\mysql-5.7.16-winx64\data
   
8. port = 3306
   
9. autocommit=0

然后重新启动数据库，确认autocommit参数是否为OFF。

点击(此处)折叠或打开

1. E:\mysql-5.7.16-winx64\bin>net stop mysql
   
2. MySQL 服务正在停止.
   
3. MySQL 服务已成功停止。
   
4. 
   
5. 
   
6. E:\mysql-5.7.16-winx64\bin>net start mysql
   
7. MySQL 服务正在启动 .
   
8. MySQL 服务已经启动成功。
   
9. 
   
10. 
    
11. E:\mysql-5.7.16-winx64\bin>mysql -u root -proot
    
12. mysql: [Warning] Using a password on the command line interface can be insecure.
    
13. Welcome to the MySQL monitor. Commands end with ; or \g.
    
14. Your MySQL connection id is 2
    
15. Server version: 5.7.16 MySQL Community Server (GPL)
    
16. 
    
17. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
    
18. 
    
19. Oracle is a registered trademark of Oracle Corporation and/or its
    
20. affiliates. Other names may be trademarks of their respective
    
21. owners.
    
22. 
    
23. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
24. 
    
25. mysql> show global variables like '%commit%';
    
26. +-----------------------------------------+-------+
    
27. | Variable_name | Value |
    
28. +-----------------------------------------+-------+
    
29. | autocommit | OFF |
    
30. | binlog_group_commit_sync_delay | 0 |
    
31. | binlog_group_commit_sync_no_delay_count | 0 |
    
32. | binlog_order_commits | ON |
    
33. | innodb_api_bk_commit_interval | 5 |
    
34. | innodb_commit_concurrency | 0 |
    
35. | innodb_flush_log_at_trx_commit | 1 |
    
36. | slave_preserve_commit_order | OFF |
    
37. +-----------------------------------------+-------+
    
38. 8 rows in set, 1 warning (0.01 sec)
    
39. 
    
40. mysql>
    
41. mysql>

4.数据插入速度提升十倍