Hello.
On Tue 2002-08-20 at 13:18:04 -0500, [EMAIL PROTECTED] wrote:
> Through your MySQL documentation, I wasn't able to find a function that I
> think could be useful. If you DELETE a row with an INT AUTO_INCREMENT field,
> it will still be able to add the next highest value to this column on an
> INSERT (ex. If after INSERTing rows, you DELETE a row with a value of five
> in its INT AUTO_INCREMENT column and the highest value for this particular
> INT column is currently six, your next INSERTed row will have a value of
> seven). This is very troubling for me, since at some point this INT will
> reach it's limit with 'holes' within the table.
Simply change to a bigger column type. With BIGINT and e.g. 100.000
INSERTs per second, you run out of numbers in roughly 3 million years.
Should not become a problem. ;-)
> I think a function to find the lowest hole in a specified table
> would be a useful addition to further upgrades. Thank you for your
> time and patience.
No separate function needed:
SELECT used.id+1
FROM mytable AS used LEFT JOIN mytable AS cmp ON cmp.id=used.id+1
WHERE ISNULL(cmp.id)
LIMIT 1
Did not test it for speed, though.
Regards,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
