MySQL AUTO_INCREMENT 不回滚

MySQL AUTO_INCREMENT does not ROLLBACK

提问by codegy

I'm using MySQL's AUTO_INCREMENT field and InnoDB to support transactions. I noticed when I rollback the transaction, the AUTO_INCREMENT field is not rollbacked? I found out that it was designed this way but are there any workarounds to this?

我使用 MySQL 的 AUTO_INCREMENT 字段和 InnoDB 来支持事务。我注意到当我回滚事务时，AUTO_INCREMENT 字段没有回滚？我发现它是这样设计的，但有什么解决方法吗？

采纳答案by Tamas Czinege

Let me point out something very important:

让我指出一件非常重要的事情：

You should never depend on the numeric features of autogenerated keys.

您永远不应该依赖自动生成的键的数字特征。

That is, other than comparing them for equality (=) or unequality (<>), you should not do anything else. No relational operators (<, >), no sorting by indexes, etc. If you need to sort by "date added", have a "date added" column.

也就是说，除了比较它们是否相等 (=) 或不相等 (<>) 之外，您不应该做任何其他事情。没有关系运算符（<、>），没有按索引排序等。如果您需要按“添加日期”排序，请设置“添加日期”列。

Treat them as apples and oranges: Does it make sense to ask if an apple is the same as an orange? Yes. Does it make sense to ask if an apple is larger than an orange? No. (Actually, it does, but you get my point.)

把它们当作苹果和橙子：问苹果和橙子是否一样有意义吗？是的。问苹果是否比橙子大是否有意义？不。（实际上，确实如此，但你明白我的意思。）

If you stick to this rule, gaps in the continuity of autogenerated indexes will not cause problems.

如果你坚持这个规则，自动生成索引的连续性中的差距不会导致问题。

回答by jmucchiello

It can't work that way. Consider:

它不能那样工作。考虑：

• program one, you open a transaction and insert into a table FOO which has an autoinc primary key (arbitrarily, we say it gets 557 for its key value).
• Program two starts, it opens a transaction and inserts into table FOO getting 558.
• Program two inserts into table BAR which has a column which is a foreign key to FOO. So now the 558 is located in both FOO and BAR.
• Program two now commits.
• Program three starts and generates a report from table FOO. The 558 record is printed.
• After that, program one rolls back.

• 程序一，你打开一个事务并插入到一个表 FOO 中，它有一个 autoinc 主键（任意地，我们说它的键值是 557）。
• 程序二启动，打开一个事务，插入表FOO得到558。
• 将两个插入编程到表 BAR 中，该表有一列是 FOO 的外键。所以现在 558 位于 FOO 和 BAR。
• 程序二现在提交。
• 程序三启动并从表 FOO 生成报告。558 记录被打印出来。
• 之后，程序一回滚。

How does the database reclaim the 557 value? Does it go into FOO and decrement all the other primary keys greater than 557? How does it fix BAR? How does it erase the 558 printed on the report program three output?

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

Oracle's sequence numbers are also independent of transactions for the same reason.

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

If you can solve this problem in constant time, I'm sure you can make a lot of money in the database field.

如果你能在恒定的时间内解决这个问题，我相信你可以在数据库领域赚到很多钱。

Now, if you have a requirement that your auto increment field never have gaps (for auditing purposes, say). Then you cannot rollback your transactions. Instead you need to have a status flag on your records. On first insert, the record's status is "Incomplete" then you start the transaction, do your work and update the status to "compete" (or whatever you need). Then when you commit, the record is live. If the transaction rollsback, the incomplete record is still there for auditing. This will cause you many other headaches but is one way to deal with audit trails.

现在，如果您要求您的自动增量字段永远不会有间隙（例如，出于审计目的）。那么您就无法回滚您的交易。相反，您需要在记录上有一个状态标志。第一次插入时，记录的状态为“未完成”，然后您开始事务，完成工作并将状态更新为“竞争”（或您需要的任何内容）。然后当你提交时，记录是实时的。如果事务回滚，则不完整的记录仍然存在以供审计。这会给您带来许多其他麻烦，但这是处理审计跟踪的一种方法。

回答by Michael Corrigan

I had a client needed the ID to rollback on a table of invoices, where the order must be consecutive

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

My solution in MySQL was to remove the AUTO-INCREMENT and pull the latest Id from the table, add one (+1) and then insert it manually.

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

If the table is named "TableA" and the Auto-increment column is "Id"

如果表名为“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, ...)

回答by Paul Lefebvre

Why do you care if it is rolled back? AUTO_INCREMENT key fields are not supposed to have any meaning so you really shouldn't care what value is used.

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

If you have information you're trying to preserve, perhaps another non-key column is needed.

如果您有要保留的信息，则可能需要另一个非键列。

回答by gpojd

I do not know of any way to do that. According to the MySQL Documentation, this is expected behavior and will happen with all innodb_autoinc_lock_modelock modes. The specific text is:

我不知道有什么方法可以做到这一点。根据MySQL 文档，这是预期的行为，所有innodb_autoinc_lock_mode锁定模式都会发生。具体文字是：

In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost.” Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table.

在所有锁定模式（0、1 和 2）中，如果生成自增值的事务回滚，则这些自增值将“丢失”。一旦为自增列生成了值，无论“INSERT-like”语句是否完成，以及包含的事务是否回滚，都无法回滚。此类丢失的值不会重用。因此，存储在表的 AUTO_INCREMENT 列中的值可能存在间隙。

回答by Marcus Adams

If you set auto_incrementto 1after a rollback or deletion, on the next insert, MySQL will see that 1is already used and will instead get the MAX()value and add 1 to it.

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

This will ensure that if the row with the last value is deleted (or the insert is rolled back), it will be reused.

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

