Search for the next available id in MySQL

Question

I need to find the next available identifier (if there is 5 data in the database, I should get the next available insertion location, which is 6) in the MySQL database. How can i do this? I used MAX(id) , but when I delete several rows from the database, it still retains the old maximum value, which it did not update.

Answer 1

I do not think that you can be sure of id next , because someone can insert a new line right after you ask for the next identifier. You will at least need a transaction, and if I'm not mistaken, you can only get the actual identifier used after , at least this is the usual way to handle it - see http://dev.mysql.com/doc/refman /5.0/en/getting-unique-id.html

Answer 2

Update 2014-12-05: I do not recommend this approach for the reasons stated in Simon's answer (accepted), as well as in Diego's comment. Please use the request below at your own risk.

The shortest I found on the mysql developer site:

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

remember, if you have few databases with the same tables, you must also specify the database name, for example:

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

Answer 3

In addition to Lukasz Lysik, the answer is a LEFT-JOIN SQL type.
As I understand it, if there is id: 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 this helps some visitors, although the mail is pretty old.

Answer 4

Given what you said in the comment:

my id coloumn is an automatic increment, I have to get the identifier and convert it to another database. Therefore, I need to get the following identifier before the reason code is inserted into which the converted code is inserted.

There is a way to do what you ask, namely: ask the table what the next inserted row identifier will be before you insert:

 SHOW TABLE STATUS WHERE name = "myTable" 

in this result set the field "Auto_increment" will appear, in which the next value of the automatic growth is indicated.

Answer 5

As I understand it, if there is id: 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 

Answer 6

you said:

my id coloumn is an automatic increment that I have to get the identifier and convert it to another database. Therefore, I need to get the next id before inserting, and the converted code will also be inserted.

what you ask 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 receive 6, and their updates or inserts will step over each other.

I suggest that instead of an INSERT in the table, get the value of auto_increment using LAST_INSERT_ID() , and then an UPDATE line to set any value that you have, which depends on the value of auto_increment.

Answer 7

If you want to select the first space, 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 a syntax version of ANSI 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 an optimizer error in MySQL .

Answer 8

If you really want to calculate the next insert key before inserting a row (which, in my opinion, is not a good idea), then I would suggest using the maximum current id plus one:

 SELECT MAX(id) + 1 FROM table 

But I would suggest that you let MySQL create the identifier itself (using the auto-increment column) and use LAST_INSERT_ID() to get it from the DBMS. To do this, use the transaction in which you are inserting and then request the identifier, for example:

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

Now the returned number contains only one column, which contains the identifier of the newly created row.

Answer 9

One way to do this is to automatically increase the index. Then your SQL statement simply points to NULL, and then the SQL parser does the rest for you.

 INSERT INTO foo VALUES (null); 

Answer 10

If it is used together to INSERT a new entry, you can use something like this.

(You indicated in your comments that the identifier automatically increases, and another table needs the following identifier + 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 pseudo code, but you get the idea

Answer 11

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

@Eimantas already gave a better answer, but the solution will not work if you have two or more tables under the same name under the same server.

I have a slightly modified @Eimantas answer to solve the above problem.

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

Answer 12

The problem with many solutions is that they only find the next "GAP", ignoring if "1" is available, or if there are no lines, they will return NULL as the next "GAP".

The following will not only find the next available space, but also consider if the first available number is 1:

 SELECT CASE WHEN MIN(MyID) IS NULL OR MIN(MyID)>1 -- return 1 if it 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 

Answer 13

This worked for me (MySQL 5.5) and also solved the problem of the "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 earlier, these types of queries are very slow, at least in MySQL.

Answer 14

 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 

Answer 15

 <?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']; } } ?>