5

In MySQL, is it possible to query the next number in sequence for an auto-incrementing field?

For a table called projects, the primary key for the table, project_id, is an auto-incrementing field. Before inserting a new row, I wish to know what number the project_id will be assigned.

How can I query that?

Improve this question
3
  • possible duplicate of Finding the next available id in MySQL Commented Sep 4, 2012 at 20:57
  • possible duplicate of Get Auto Increment value with MySQL query Commented Sep 4, 2012 at 20:58
  • Now why weren't those suggested when I was posting the question..? I checked out several... =^/ Thanks, I'll check them out. Commented Sep 4, 2012 at 21:05

2 Answers 2

Reset to default
5

Rid is correct. This question appears to be a duplicate of Finding the next available id in MySQL

In that thread, user Eimantas provided the working solution I used. Reproduced here for convenience:

SELECT Auto_increment FROM information_schema.tables WHERE table_name='the_table_you_want';

Note that you cannot write:

...WHERE table_name=`the_table_you_want`...

since backticks indicate a column name, not a table name.

CAVEATA:

Future readers are advised to note Gavin's comment (eggyal's answer) regarding race conditions (that is: another DB entry happening a split second before yours and "stealing" the ID you thought you would get).

If race conditions are even a remote possibility, using LAST_INSERT_ID() as described by eggyal is recommended over the method described in this answer.

Nonetheless, this answer is useful in many legitimate situations, including: learning/study situations, dev testing situations, low-user/low-use databases, etc.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Although this answer was down-voted, I have now used this method several times. Not every web dev is building CNN.com. Some of us build sites where concurrent USERS are rare... and database entries will never happen even within several hours of each other. On such low traffic sites, the above method is quite useful. A pox on the downvoters: "Poo to you with knobs on"
Even if you only have a single user, it is generally hard to prevent them from (accidentally) submitting the form multiple times - possibly in quick succession - through which this approach could also give rise to buggy behaviour. Given how easy it is to do things the "right" way, and how much sense there is in applying best practice whenever possible (if nothing else, for the personal discipline in order that it becomes automatic for when it is genuinely required), I cannot see any justification for ever performing this operation in a manner that is prone to error. -1
1

Do it the other way around: insert the new record first, then find out its value using LAST_INSERT_ID().

Improve this answer

6 Comments

Thanks, that's a useful command to know. However, I'd really like to know ahead of time if at all poss.
@gibberish: Why? Your requirements are indicative of bad design. See What is the XY problem?
@gebberish. You cannot possibly know for certain what the next value is. Say you do a query to find out what the next highest value is; then another user inserts a row, now your answer is incorrect. This is a "race condition." Also there are server options like auto_increment_offest and auto_increment_increment. The numbers generated are not always sequential. Therefore you need to actually do the insert before you know what number you generate.
Hi guys, I understand your concerns. I'll be using this in a routine that is only run by the system moderator. Since only the one user can run it, there is no risk of supercedence. I require this value for a recursive directory naming routine, and knowing it aforehand streamlines the process. But thank you for having my back on the design.
Not quite a perfect answer. There's some code (especially when do migration from Oracle to MySQL) that get next sequence no. first, do something, then do insert later. So I think the question is practical but this answer is impractical.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.