具有 AUTO_INCRMENT 主 ID 的 MySQL 表在回滚后不会释放该数字
我有一张桌子，上面有账单。每张账单都有一个 ID，该 ID 在我插入新记录后来自数据库。该字段是一个设置了 AUTO_INCRMENT 的 INTEGER。
如果我插入一条新记录作为事务的一部分并且必须回滚该事务，则 ID 将被占用并消失。因此，下一条记录将成为更高的 ID，即使该 ID 未被使用。
帐单最好采用线性编号，这样会计人员就可以找出是否有问题。
I have a table with bills. Every bill has an id that comes from the DB after I insert a new record. The field is an INTEGER with AUTO_INCREMENT set.
If I insert a new record as part of a transaction and I have to rollback this transaction, the ID is taken and gone. So the next record becomes the ID one higher, even though this ID is not in use.
It would be better for the bills to have a linear numbering, so the accounting can figure out if something is wrong.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
出于并发原因，自动增量值无法随您的事务“回滚”。如果在您的事务处理过程中另一个进程插入了记录，您稍后将面临与它们的 ID 发生冲突的风险。
举个例子，假设进程“A”中的事务获取 ID 1,2 和 3。另一个进程“B”运行并获取 ID 4 和 5。如果身份随您的事务回滚，并且需要下一个进程“C” 5 个 ID，它会得到 1,2,3,4,5，但是 4 和 5 已经被进程“B”占用了。
For concurrency reasons, the auto increment value cannot "rollback" with your transaction. If another process had inserted records while your transaction was in process, you'd risk a collision with their IDs later.
As an example, let's say your transaction in process "A" grabs IDs 1,2 and 3. Another process "B" runs and gets IDs 4 and 5. If the identity rolled back with your transaction and the next process "C" needed 5 IDs, it would get 1,2,3,4,5 but 4 and 5 were already taken by process "B".