DelphiFAQ Home Search:

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

 

comments2 comments. Current rating: 4 stars (1 votes). Leave comments and/ or rate it.

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


Comments:

2006-08-10, 22:03:02
anonymous from United States  
rating
'It is not possible to do obtain the next value of an AUTO_INCREMENT field'

This is not true. If you have ever used phpMyAdmin, you know it shows this information in the table structure view. You want to use this query:

SHOW TABLE STATUS LIKE 'table_name';

http://blog.jamiedo../geek/560/
2017-08-23, 02:21:32
anonymous from Indonesia  
=RAHASIA MENHASILKAN UANG BERLIMPAH= ====RATUSAN RIBUH HINGGA JUTAAN RUPIAH BAHKAN SAMPAI RATUSAN JUTA=== ====TIAP HARI DENGAN BANTUAN NYAI-RONGGENG HU 0821-8948-1547=====

JIKA ANDA SUDAH CAPE DAN LELAH DENGAN STATUS P7-PERGI PAGI PULANG PETANG PENGHASILAN CUMA PAS-PASAN DAN TIDAK TAU HARUS BAGAIMANA LAGI UNTUK MENINGKATKAN KONDISI FINANSIAL KEUANGAN ANDA…MAKA SAYA MINTA SEGERA HUB NYAI-RONGGENG DI 082-189-481-547 BELIAU PASTI MEMBANTU ANDA SAMPAI ANDA SUKSES,SPERTI SAYA.KEHIDUPAN SAYA YANG DULUNYA SUSAH SEKARANG SUDAH SERBA BERKECUKUPAN BERKAT BANTUAN DARI NYAI RONGGENG.ANDA PASTI SUKSES JIKA UDAH DI BANTU AMA NYAI RONGGENG SEGERA SAJA HUB BELIAU JIKA ANDA UDAH CAPE HIDUP SUSAH.

DAN DI SINI PULA ANDA BISA MENGHASILKAN DANA CEPAT DENGAN CARA YANG LAIN ATAU DENGAN KATA LAIN
PESUGIHN TUYUL
UANG GAIB
ANKA GAIB
DAN PESUGIHAN PUTIH TANPA TUMBAL
This image was also posted here:
The UK NATIONAL LOTTERY scam
The UK NATIONAL LOTTERY scam
How to run a *.sql script (mysql)
Dating scammer Rose James
DelphiFAQ Site wide search function
Various dating scammers
mysql: How can I rename a database
How can I rename table names and column names in mysql
Compare 2 dates in mysql
Lock an entire mysql database with all its tables



Keywords:

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: