MySQL AUTO_INCREMENT没有ROLLBACK

我正在使用MySQL的AUTO_INCREMENT字段和InnoDB来支持事务.我注意到当我回滚事务时,AUTO_INCREMENT字段没有回滚？我发现它是按照这种方式设计的,但有没有解决方法呢？

1> jmucchiello..：

---

它不能那样工作.考虑:

程序一,你打开一个事务并插入一个表有一个autoinc主键的表FOO(任意,我们说它的键值为557).

程序两次启动,它打开一个事务并插入表FOO得到558.

将两个插入程序编程到表BAR中,该表具有作为FOO的外键的列.所以现在558位于FOO和BAR.

程序二现在提交.

程序三启动并从表FOO生成报告.打印出558记录.

之后,程序一回滚.

数据库如何回收557值？是否进入FOO并减少大于557的所有其他主键？它是如何修复BAR的？它如何擦除报告程序中输出的558三个输出？

出于同样的原因,Oracle的序列号也与事务无关.

如果你可以在固定的时间内解决这个问题,我相信你可以在数据库领域赚很多钱.

现在,如果您要求自动增量字段永远不会有间隙(例如,用于审计目的).然后你无法回滚你的交易.相反,您需要在记录上有一个状态标志.在第一次插入时,记录的状态为"未完成",然后您开始交易,完成工作并将状态更新为"竞争"(或您需要的任何内容).然后当你提交时,记录是实时的.如果事务回滚,则不完整的记录仍然存在于审计中.这会引起许多其他问题,但这是处理审计跟踪的一种方法.

2> Tamas Czineg..：

---

让我指出一些非常重要的事情:

您永远不应该依赖自动生成密钥的数字功能.

也就是说,除了将它们与等式(=)或不等式(<>)进行比较之外,你不应该做任何其他事情.没有关系运算符(<,>),没有按索引排序等.如果需要按"添加日期"排序,请添加"添加日期"列.

把它们视为苹果和橘子:问一个苹果是否与橙子相同是否有意义？是.问一个苹果是否大于橙色是否有意义？不.(实际上,确实如此,但你明白我的意思.)

如果遵守此规则,自动生成索引的连续性中的间隙不会导致问题.

---

这是一个建议,而不是答案

---

你有资格获得这个资格吗？我的意思是,为什么在autoinc列中已经有一个非常好的值,为什么要添加一个完整的其他列,以及所需的所有开销(索引管理,磁盘使用,更多IO等等)？根据定义,它提供了一个永不重复的唯一值,并且随着记录的添加而不断增加.它唯一不是连续的,但只要你假设序列中存在间隙,我就会发现使用它来执行诸如按插入顺序对一组记录进行排序等问题没有任何问题.事实上,就性能和清晰度而言,我认为这是最好的方式.

3> 小智..：

---

我有一个客户需要ID在发票表上回滚,其中订单必须是连续的

我在MySQL中的解决方案是删除AUTO-INCREMENT并从表中提取最新的Id,添加一个(+1)然后手动插入.

如果表名为"TableA"且自动增量列为"Id"

INSERT INTO TableA (Id, Col2, Col3, Col4, ...)
VALUES (
(SELECT Id FROM TableA t ORDER BY t.Id DESC LIMIT 1)+1,
Col2_Val, Col3_Val, Col4_Val, ...)

---

这不是一个原子操作,当多个查询获得相同的maxID时肯定会有一个小窗口.

---

可能这会更好`(SELECT MAX(id)AS maxval FROM table_name;)+ 1`

---

那应该放在答案中.

4> Paul Lefebvr..：

---

为什么要关心它是否回滚？AUTO_INCREMENT键字段不应该有任何意义,所以你真的不应该关心使用什么值.

如果您有想要保留的信息,则可能需要另一个非关键列.

5> gpojd..：

---

我不知道有什么方法可以做到这一点.根据MySQL文档,这是预期的行为,并将发生在所有innodb_autoinc_lock_mode锁定模式.具体文字是:

在所有锁定模式(0,1和2)中,如果生成自动增量值的事务回滚,则这些自动增量值将"丢失".一旦为自动增量列生成值,则不能回滚,无论"INSERT-like"语句是否完成,以及是否回滚包含的事务.这些丢失的值不会被重用.因此,存储在表的AUTO_INCREMENT列中的值可能存在间隙.

6> Marcus Adams..：

---

如果在回滚或删除后设置auto_increment为1，则在下一次插入时，MySQL将看到1已使用该MAX()值，而是获取该值并将其加1。

这将确保如果删除最后一个值的行（或回滚插入），则将重用该行。

要将auto_increment设置为1，请执行以下操作：

ALTER TABLE tbl auto_increment = 1

这不像简单地继续输入下一个数字那样有效，因为这样做MAX()可能会很昂贵，但是如果您不经常删除/回滚并且沉迷于重用最高值，那么这是一种现实的方法。

请注意，这不能防止中间删除的记录出现间隙，或者在将auto_increment设置回1之前是否应该进行另一次插入。