[sql] Finding the next available id in MySQL

I have to find the next available id (if there are 5 data in database, I have to get the next available insert place which is 6) in a MySQL database. How can I do that? I have used MAX(id), but when I delete some rows from the database, it still holds the old max value it didn't update.

you said:

my id coloumn is auto increment i have to get the id and convert it to another base.So i need to get the next id before insert cause converted code will be inserted too.

what you're asking for is very dangerous and will lead to a race condition. if your code is run twice at the same time by different users, they will both get 6 and their updates or inserts will step all over each other.

i suggest that you instead INSERT in to the table, get the auto_increment value using LAST_INSERT_ID(), and then UPDATE the row to set whatever value you have that depends on the auto_increment value.

Given what you said in a comment:

my id coloumn is auto increment i have to get the id and convert it to another base.So i need to get the next id before insert cause converted code will be inserted too.

There is a way to do what you're asking, which is to ask the table what the next inserted row's id will be before you actually insert:

SHOW TABLE STATUS WHERE name = "myTable"

there will be a field in that result set called "Auto_increment" which tells you the next auto increment value.

The problem with many solutions is they only find the next "GAP", while ignoring if "1" is available, or if there aren't any rows they'll return NULL as the next "GAP".

The following will not only find the next available gap, it'll also take into account if the first available number is 1:

SELECT CASE WHEN MIN(MyID) IS NULL OR MIN(MyID)>1
-- return 1 if it's available or if there are no rows yet
THEN
    1
ELSE -- find next gap
    (SELECT MIN(t.MyID)+1
    FROM MyTable t (updlock)
    WHERE NOT EXISTS (SELECT NULL FROM MyTable n WHERE n.MyID=t.MyID+1))
END AS NextID
FROM MyTable

If you really want to compute the key of the next insert before inserting the row (which is in my opinion not a very good idea), then I would suggest that you use the maximum currently used id plus one:

SELECT MAX(id) + 1 FROM table

But I would suggest that you let MySQL create the id itself (by using a auto-increment column) and using LAST_INSERT_ID() to get it from the DBMS. To do this, use a transaction in which you execute the insert and then query for the id like:

INSERT INTO table (col1) VALUES ("Text");
SELECT LAST_INSERT_ID();

The returnset now contains only one column which holds the id of the newly generated row.

If you want to select the first gap, use this:

SELECT  @r
FROM    (
        SELECT  @r := MIN(id) - 1
        FROM    t_source2
        ) vars,
        t_source2
WHERE   (@r := @r + 1) <> id
ORDER BY
        id
LIMIT   1;

There is an ANSI syntax version of the same query:

SELECT  id
FROM    mytable mo
WHERE   (
        SELECT  id + 1
        FROM    mytable mi
        WHERE   mi.id < mo.id
        ORDER BY
                mi.id DESC
        LIMIT 1
        ) <> id
ORDER BY
        id,
LIMIT   1

however, it will be slow, due to optimizer bug in MySQL.

Update 2014-12-05: I am not recommending this approach due to reasons laid out in Simon's (accepted) answer as well as Diego's comment. Please use query below at your own risk.

The shortest one i found on mysql developer site:

SELECT Auto_increment FROM information_schema.tables WHERE table_name='the_table_you_want'

mind you if you have few databases with same tables, you should specify database name as well, like so:

SELECT Auto_increment FROM information_schema.tables WHERE table_name='the_table_you_want' AND table_schema='the_database_you_want';

<?php
Class Database{
    public $db;
    public $host   = DB_HOST;
    public $user   = DB_USER;
    public $pass   = DB_PASS;
    public $dbname = DB_NAME;

    public $link;
    public $error;

    public function __construct(){
        $this->connectDB();
    }
    private function connectDB(){
    $this->link = new mysqli($this->host, $this->user, $this->pass, $this->dbname);
    if(!$this->link){
        $this->error ="Connection fail".$this->link->connect_error;
        return false;
    }
 }

    // Select or Read data

    public function select($query){
        $result = $this->link->query($query) or die($this->link->error.__LINE__);
        if($result->num_rows > 0){
            return $result;
        } else {
            return false;
        }
    }

}
 $db = new Database();
$query = "SELECT * FROM table_name WHERE id > '$current_postid' ORDER BY ID ASC LIMIT 1";
$postid = $db->select($query);
if ($postid) {
  while ($result = $postid->fetch_assoc()) { 
        echo $result['id'];
    } 

  } ?>

It's too late to answer this question now, but hope this helps someone.

@Eimantas has already given the best answer but the solution won't work if you have two or more tables by the same name under the same server.

I have slightly modified @Eimantas's answer to tackle the above problem.

select Auto_increment as id from information_schema.tables where table_name = 'table_name' and table_schema = 'database_name'

SELECT ID+1 "NEXTID" 
FROM ( 
    SELECT ID from TABLE1 
    WHERE ID>100 order by ID
) "X" 
WHERE not exists (
    SELECT 1 FROM TABLE1 t2
    WHERE t2.ID=X.ID+1
) 
LIMIT 1

In addition to Lukasz Lysik's answer - LEFT-JOIN kind of SQL.
As I understand, if have id's: 1,2,4,5 it should return 3.

SELECT u.Id + 1 AS FirstAvailableId
FROM users u
LEFT JOIN users u1 ON u1.Id = u.Id + 1
WHERE u1.Id IS NULL
ORDER BY u.Id
LIMIT 0, 1

Hope it will help some of visitors, although post are rather old.

One way to do it is to set the index to be auto incrementing. Then your SQL statement simply specifies NULL and then SQL parser does the rest for you.

INSERT INTO foo VALUES (null);

This worked well for me (MySQL 5.5), also solving the problem of a "starting" position.

SELECT
    IF(res.nextID, res.nextID, @r) AS nextID
FROM
    (SELECT @r := 30) AS vars,
    (
    SELECT MIN(t1.id + 1) AS nextID
    FROM test t1
    LEFT JOIN test t2
      ON t1.id + 1 = t2.id
    WHERE t1.id >= @r
      AND t2.id IS NULL
      AND EXISTS (
          SELECT id
          FROM test
          WHERE id = @r
      )
  LIMIT 1
  ) AS res
LIMIT 1

As mentioned before these types of queries are very slow, at least in MySQL.

As I understand, if have id's: 1,2,4,5 it should return 3.

SELECT t1.id + 1
FROM theTable t1
WHERE NOT EXISTS (
    SELECT * 
    FROM theTable t2
    WHERE t2.id = t1.id + 1
)
LIMIT 1

If this is used in conjunction for INSERTING a new record you could use something like this.

(You've stated in your comments that the id is auto incrementing and the other table needs the next ID + 1)

INSERT INTO TABLE2 (id, field1, field2, field3, etc) 
VALUES(
   SELECT (MAX(id) + 1), field1, field2, field3, etc FROM TABLE1
   WHERE condition_here_if_needed
)

This is pseudocode but you get the idea