Insert the smallest possible unique integer of positrons in an atomic manner in MySql

Question

Say I have a table with a unique positive integer field. I currently have lines with id [1, 2, 3, 4, 5, 8, 12, 35]. Is there an insert request that I could write to assign the id field to the smallest unique positive number (in this case 6)? This would have to do this atomically so that there is no possibility of simultaneous insertion wiping each other or crashing.

Answer 1

I would use no to fill in the "missing" identifiers, but this should work:

Insert Into t (id)
  Select Coalesce( Min(t.id) + 1, 0 )
  From t
  Left Join t As t2 On ( t2.id = t.id + 1 )
  Where t2.id Is Null

id, id + 1 (Left Join), Min(id)+1 0, .