How to query the minimum available id value in the Mysql table

Today, when I was looking at the laboratory projects, I encountered a "tricky" problem for me. In fact, it was because I was too stupid. Throw out the sql statement first

// This statement cannot get the correct query result when id is not 1.
select min(id+1) from oslist c where not exists (select id from oslist where id = c.id+1);

When I first saw this query statement, I was completely confused, probably because I hadn’t touched SQL for a long time.

1 exists syntax

In sql syntax, exists is used to filter results. During the actual execution process, the EXISTS statement loops the outer table and queries the inner table each time the loop is repeated. Substitute the records of the outer table into the subquery one by one. If the subquery result set is empty, it means it does not exist; otherwise, it exists.

It should be noted here that substituting the records of the outer table into the subquery is only to see whether the query result is empty, rather than making a substantive value comparison.

Let’s take an example:

If the id in the table oslist is 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 14, 15. The SQL statement at the beginning of the article is used here, using self-connection.

Then the query process is as follows:

Take id 1 and check whether there is id = 2 in oslist? , if it exists, it will not be included in the result set;
Take id 2 and check whether there is id = 3 in oslist? , if it exists, it will not be included in the result set;
Take id 3 and check whether there is id = 4 in oslist? , if it exists, it will not be included in the result set;
Take id 4 and check whether there is id = 5 in oslist? , if it exists, it will not be included in the result set;
Take id 5 and check whether there is id = 6 in oslist? , if it exists, it will not be included in the result set;
Take id 6 and check whether there is id = 7 in oslist? , if it exists, it will not be included in the result set;
Take id 7 and check whether there is id = 8 in oslist? , if it exists, it will not be included in the result set;
Take id 8 and check whether there is id = 9 in oslist? , if it exists, it will not be included in the result set;
Take id 9 and check whether there is id = 10 in oslist? , if it exists, it will not be included in the result set;
Take id 10 and check whether there is id = 11 in oslist? , if it exists, it will not be included in the result set;
Take id 11 and check whether there is id = 12 in oslist? , if it does not exist, it will be included in the result set;
Take id 14 and check whether there is id = 15 in oslist? , if it exists, it will not be included in the result set;
Take id 15 and check whether there is id = 16 in oslist? , if it does not exist, it will be included in the result set;
Take the smallest value between (11+1) and (15+1) and return the result.

End the query.

ps: Delete the data with the smallest id in MySQL

//Method 1
 delete from tablenamewhere id in (select id from (select min(id) id from tablenamec1) t1); 
//Method 2
delete from table name order by id asc limit 1;

Summarize

The above is the method that I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!