如何防止mysql隐式提交 [英] How to prevent mysql implicit commit
问题描述
mysql 文档指出某些语句将在事务期间导致隐式提交.例如:
The mysql documentation states that certain statements will cause an implicit commit during a transaction. For example:
CREATE TABLE foo (bar INT);
START TRANSACTION;
INSERT INTO foo VALUES (1);
CREATE TEMPORARY TABLE mumble like foo;
ALTER TABLE mumble modify bar INT UNSIGNED;
ROLLBACK;
SELECT * FROM foo;
回滚后，我从foo中得到了一行-文档实际上说，如果您使用临时关键字，则alter table不应导致隐式提交，但是ALTER TEMPORARY TABLE无效语法，并删除临时表不会导致隐式提交，因此我怀疑只有一个错误(至少从5.5.29开始)
after the rollback, I get a single row back from foo -- the documentation actually says that an alter table shouldn't cause an implicit commit if you use the temporary keyword, but ALTER TEMPORARY TABLE is not valid syntax, and dropping a temporary table doesn't cause an implicit commit, so I suspect there's just a bug (at least as of 5.5.29)
无论如何，我想做的就是告诉mysql不要隐式提交，但是如果给出命令会导致隐式提交，则失败/回滚.
In any case, what I'd like to do is to tell mysql to never implicitly commit, but instead to fail / rollback if a command is given which would cause an implicit commit.
我怀疑没有办法，只是环顾四周，但我希望我错了.希望这里的人知道:)
I suspect there is no way to do this, having looked around a fair bit, but I'm hoping I'm wrong. Hopefully someone on here knows :)
推荐答案
我刚刚成功尝试的另一种hacky方法是通过mysql特定命令获取创建表DDL
Another hacky approach that I just tried successfully is to get the create table DDL via the mysql specific command
SHOW CREATE TABLE `tableName`
然后进行一些正则表达式魔术，并制作一个新的DDL查询，该查询将基于原始表创建一个临时表，并将所有alter table更改合并到create表中.
Then make some regexp magic and craft a new DDL query that will create a temporary table based on the original table, with all the alter table changes incorporated into the create table.
在基于PHP的项目中，我执行了以下操作以向临时表添加唯一索引.它成功了，交易过程中不再发生隐式提交.
In my PHP based project, I did the following to add a unique index to a temporary table. It did the trick and no more implicit commit occurred midst the transaction.
$createDDL = ... get from SHOW CREATE TABLE `tableName`
$nr = 0;
$createDDL = preg_replace("/CREATE TABLE `$tableName` \(/", "CREATE TEMPORARY TABLE `$tmpName` (\nUNIQUE `ukey-1` ($uniqCols),", $createDDL, -1, $nr);
if (!$nr)
throw new Exception("CREATE TABLE replacement error. No reps made.");
mysqli_query($con, $createDDL);
EDIT (顺便说一下)，这里有一些错误(功能)报告(已有很多年了).在第一个中，您可以看到一个响应(日期为2006年)，其中指出:由于此行为与oracle db中的相同，因此这是一致的...
EDIT By the way here are some bug (feature) reports (since many years). In the first you can see a response (dating to 2006) that states: since this behavior is the same as in oracle db, this is the consistent one...
- http://bugs.mysql.com/bug.php?id=22857
- http://bugs.mysql.com/bug.php?id=28109
也许应该启动功能请求垃圾邮件运动"，以使该功能请求恢复正常！
Maybe a feature request "spamming campaign" should be initiated for this feature request to revive!
这篇关于如何防止mysql隐式提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
