Suppose I have a field in a table say tb_example named id. This id is with AUTO_INCREMENT property. So in every new insert it goes like 1,2,3,..... and so on.
Now suppose there are 10 rows of data with id 1-10. Somebody deleted the data where id=5. Now for the next insert I want to insert data in position 5 NOT IN 11 Also I want to get the unused id before insertion. That means I want to get the unused id and insert the next data using query like
"INSERT INTO tb_example .......... WHERE id=".$unused_id;
Is it possible with any simple function or process to get that unused id? Also is it possible to get unused id inside a range? Like I want to know if any unused id exists inside 100 to 200? Or do I need to find that id with logical coding? Please help....
the lowest friction approach is probably to have a separate table recording reusable IDs that you insert into each time a record is deleted (ideally with a trigger). when you want to insert a new record, look aside at this table and see if there are IDs to reuse. if so, use them; if not, just insert normally. this should also allow you to easily find an unused ID in a range. this should be fast provided the column in the look-aside table storing each recyclable ID is indexed.