how do you run an insert query for the next available row

[neller]

title sounds a bit strange but wasnt sure how to put it but here goes


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

[AndyB]

In your example, the [u]next available record[/u] really is 11 regardless of how many lower numbered records have been deleted. Messing with auto-increment numbers is pointless and may well negate all the advantages of relational databases where a record number is used as a pointer/index to related data in another table. Just go with the flow and let MySQL take care of where records are written - it really knows best.

[fenway]

Agreed -- pretend that number can't be changed, and you'll be better off. As I've said many times before, you can present /generate a "proper" sequence for the output without messing around with DB internals.

As an aside, InnoDB tables do re-use "holes" in auto_increment fields, AFAIK, while MyISAM tables do not.

[wickning1]

Filling in holes is a lot more reasonable than re-indexing, I've thought about it myself quite a few times, when I needed a fast way to select random rows (big holes make that a real pain).

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.

[fenway]

If you only wanted one random row, [a href=\"http://jan.kneschke.de/projects/mysql/order-by-rand/\" target=\"_blank\"]this technique[/a] is the best. Otherwise, you either have to run that in a loop (i.e. SP) or maintain your own index.

[wickning1]

Yeah I've seen that technique before. With holes in your data it is not truly random, and even without holes, it has clustering problems when you adapt it for more than one row.

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.

[fenway]

Yeah, I've opted for the latter whenever I've encountered this issue. That is, whenever I delete a row in the main table, a insert a row into my index table with the appropriate FK UID, and then LEFT JOIN and check for NOT NULLs to ensure that I never get a "hole". You're right, though -- seems ridiculously easy to build into MySQL.