Finding the next available id in MySQL

Question

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

User · Answer

SELECT ID 1  NEXTID   FROM        SELECT ID from TABLE1      WHERE ID gt 100 order by ID    X   WHERE not exists       SELECT 1 FROM TABLE1 t2     WHERE t2 ID X ID 1    LIMIT 1

User · Answer

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  gt    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

User · Answer

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

User · Answer

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

User · Answer

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  gt 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

User · Answer

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   lt  gt  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  lt  mo id         ORDER BY                 mi id DESC         LIMIT 1            lt  gt  id ORDER BY         id  LIMIT   1   however  it will be slow  due to optimizer bug in MySQL

User · Answer

lt  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- gt connectDB              private function connectDB         this- gt link   new mysqli  this- gt host   this- gt user   this- gt pass   this- gt dbname       if   this- gt link            this- gt error   Connection fail   this- gt link- gt connect error          return false                   Select or Read data      public function select  query            result    this- gt link- gt query  query  or die  this- gt link- gt error   LINE             if  result- gt num rows  gt  0               return  result            else               return false                       db   new Database     query    SELECT   FROM table name WHERE id  gt    current postid  ORDER BY ID ASC LIMIT 1    postid    db- gt select  query   if   postid      while   result    postid- gt fetch assoc               echo  result  id                  gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I don t think you can ever be sure on the next id  because someone might insert a new row just after you asked for the next id  You would at least need a transaction  and if I m not mistaken you can only get the actual id used after inserting it  at least that is the common way of handling it -- see http   dev mysql com doc refman 5 0 en getting-unique-id html

[sql] Finding the next available id in MySQL

Examples related to sql

Examples related to mysql