CODEWITHSUNDEEP
mysql
cssyphus
cssyphus

Reputation: 40030

MySQL query next sequence number for MYSQL auto-incremented field

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?

Upvotes: 5

Views: 25104

Answers (2)

cssyphus
cssyphus

Reputation: 40030

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.

Upvotes: 5

eggyal
eggyal

Reputation: 125835

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

Upvotes: 1

Related Questions