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?

cssyphus
  • 37,875
  • 18
  • 96
  • 111
  • possible duplicate of [Finding the next available id in MySQL](http://stackoverflow.com/questions/1405393/finding-the-next-available-id-in-mysql) – rid Sep 04 '12 at 20:57
  • possible duplicate of [Get Auto Increment value with MySQL query](http://stackoverflow.com/questions/933565/get-auto-increment-value-with-mysql-query) – rid Sep 04 '12 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. – cssyphus Sep 04 '12 at 21:05

2 Answers2

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';

Kudos to eggyal and GavinTowey in this thread, though, for their direction.

Future readers are advised to note Gavin's comment (to 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).

Use of LAST_INSERT_ID() as described by eggyal is strongly recommended over the method described in this answer.

I encourage future readers to upvote eggyal's answer, as it is a better approach, but I would appreciate no further downvotes to this answer. Since this is what the question specifically asked, this is the "correct" answer - eggyals' downvotes notwithstanding. I trust there are no hard feelings; as you know, I receive no benefit for choosing my own answer as the correct one.

Community
  • 1
  • 1
cssyphus
  • 37,875
  • 18
  • 96
  • 111
  • 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"](http://www.suslik.org/Humour/FilmOrTV/BlackAdder/ba3-1.html) – cssyphus Nov 27 '13 at 18:39
  • 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 – eggyal Nov 27 '13 at 22:49
1

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

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Thanks, that's a useful command to know. However, I'd really like to know ahead of time if at all poss. – cssyphus Sep 04 '12 at 21:06
  • @gibberish: Why? Your requirements are indicative of bad design. See [What is the XY problem?](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) – eggyal Sep 04 '12 at 21:07
  • 3
    @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. – Gavin Towey Sep 04 '12 at 21:14
  • 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. – cssyphus Sep 05 '12 at 15:54
  • 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. – Scott Chu May 22 '17 at 07:20
  • @ScottChu: In the worst case, you can insert a dummy record which you then update with the intended values. But I've never in my life had to do that, on projects of any size—including migrations from Oracle. – eggyal May 22 '17 at 08:40