MySQL find ID that does not exist

Recently, someone in the group asked how to find an ID that does not exist in the MySQL table (id is self-increment, or can be continuous)

The first method:

Select Bewin_id,a from

(
Select bewin_id,1 as a from (select bewin_id from C_userinfo_his ORDER by bewin_id ASC) T where is exists (select 1 from C_userinfo_his where bewin_id=t.bewin_id-1)
Union
Select bewin_id,2 as a from (select bewin_id from C_userinfo_his ORDER by bewin_id ASC) T where is exists (select 1 from C_userinfo_his where bewin_id=t.bewin_id+1)

) T ORDER by bewin_id

Explanation under Select bewin_id,1 as a from (select bewin_id from C_userinfo_his ORDER by bewin_id ASC) T where NOT exists (select 1 fr Om c_userinfo_his where bewin_id=t.bewin_id-1)

This SQL ID is missing the ID of the previous ID (a bit of a mouthful, for example, the ID of 7 is found that 6 is not present in this table)

Select bewin_id,2 as a from (select bewin_id from C_userinfo_his ORDER by bewin_id ASC) T where is exists (select 1 from C_userinfo_his where bewin_id=t.bewin_id+1)

This SQL to find the ID is missing the ID of the last ID (for example, the ID of 7 is found that 8 is not present in this table)

So look at the results.

You can directly know 456 and 1213 and so on are not exist, the above has a disadvantage is to go to the number of their own

The second method of

Create a table (C_userinfo_his_test) with only ID and another field test, and the ID is contiguous, the maximum ID is select MAX (bewin_id) from C_userinfo_his, This gets a complete ID (insert INTO c_userinfo_his_test (' 1 '), which makes bewin_id equal to Max (bewin_id)),

Then it is the execution of select bewin_id from C_userinfo_his_tset where bewin_id not in (select bewin_id from C_userinfo_his)

This statement will give you the desired result (the test is wrong here)

It's the same as before.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL find ID that does not exist