如何关闭 MySQL 客户端的自动提交？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2280465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I turn off autocommit for a MySQL client?
提问by Michael Hinds
I have a web app that has been written with the assumption that autocommit is turned on on the database, so I don't want to make any changes there. However all the documentation I can find only seems to talk about using init_connect on the database, i.e. a global setting for all client connections.
我有一个假设已在数据库上打开自动提交的 Web 应用程序，因此我不想在那里进行任何更改。然而，我能找到的所有文档似乎只讨论在数据库上使用 init_connect，即所有客户端连接的全局设置。
Is there a way to set autocommit=0 just when running mysql on a Linux command line (without having to type it in every time)?
有没有办法在 Linux 命令行上运行 mysql 时设置 autocommit=0 （不必每次都输入）？
采纳答案by DeveloperChris
Perhaps the best way is to write a script that starts the mysql command line client and then automatically runs whatever sql you want before it hands over the control to you.
也许最好的方法是编写一个脚本来启动 mysql 命令行客户端，然后在将控制权交给您之前自动运行您想要的任何 sql。
linux comes with an application called 'expect'. it interacts with the shell in such a way as to mimic your key strokes. it can be set to start mysql, wait for you to enter your password. run further commands such as SET autocommit = 0;then go into interactive mode so you can run any command you want.
linux 带有一个名为“expect”的应用程序。它以模仿您的击键方式的方式与外壳交互。可以设置启动mysql，等你输入密码。运行更多命令，例如SET autocommit = 0;然后进入交互模式，以便您可以运行任何您想要的命令。
for more on the command SET autocommit = 0;see.. http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html
有关命令的更多信息，SET autocommit = 0;请参阅.. http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html
I use expect to log in to a command line utility in my case it starts ssh, connects to the remote server, starts the application enters my username and password then turns over control to me. saves me heaps of typing :)
我使用 expect 登录到命令行实用程序，在我的情况下，它启动 ssh，连接到远程服务器，启动应用程序，输入我的用户名和密码，然后将控制权交给我。为我节省了大量的打字时间:)
http://linux.die.net/man/1/expect
http://linux.die.net/man/1/expect
DC
直流电
Expect script provided by Michael Hinds
期待脚本由 Michael Hinds 提供
spawn /usr/local/mysql/bin/mysql
expect "mysql>"
send "set autocommit=0;\r"
expect "mysql>" interact
expect is pretty powerful and can make life a lot easier as in this case.
expect 非常强大，可以让生活变得更轻松，就像在这种情况下一样。
if you want to make the script run without calling expect use the shebang line
如果你想让脚本在不调用 expect 的情况下运行，请使用 shebang 行
insert this as the first line in your script (hint: use which expectto find the location of your expect executable)
将此作为脚本的第一行插入（提示：用于which expect查找您期望的可执行文件的位置）
#! /usr/bin/expect
then change the permissions of your script with..
然后使用..更改脚本的权限
chmod 0744 myscript
then call the script
然后调用脚本
./myscript
DC
直流电
回答by nanda
You do this in 3 different ways:
您可以通过 3 种不同的方式执行此操作：
Before you do an
INSERT, always issue aBEGIN;statement. This will turn off autocommits. You will need to do aCOMMIT;once you want your data to be persisted in the database.Use
autocommit=0;every time you instantiate a database connection.For a global setting, add a
autocommit=0variable in yourmy.cnfconfiguration file in MySQL.
在你做一个之前
INSERT，总是发表一个BEGIN;声明。这将关闭自动提交。COMMIT;一旦您希望将数据保存在数据库中，您将需要执行一次。使用
autocommit=0;每次实例化一个数据库连接时间。对于全局设置，
autocommit=0请my.cnf在 MySQL的配置文件中添加一个变量。
回答by rascalking
It looks like you can add it to your ~/.my.cnf, but it needs to be added as an argument to the init-command flag in your [client] section, like so:
看起来您可以将其添加到 ~/.my.cnf 中，但需要将其作为参数添加到 [client] 部分中的 init-command 标志中，如下所示：
[client]
init-command='set autocommit=0'
回答by e4c5
Do you mean the mysql text console? Then:
你的意思是mysql文本控制台？然后：
START TRANSACTION;
...
your queries.
...
COMMIT;
Is what I recommend.
是我推荐的。
However if you want to avoid typing this each time you need to run this sort of query, add the following to the [mysqld] section of your my.cnf file.
但是，如果您想避免每次需要运行此类查询时都键入此内容，请将以下内容添加到 my.cnf 文件的 [mysqld] 部分。
init_connect='set autocommit=0'
This would set autocommitto be off for every client though.
不过，这autocommit对每个客户都将关闭。
回答by PodTech.io
This is useful to check the status of autocommit;
这对于检查自动提交的状态很有用；
select @@autocommit;
回答by Ram
For auto commit off then use the below command for sure. Set below in my.cnffile:
对于自动提交，请务必使用以下命令。在my.cnf文件中设置如下：
[mysqld]
autocommit=0
回答by Jpsy
Instead of switching autocommit off manually at restore time you can already dump your MySQL data in a way that includes all necessary statements right into your SQL file.
无需在恢复时手动关闭自动提交，您已经可以将 MySQL 数据转储到 SQL 文件中，将所有必要的语句包含在内。
The command line parameter for mysqldump is --no-autocommit. You might also consider to add --optwhich sets a combination of other parameters to speed up restore operations.
mysqldump 的命令行参数是--no-autocommit. 您还可以考虑添加--optwhich set 与其他参数的组合以加快还原操作。
Here is an example for a complete mysqldump command line as I use it, containing --no-autocommitand --opt:
这是我使用的完整 mysqldump 命令行的示例，其中包含--no-autocommit和--opt：
mysqldump -hlocalhost -uMyUser -p'MyPassword' --no-autocommit --opt --default-character-set=utf8 --quote-names MyDbName > dump.sql
For details of these parameters see the reference of mysqldump
