What is automatic commit in MySQL and how can it be controlled and managed?

In MySQL, auto-commit means that after each SQL statement finishes executing, it is immediately committed to the database, even if it’s still within the same transaction. This implies that each SQL statement takes effect immediately and you don’t need to manually execute a COMMIT statement to commit the transaction.

To control and manage automatic submissions, you can do so in the following ways:

1. With the SET statement, you can temporarily change the setting of auto-commit, for example, you can use the following statement to disable auto-commit.

SET autocommit = 0;

Alternatively, you can enable automatic submission.

SET autocommit = 1;

1. You can control the submission of a transaction explicitly by using START TRANSACTION and COMMIT statements at the beginning and end of the transaction.

START TRANSACTION;
-- 执行一系列SQL语句
COMMIT;

1. Edit the MySQL configuration file: You can permanently change the settings for auto commit by editing the MySQL configuration file (such as my.cnf). Locate the following parameters to make changes.

autocommit = 1;  // 打开自动提交
或
autocommit = 0;  // 关闭自动提交

The above methods allow for easy control and management of automatic submission behavior in MySQL, allowing for flexible adjustment of transaction submission based on business needs.