Auto Increment Reset after 2147483647?

[dragon]

Using MySQL database, how can I get it to reset the auto increment for an int field after it hits 2147483647? I believe that it is unlikely that my database logs will hit that any time soon, but I did a quick test by inserting 99999999 and I got 2147483647 as an ID number. It didn't start recounting when I did an insert after that - it only said that there was a duplicate id. The quickest way that I could think of to get the database working again was to delete the table and recreate it. Is there another way to get auto increment to reset?

[GJay]

ALTER TABLE tablename AUTO_INCREMENT=0;
But it won't work if you have entries in the table.

If you want to re-number rows, and there are no dependencies, you can DROP and then ADD the id column, and it will assign numbers automatically, and remove any gaps in the sequence.

If you want to get rid of all the values in the table, and start the increment at 0, then TRUNCATE will drop and recreate the table, thus resetting the counter.

[raf]

why don't you just use a bigger datatype for this column? like BIGINT UNSIGNED which will allow you to go up till 18446744073709551615.

[dragon]

Thanks. I believe that for the one table that I'm (slightly) concerned about, dropping and then adding the id column should be fine.