如何关闭MySQL客户机的自动提交?

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时，是否有办法设置自动提交=0(而不必每次都输入)?

7 个解决方案

#1

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

更多关于命令集的自动提交= 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

直流

Expect script provided by Michael Hinds

Expect脚本由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 expect to find the location of your expect executable)

将此插入到脚本中的第一行(提示:使用希望找到expect可执行文件的位置)

#! /usr/bin/expect

then change the permissions of your script with..

然后使用..更改脚本的权限。

chmod 0744 myscript

then call the script

然后调用脚本

./myscript

直流

#2

You do this in 3 different ways:

你可以用三种不同的方式来做:

Before you do an INSERT, always issue a BEGIN; statement. This will turn off autocommits. You will need to do a COMMIT; once you want your data to be persisted in the database.

在插入之前，总是先发出一个开始;声明。这将关闭自动提交。你需要做一个承诺;一旦您希望数据被持久化到数据库中。
Use autocommit=0; every time you instantiate a database connection.

使用自动提交= 0;每次实例化数据库连接。
For a global setting, add a autocommit=0 variable in your my.cnf configuration file in MySQL.

对于全局设置，在MySQL中的my.cnf配置文件中添加一个自动提交=0变量。

#3

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中，但是需要在您的[客户]部分中添加作为init命令标志的参数，比如:

[client]
init-command='set autocommit=0'

#4

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 autocommit to be off for every client though.

这将为每个客户机设置自动提交。

#5

This is useful to check the status of autocommit;

这有助于检查自动提交的状态;

select @@autocommit;

#6

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 --opt which sets a combination of other parameters to speed up restore operations.

mysqldump的命令行参数是——不自动提交。您还可以考虑添加—opt，它设置了其他参数的组合以加速恢复操作。

Here is an example for a complete mysqldump command line as I use it, containing --no-autocommit and --opt:

这里是一个完整的mysqldump命令行的示例，我使用它，其中包含-不自动提交和-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

有关这些参数的详细信息，请参阅mysqldump的引用。

#7

-1

For auto commit off then use the below command for sure. Set below in my.cnf file:

对于自动提交，请确保使用下面的命令。在my.cnf文件中设置:

    [mysqld]
    autocommit=0

#1