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?
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.