Saving auto-increment mySQL identifiers?

Question

We want to get the auto-increment ID from mySQL without storing it until other non-mysql processes have completed successfully, so the record will not be saved if the application crashes or crashes. We need to use the identifier as the key for other processes. Essentially, we want to “reserve” auto-increment and insert rows in mySQL as the last step. We do not want to insert a row until we find out that the whole process has completed successfully.

Is it possible to make such an auto-increment reservation in mySQL?

Note. I know about SQL transactions. But our process contains things other than SQL that must be performed outside the database. This process can take several minutes to several hours. But we do not want any other process to use the same auto-increment identifier. That is why we want to “reserve” an automatic increment identifier without inserting any data into the database. -

Answer 1

The only way to generate an auto-increment value is to try pasting. But you can cancel this transaction and still read the generated identifier. In MySQL 5.1 and later, the default behavior is that auto-increment values ​​do not get returned to the stack when rolling back.

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

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

Answer 2

Have you discussed using mysql tranactions?

In essence, you start a transaction, if all the SQL queries are correct and can be compromised, then you commit the transaction. If not, then you roll back as if nothing had happened.

More information can be found at this link: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-transactions.html

Answer 3

you can use a temporary table along with a transaction

if the worksheet with the full transaction disappears and transfers the data to the real table

http://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm