保留mySQL自动递增的ID? [英] Reserving mySQL auto-incremented IDs?

问题描述

我们要从mySQL获取一个自动递增的ID，而不实际存储它，直到其他与mysql相关的非成功过程都成功完成为止，这样，如果发生异常或应用程序崩溃，就不会存储该条目.我们需要使用ID作为其他进程的密钥.本质上，我们要保留"自动增量，然后将行插入到mySQL中作为最后一步.在我们知道整个过程成功完成之前，我们不希望插入任何行.

We want to obtain an auto-increment ID from mySQL without actually storing it until the other non-mysql related processes are successfully completed, so that the entry is not stored if an exception or application crash happens. We need to use the ID as a key for the other processes. In essence we want to "reserve" the auto-increment and insert the rows into mySQL as the last step. We don’t want to insert any row until we know the entire process has completed successfully.

是否可以在mySQL中进行这种自动增量保留?

Is it possible to do this sort of auto-increment reservation in mySQL?

注意:我了解SQL事务.但是，我们的流程包含需要在数据库外部进行的非SQL事务.这些过程可能需要几分钟到几个小时.但是我们不希望任何其他使用相同自动递增ID的进程.这就是为什么我们要保留"一个自动递增的ID而不真正将任何数据插入数据库的原因. –

Note: I know about the SQL transactions. But our process contains non-SQL stuff that need to happen outside of the DB. These process may take few mins to several hours. But we don't want any other process using the same auto-increment ID. That is why we want a "reserve" an auto-increment ID without really inserting any data into the DB. –

推荐答案

生成自动增量值的唯一方法是尝试插入.但是您可以回滚该事务，并且仍然读取生成的ID.在MySQL 5.1及更高版本中，默认行为是回滚时不会将自动增量值返回"到堆栈中.

The only way to generate an auto-increment value is to attempt the insert. But you can roll back that transaction, and still read the id generated. In MySQL 5.1 and later, the default behavior is that auto-increment values aren't "returned" to the stack when you roll back.

START TRANSACTION;
INSERT INTO mytable () VALUES ();
ROLLBACK;
SELECT LAST_INSERT_ID() INTO @my_ai_value;

现在，您可以确定没有其他事务会尝试使用该值，因此您可以在外部流程中使用它，然后最终手动插入一个使用该ID值的值(当您插入特定的ID值时， MySQL不会产生新值.

Now you can be sure that no other transaction will try to use that value, so you can use it in your external processes, and then finally insert a value manually that uses that id value (when you insert a specific id value, MySQL does not generate a new value).

这篇关于保留mySQL自动递增的ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！