The method of minimum available id value in Mysql query table

Today, when I was watching the project in the lab, I encountered a problem that made me "thorny". In fact, I was too stupid. Throw out the sql statement first.

/ / this statement does not get the correct query result when id does not have 1. Select min (id+1) from oslist c where not exists (select id from oslist where id = c.id+1)

At first, I saw this query statement, and I was completely confused. I probably haven't touched sql for a long time.

1 exists syntax

In sql syntax, exists is used to filter results. In the actual execution process, the exists statement is a loop loop for the external table, and each loop loop queries the inner table. Replace the external records into the subquery one by one, if the subquery result set is empty, it does not exist, otherwise, it does.

It should be noted here that the external record is substituted into the subquery, only to see whether the query result is empty, rather than making a substantial value comparison.

For example:

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

Then the query process is as follows:

Take id as 1 and see if there is an id = 2 in oslist? If it exists, it is not included in the result set

Take id as 2 to see if there is an id = 3 in oslist. If it exists, it is not included in the result set

Take id as 3 to see if there is an id = 4 in oslist. If it exists, it is not included in the result set

Take id as 4 to see if there is an id = 5 in oslist. If it exists, it is not included in the result set

Take id as 5 to see if there is an id = 6 in oslist. If it exists, it is not included in the result set

Take id as 6 to see if there is an id = 7 in oslist. If it exists, it is not included in the result set

Take id as 7 to see if there is an id = 8 in oslist. If it exists, it is not included in the result set

Take id as 8 to see if there is an id = 9 in oslist. If it exists, it is not included in the result set

Take id as 9 to see if there is an id = 10 in oslist. If it exists, it is not included in the result set

Take id as 10 to see if there is an id = 11 in oslist. If it exists, it is not included in the result set

Take id as 11 to see if there is an id = 12 in oslist. Does not exist, it is included in the result set

Take id as 14 to see if there is an id = 15 in oslist. If it exists, it is not included in the result set

Take id as 15 to see if there is an id of 16 in oslist. Does not exist, it is included in the result set

Take the one with the lowest value between (11: 1) and (15: 1), and return the result.

End the query.

Delete data with minimum id in ps:MySQL

/ / method 1 delete from table name where id in (select id from (select min (id) id from table name C1) T1); / / method 2delete from table name order by id asc limit 1

Summary

The above is the method of the minimum available id value in the Mysql query table introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!