DelphiFAQ Home Search:
General :: Databases :: mysql
General information about mysql - how to get around the differences between version 3.33 an 4, how to do stuff that you think you need a nested query for etc.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Featured Article

How can I obtain the next auto_increment number (before inserting a record)?

Question:

How can I obtain the next auto_increment number (before inserting a record)?

Answer:

At first glance you might think to use select MAX(..) to obtain the most recent number and add one.. but that would not work if the most recent row got deleted or if there are two concurrent transactions.

It is not possible to do obtain the next value of an AUTO_INCREMENT field but you can use a little trick:

Let's say you have a table CUSTOMERS with a unique CUST_ID which is an auto_increment integer.

Change this to be a unique integer (no auto_increment) and create a second helper table CUSTOMER_IDS.

This second table has only the purpose to deliver a new unique number. In order for this to work, you'll always have to use this helper table whenever a row is inserted into your main table CUSTOMERS.

When you want to find out what the next number will be, you can use LAST_INSERT_ID() on that helper table. For more details read the documentation on this function.

http://dev.mysql.com/doc/mysql/en/Information_functions.html


Generated 20:00:31 on Apr 23, 2018