Dave Crawford wrote:
>
> In regards to a auto_increment 'ID' column, is there a way
> for the Mysql server to assign the LOWEST available 'record'
> number (if one is available) instead of using the highest
> existing record + 1?
> For example if I have 20 records and I delete row 11, when
> I insert the next record assign it number 11 instead of 21.
> Is there an easier way to accomplish this other than polling
> a range of record numbers and seeing which rows come back NULL?
I would do this by adding a "Deleted" column to your table.
Instead of physically deleting rows you set the Deleted flag;
"update table set Deleted = 'Y' where ID = x".
When you wanted to insert a new record you first find the
lowest ID value;
"select min(ID) from table where Deleted = 'Y'
You then "insert" your new row;
"update table col1 = NewValue, col2 = NewValue ...
where ID = <value returned from select>"
This works fine for a single user application, but if there
is a possibility of concurrent updates you first read the
row and modify the where clause to include the old values;
"update table col1 = NewValue, col2 = NewValue ...
where ID = <value returned from select>,
col1 = OldValue, Col2 = OldValue ..."
Failure to do this can result in data loss!
Hope this helps...
--
If it ain't opinionated, it ain't Rich Teer.
