How to predict the next auto-increment number?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ljCharlie
Forum Contributor
Posts: 289
Joined: Wed May 19, 2004 8:23 am

How to predict the next auto-increment number?

Post by ljCharlie »

My primary ID in a MySQL database table is set to auto-increment. My question is, how do I predict or know what the next number is ahead of time before the entry is submitted to the database? Is this possible to know? In most cases, I found that this number is sequential but other cases I found that this number also jumps or skips if for example, I have ID 1, 2, 3, 4, 5, 6 and then I deleted entry 6, the next number would be 7 and not 6.

Help is appreciated.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

http://dev.mysql.com/doc/refman/5.0/en/ ... tatus.html

However, why do you want to know the next autoincrement value?

[edit]
URL corrected
[/edit]
Last edited by Weirdan on Tue Oct 18, 2005 10:25 am, edited 1 time in total.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

You can also call:

Code: Select all

SELECT LAST_INSERT_ID() FROM `my_table`;
But ya, why do you want to predict the next id?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

pickle wrote:You can also call:

Code: Select all

SELECT LAST_INSERT_ID() FROM `my_table`;
I think you did mean

Code: Select all

select last_insert_id();
and it would be the last insert id generated during the db connection session, not the next autoinc value for a table.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Ah, my bad. You're right ~Weirdan - using LAST_INSERT_ID() will only really work if one's only inserting into a single table with an auto_increment value. Otherwise, the values could be screwed up.

To get the next value, you can just add one:

Code: Select all

SELECT LAST_INSERT_ID() + 1;
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
ljCharlie
Forum Contributor
Posts: 289
Joined: Wed May 19, 2004 8:23 am

Post by ljCharlie »

Many thanks for all your help. The reason I want to know the id is in the following situation. I'm creating a website that its contents are store on a MySQL database tabel. So when the user click on a navigation menu, it has the following link characteristics:

<a href='found.php?id=3'>Welcome</a>

And so on. The id 3 is related to the primary id that is auto-increment. Now, for creating new pages, I need to know this id ahead of time so I can insert into the URL field on the table at the same the new page is inserted by the user.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Just change the order of inserts... First insert the page, then insert the url...
ljCharlie
Forum Contributor
Posts: 289
Joined: Wed May 19, 2004 8:23 am

Post by ljCharlie »

Thanks for the help. I see that changing the order or delay the insert of the URL field after the primary ID has been assigned will work; however, I'm not sure I know how to delay the process until the id has been assigned then do the URL field insert.
ljCharlie
Forum Contributor
Posts: 289
Joined: Wed May 19, 2004 8:23 am

Post by ljCharlie »

Many thanks for all your help. As it turns out, I don't need to store the id in the URL field after all. I can combine the url field and id on the fly.

Again, thanks!
Post Reply