MySQL incriment/count problem

[fishhyren]

Lets say I have some ID numbers that I want to be able to be recycled--

1, 2,3,4,5,6,7 and I delete 2, 3, - well with a select count(...) statement I'd get 5 (which is already used)-

If I did a select Max(...) and added one I'd get 8, but that doesn't help me recycle the numbers-

Is there an easier way besides using a while loop to find an unused spot (say 2 and 3)? (maybe an SQL command?)

Thanks for the help!
-Scott

[LordShryku]

Well, food for thought, considering MySQL doesn't support subqueries(!). Provided "1" exists, this should work....

SELECT    (min(t.id)+1) AS emptyNum
FROM      table t
LEFT JOIN table ta
ON        (t.id+1) = ta.id
WHERE     ta.id IS NULL;

[pecoes]

I'm not clever enough to understand LordShryku's code, but I also don't understand why you bother with reassigning abandoned ids. Missing information is information too. It's helpful to know that a person has opted out, a product is out of stock or whatever.
Besides assigning a value to an id column is a pretty bad idea. It's much safer to declare it as "auto_increment" and never-ever assign a value to it! And I wouldn't worry about running out of numbers either: the maximum value for an "UNSIGNED INT" is 4294967295.