Jump to content

Archived

This topic is now archived and is closed to further replies.

neller

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.