MySQL Forums
Forum List  »  Newbie

Re: renumbering autoincrement pk
Posted by: Ulf Wendel
Date: April 21, 2005 06:34AM

Ahoy!

Steve Ross wrote:
> I'm maintaining a list of images and eventually
> they will overflow the max value of the
> autoincrement primary key. I have a scavenger that

Aha... I tend to use integer unsigned auto_icrement primary key for such column. That gives me a range of 0 ... 4.294.967.295 (= 2^32 - 1). So, you have room for some 4 billion pictures. If you add a picture every second to your table it should last for the next 136 years.

If that's not enough, use bigint unsigned. That one gives you a range of 0 ... 18.446.744.073.709.551.615 (2^64 - 1). That's some trillion pictures which should last until the next ice age.

http://dev.mysql.com/doc/mysql/en/numeric-types.html

> The primary key is maintained only to preserve
> chronological order of entry.
>
> Is there a way to do this other than adding a
> timestamp?

A timestamp? MySQL timestamps currently have a resolution of a second. If you add more than one picture per second, there's no exact way to find the proper order of the pictures that have been added within a certain second. Switching to a timestamp does not make much sense unless you need the creation time for some other purpose.

Check the properties of timestamps on http://dev.mysql.com/doc/mysql/en/datetime.html (all: especially the difference in handling prior and past 4.1 is of general interest).

Ulf

Options: ReplyQuote


Subject
Written By
Posted
April 20, 2005 02:36PM
Re: renumbering autoincrement pk
April 21, 2005 06:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.