auto-increment limit? 2

[flubbard]

I have a database which holds the name and location of reports stored on a system. Each one has an auto incrementing primary key. Throughout the day, a crontab entry "freshens" the list by basically adding new report entries to the database. At night, I flush the database by deleting all of the entries and rebuilding the table. This ensures database integrity so that I don't have to worry about a report having been renamed, moved, or deleted.

There are now a bunch of reports in the database and being that it is flushed daily, the primary keys are getting high. Is there any problem if the primary key increments too high (is there an upper limit where I will get an error)? Does it roll over? Is there a way to reset it?

I appreciate any help you can offer.

 

[Bastien]

truncate the table instead of deleting, it will reset the auto-increment

truncate tablename

as to the limit, its based on the integer, so if you have an eleven character int value (99,999,999,999) would be the max and it should roll over whe the limit is reached

Bastien

Cat, the other other white meat

 

[skiflyer]

Truncate is what I'd suggest also... but also specifically to reset an auto-increment value...

ALTER TABLE `table_name` AUTO_INCREMENT =1

 

[flubbard]

Thanks for the posts. Looking at the manual, Truncate seems to be what I should have been doing from the start. I didn't know you could reset the auto_increment, though, so I gave a star to skiflyer too (since that was one of my initial questions).

Thanks for the help!

 

[sleipnir214]

To add to part of Bastien's post...

The number at which your auto_increment column rolls over depends on the type of column being incremented.

As a first piece of advice, I recommend always using an UNSIGNED integer with auto_increment columns. That almost doubles the largest number that can be stored in a column, and auto_increment doesn't generate negative numbers.

Then analyze how you're going to use a table with an auto_increment column, and use an appropriate bit-width of integer type for your auto_increment. You may not often need auto_increment numbers in the range of 1 to 18446744073709551615, but it's useful to know that an auto_increment on a column of type BIGINT UNSIGNED can get you there.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[r937]

bastien, there is no such thing as an eleven character integer

nor even an eleven digit integer

10 digits is as high as an integer can get, even if you say INT(99)

also, the auto_increment doesn't roll over, what happens is mysql just will not insert any more rows

you can confirm this yourself with a tinyint(4), try adding more than 127 rows



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Bastien]

sorry r937

too many damn db specs stuck in my head. sometimes i get them confused

Bastien

Cat, the other other white meat