Whats the largest AutoNumber Type? 1

[d2g]

I need to know what to assign my autonumber column in my table. I need a datatype that can count to an almost unlimited amount.. In mysql what is the data type i should use for this autonumber field? And what will be its maximum limit.

thanks!

D2G

 

[nego78]

You can set type tu unsigned bigint which can have value of 18446744073709551615
Everything depends on your system and mysql vers

Read more about in mysql manual 11.2. Numeric Types

gry online

 

[d2g]

Ok, I have it set now to bigint, len = 11, unsigned, auto inc. Thats going to give me a total of 18446744073709551615 available?

thanks in advance!

D2G

 

[r937]

len=11 does not affect the size of the number a bigint can hold

bigints are bigints

what kind of application are you building that you need more than 2 billion rows (the size when using integer instead of bigint)

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

 

[d2g]

Its for a web application that will have several hundred users and everytime they connect the autonumber would increase, so want to make sure the program doesn't expire.

 

[r937]

quick calculation --

let's say a single user connects 5 times per minute, 24 hours a day

that's 720,000 connections per day for 100 users

if you use INTEGER, you will run out of numbers in approx 3000 days

to recap: if each of 100 users connects 5 times per minute, 24 hours per day, you will run out of numbers in approximately 8 years

i'd say that's a relatively low risk

if you use BIGINT, you double the storage space for the auto_increment, from 8 gigabytes to 16 gigabytes

just thinking out loud here ...

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

 

[d2g]

Thanks for the calculations Gota love the numbers.. I'll prob go with integer, but i really wanted to know for future reference.. Thanks!