To set the auto_increment to 1, do something like this:

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

ALTER TABLE tbl auto_increment = 1

This is not as efficient as simply continuing on with the next number because MAX()can be expensive, but if you delete/rollback infrequently and are obsessed with reusing the highest value, then this is a realistic approach.

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

Be aware that this does not prevent gaps from records deleted in the middle or if another insert should occur prior to you setting auto_increment back to 1.

请注意，这不会阻止中间删除记录的间隙，或者在将 auto_increment 设置回 1 之前应该发生另一个插入。

回答by danis

INSERT INTO prueba(id) 
VALUES (
(SELECT IFNULL( MAX( id ) , 0 )+1 FROM prueba target))

If the table doesn't contain values or zero rows

如果表不包含值或零行

add target for error mysql type update FROM on SELECT

在 SELECT 上添加错误 mysql 类型更新的目标

回答by teomor

Concrete answer to this specific dilemma (which I also had) is the following:

这个特定困境（我也遇到过）的具体答案如下：

1) Create a table that holds different counters for different documents (invoices, receipts, RMA's, etc..); Insert a record for each of your documents and add the initial counter to 0.

1) 创建一个表格，其中包含不同文件（发票、收据、RMA 等）的不同计数器；为每个文档插入一条记录，并将初始计数器添加到 0。

2) Before creating a new document, do the following (for invoices, for example):

2) 在创建新文档之前，请执行以下操作（例如，对于发票）：

UPDATE document_counters SET counter = LAST_INSERT_ID(counter + 1) where type = 'invoice'

3) Get the last value that you just updated to, like so:

3) 获取您刚刚更新到的最后一个值，如下所示：

SELECT LAST_INSERT_ID()

or just use your PHP (or whatever) mysql_insert_id() function to get the same thing

或者只是使用你的 PHP（或其他） mysql_insert_id() 函数来获得同样的东西

4) Insert your new record along with the primary ID that you just got back from the DB. This will override the current auto increment index, and make sure you have no ID gaps between you records.

4) 插入您的新记录以及您刚从数据库中获取的主 ID。这将覆盖当前的自动增量索引，并确保您的记录之间没有 ID 间隙。

This whole thing needs to be wrapped inside a transaction, of course. The beauty of this method is that, when you rollback a transaction, your UPDATE statement from Step 2 will be rolled back, and the counter will not change anymore. Other concurrent transactions will block until the first transaction is either committed or rolled back so they will not have access to either the old counter OR a new one, until all other transactions are finished first.

当然，这整个事情都需要包含在一个事务中。这种方法的美妙之处在于，当您回滚一个事务时，您在第 2 步中的 UPDATE 语句将被回滚，并且计数器不会再发生变化。其他并发事务将阻塞，直到第一个事务提交或回滚，因此在所有其他事务首先完成之前，它们将无法访问旧计数器或新计数器。

回答by mld.oscar

SOLUTION:

解决方案：

Let's use 'tbl_test' as an example table, and suppose the field 'Id' has AUTO_INCREMENT attribute

让我们使用 'tbl_test' 作为示例表，并假设字段 'Id' 具有 AUTO_INCREMENT 属性

CREATE TABLE tbl_test (
Id int NOT NULL AUTO_INCREMENT ,
Name varchar(255) NULL ,
PRIMARY KEY (`Id`)
)
;

Let's suppose that table has houndred or thousand rows already inserted and you don't want to use AUTO_INCREMENT anymore; because when you rollback a transaction the field 'Id' is always adding +1 to AUTO_INCREMENT value. So to avoid that you might make this:

假设该表已经插入了成百上千行，并且您不想再使用 AUTO_INCREMENT；因为当您回滚事务时，字段 'Id' 总是将 +1 添加到 AUTO_INCREMENT 值。所以为了避免这种情况，你可能会这样做：

• Let's remove AUTO_INCREMENT value from column 'Id' (this won't delete your inserted rows):

• 让我们从“Id”列中删除 AUTO_INCREMENT 值（这不会删除您插入的行）：

ALTER TABLE tbl_test MODIFY COLUMN Id  int(11) NOT NULL FIRST;

• Finally, we create a BEFORE INSERT Trigger to generate an 'Id' value automatically. But using this way won't affect your Id value even if you rollback any transaction.

• 最后，我们创建一个 BEFORE INSERT 触发器来自动生成一个“Id”值。但是即使您回滚任何事务，使用这种方式也不会影响您的 Id 值。

CREATE TRIGGER trg_tbl_test_1
BEFORE INSERT ON tbl_test
FOR EACH ROW
  BEGIN
    SET NEW.Id= COALESCE((SELECT MAX(Id) FROM tbl_test),0) + 1;
  END;

That's it! You're done!

就是这样！你完成了！

You're welcome.

别客气。

回答by tvanfosson

If you need to have the ids assigned in numerical order with no gaps, then you can't use an autoincrement column. You'll need to define a standard integer column and use a stored procedure that calculates the next number in the insert sequence and inserts the record within a transaction. If the insert fails, then the next time the procedure is called it will recalculate the next id.

如果您需要按数字顺序无间隙地分配 id，则不能使用自动增量列。您需要定义一个标准整数列并使用一个存储过程来计算插入序列中的下一个数字并将记录插入到事务中。如果插入失败，那么下次调用该过程时，它将重新计算下一个 id。

Having said that, it is a bad idea to rely on ids being in some particular order with no gaps. If you need to preserve ordering, you should probably timestamp the row on insert (and potentially on update).

话虽如此，依赖 id 处于某种特定顺序而没有间隙是一个坏主意。如果您需要保留顺序，您可能应该在插入时（以及可能在更新时）对行进行时间戳记。