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 ?

Answer 1

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.

Answer 2

does auto_increment help?

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

Answer 3

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.