how do you run an insert query for the next available row
Posted 11 April 2006 - 12:01 AM
in my database/table i have the normal primary key with auto increment
well say i had 10 records keys 1-10 when i delete a record say number 7 i would have the rows 1,2,3,4,5,6,8,9,10 then when i add a new insert it inserts it as number 11 how can i do it so i inserts it into the next available key which would be 7 in this case
thanks in advance and sorry if the explanation isnt great
nearly forgot to say i need to be able to do it without using say.. WHERE table_id = 7
or i could if there is a way to find the next avail key first
Posted 11 April 2006 - 01:20 AM
Posted 11 April 2006 - 01:43 PM
As an aside, InnoDB tables do re-use "holes" in auto_increment fields, AFAIK, while MyISAM tables do not.
Posted 11 April 2006 - 08:06 PM
Unfortunately I've never really found a good way to fill in holes. The only thing I've ever even thought of is maintaining a table of integers - 1..max_records; and then LEFT JOIN + ORDER BY + LIMIT to find the smallest missing ids. I never needed it enough to implement something so ugly though.
Posted 11 April 2006 - 09:44 PM
Posted 11 April 2006 - 11:36 PM
The two solutions I've considered are to fill in holes in your data, or create a nightly index table that's numbered sequentially and links to the primary key of the target table. With either of those, it's easy to generate random numbers in a given range and select them. The problem is keeping the data clean, and the overhead to do it.
There really is no perfect way that I've ever seen to get random rows in mysql. The frustrating part is that it seems easy to build support for it into mysql itself. Maybe someday.
Posted 12 April 2006 - 03:32 AM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users