can we get the preset auto-increment number before any insertion

[Nakata kokuyo]

hi,

if the table is blank , how can we get the preset auto-increment number ?

i have a try on this :

select case when LAST_INSERT_ID() = " " then '10001' else  LAST_INSERT_ID()+1 end as running_num from tbl_invoicehdr limit 1

it return nothing if the table have no records

even i try as follow but give me same blank result

SELECT LAST_INSERT_ID()  FROM tbl_invoicehdr

besides in mysql , to compare the column is contains empty string, is it like this ?

LAST_INSERT_ID() = " "

thank you very much for helping

database used : mysql

[Scott Selikoff]

Not really. MySQL has the neat feature that it keeps auto-incrementing from the last insert even if you wipe the entire table. This is one of the reasons primary key management is often set as an external service in more advanced systems instead of using database features.

You could run your cod as a transaction so that you can read the value of the record after insertion while not committing the changes. JDBC has features to read the newly generated keys from previous an update statement but they are not guarenteed to work so often times people just select max id (which is dangerous too).