Finding the Next Available Id in MySQL

Finding the next available id in MySQL

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

How to get the next auto-increment id in mysql

Use LAST_INSERT_ID() from your SQL query.

Or

You can also use mysql_insert_id() to get it using PHP.

mysql find smallest + unique id available

You can get the minimum available ID using this query:

SELECT MIN(t1.ID + 1) AS nextID
FROM tablename t1
   LEFT JOIN tablename t2
       ON t1.ID + 1 = t2.ID
WHERE t2.ID IS NULL

What it does is that it joins the table with itself and checks whether the min+1 ID is null or not. If it's null, then that ID is available. Suppose you have the table where ID are:

1

2

5

6

Then, this query will give you result as 3 which is what you want.

How to find out next available number

To find out the next ID after 3 that appears in your table, you should do

SELECT id FROM thetable WHERE id>3 ORDER BY id ASC LIMIT 1

This just considers IDs that are greater than 3, in ascending order, and then takes the first one on that list. If it returns you one result, then that's the next one used in the table; if it doesn't return a result at all, then the ID you gave it was already the highest one in the table (or, strictly speaking, at least as high as the highest one in the table).

How to find the next available integer in MySQL table using PHP

SELECT t1.id+1 AS MISSING_ID
FROM the_table AS t1
LEFT JOIN the_table AS t2 ON t1.id+1 = t2.id
WHERE t2.id IS NULL
ORDER BY t1.id LIMIT 1;

I made a fiddle: http://sqlfiddle.com/#!2/4d14d/2

SQL Query to retrieve next available ID from table

Maybe I'm missing something in your question, but it seems that this should do it:

SELECT CONCAT('STK', 
              LPAD(MAX(SUBSTRING(ticket_id, 4)) + 1, 
                   10, 
                   '0')
             ) 
FROM sma_support_tickets;

---

Related Topics