What is the shortest way to get the next free id from MySQL table?

Question

I have a table with id column. ids are in ascending order but not necessarily consecutive. For example: 1, 2, 3, 5, 6, 10, 11, 12

I need to find the next "free" id, i.e. max(id) + 1 (or 1 if the table is empty).

Currently I'm doing this in PHP like this:

function get_free_id($con) {
    $sql = "SELECT MAX(id) AS last_id FROM Table";
    $last_id_query = mysql_query($sql, $con);
    $last_id_result = mysql_fetch_array($last_id_query);
    $last_id = $last_id_result['last_id'];  
    return ($last_id == NULL) ? 1 : ($last_id + 1);
}

I have a feeling that this function is "too long" for this simple task and that this can be done much simpler.

Am I right ? How ?

dup http://stackoverflow.com/questions/1405393/finding-the-next-available-id-in-mysql — Haim Evgi, Aug 15 '10 at 12:12
Why do you need the next id? Is the id field in the table auto-incrementing? — Stephen, Aug 15 '10 at 12:12
It is not auto-incrementing. I need the next id in order to insert the next row. I don't like auto-incrementing. I like to control the values I enter to database. — Misha Moroshko, Aug 15 '10 at 12:18
that is quite odd behavior. may I ask what's the use of such a control? And at least you shouldn't name this field "id". for disambiguation — Your Common Sense, Aug 15 '10 at 12:20
@Misha: Sounds like what you really want to use is a GUID, but be aware that those incur a performance hit on the database index. Seriously though, there's nothing wrong with auto-increment. Use the tools you have available. — David, Aug 15 '10 at 12:21
if you really want to 'control' the id you write to database. you could maintain a sequence-like table. say, a table with one column(number), current value 1. on your application side, there would be a cache, let's say cache size 20. so in memory there are 20 ids. You can do sql-insert with an Id from the cache. once you run out the cached ids, go to that table,increment the column by cache-size. and read next 20. But beware of the clustered application server situation. or, you can generate a UUID as ids. I still think auto-incrementing field might be good choice for your case. — Kent, Aug 15 '10 at 12:54

score 8 · Accepted Answer · answered Aug 15 '10 at 12:14

8

There is no guaranteed way.
So, you shouldn't do it that way.

Create a record first, get autogenerated id and then use it where you wanted it.

answered Aug 15 '10 at 12:14

Your Common Sense

156,878
40
214
345

What do you mean by "There is no guaranteed way." ? – Misha Moroshko Aug 16 '10 at 10:41

score 1 · Answer 2 · answered Aug 15 '10 at 12:16

1

does auto_increment help?

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

answered Aug 15 '10 at 12:16

Kent

189,393
32
233
301

If I use AUTO_INCREMENT id, insert a couple of rows, and then close the connection. At later time, how would I get the next free id ? (say I need it for other purposes, not for the next INSERT) Should I save it from the last time I inserted a row ? – Misha Moroshko Aug 16 '10 at 10:40
1

@MishaMoroshko Why would you need to know the next free ID if not to do an insert? – Schwern May 23 '20 at 03:11

score 1 · Answer 3 · answered Aug 15 '10 at 14:43

The issue you will have in doing this way is when you have concurrent requests for the next ID. Two records could potentially end up requesting the same ID and then one will fail when you try to insert it. That being said, if you really want to do it this way, here are a couple of options.

// select the id to use for your insert statement
SELECT coalesce(max(id)+1,1) AS ID FROM `table`;

// select next value during insert
INSERT INTO `table`(id,name)
    SELECT coalesce(max(id)+1,1) AS ID, 'New Name' FROM `table`

That being said, I would advise against doing it this way. But here you have your answer.

I really need to think about concurrent requests... Anyway, my main question was how to get the number **in PHP**... — Misha Moroshko, Aug 16 '10 at 10:37

XDanger · Answer 4 · 2020-05-23T02:53:11.720

fast version:

id must be PRIMARY or UNIQUE INDEX!!!

SELECT IF( 
           @freeID:=IFNULL(
                  (
                       SELECT @r
                       FROM (SELECT @r:=@minID:=MIN(`id`)-1 FROM `table`) as v,
                       `table` 
                       WHERE `id` > @r:=@r+1 LIMIT 1
                   ), 
                   IF(@minID < 1, @r+1, @minID)
          ), @freeID, 1) as freeID

score 0 · Answer 5 · answered May 23 '20 at 03:34

Trying to determine the next ID is inviting race conditions. The next available ID is shared data which can change at any time.

Consider two database connections working on the same table simultaneously. max(id) is 9.

|  A                                          B
|  id = get_next_id(some_table) # 10
t                                             id = get_next_id(some_table) # 10
i
m  # Success
e  insert into some_table (id) values (id)
|                                             # Failure
V                                             insert into some_table (id) values (id)

Both ask for the next ID and get the same value. Both try to insert the same ID. One succeeds, the other fails.

You can try to patch this up with locks, transactions, sequence tables... but you'd be reimplementing auto_increment poorly.

Use auto_increment. If you want a specific ID, pass it in explicitly. Though note that this will cause the ID to increment from there. This is correct behavior to avoid ID conflicts.

-- Auto increment ID
insert into some_table (foo, bar) values (23, 42);

-- Explicit ID
insert into some_table (id, foo, bar) values (99, 23, 42);

insert into some_table (foo, bar) values (13, 44); -- ID is 100

If you want to know the next ID, you can check the information_schema table.

SELECT `AUTO_INCREMENT`
   FROM `information_schema`.`TABLES`
   WHERE `TABLE_SCHEMA` = ‘yourDatabaseName’
   AND `TABLE_NAME` =’yourTableName';

But it's difficult to think of a case when you need to know the next ID that isn't a symptom of a poor design.

A good schema design doesn't care what the next ID is, just that it is a unique identifier. You can use UUID primary keys. Then there is no "next" key.

create table people (
    id binary(16) primary key default (uuid_to_bin(uuid())),
    name varchar(255)
);

insert into people (name) values ('Yarrow Hock'), ('Che Guvera');

select * from people;
+------------------------------------+-------------+
| id                                 | name        |
+------------------------------------+-------------+
| 0x8A501F549CA611EA91B96B65B86DF892 | Yarrow Hock |
| 0x8A50240E9CA611EA91B96B65B86DF892 | Che Guvera  |
+------------------------------------+-------------+

You can use the default generated by MySQL, or you can pass your own in.

What is the shortest way to get the next free id from MySQL table?

5 Answers5