[Resolved] Need Clarification About INT, BIGINT Column Types

[Volitics]

I need clarification about the INT and BIGINT column types. For some reason either I'm just not reading it right (which unfortunately is a distinct possibility) or the MySQL Manual is not real clear.

The MySQL Manual says that the signed range for the INT column type is from -2147483648 to 2147483647. The MySQL Manual also says that the signed range for the BIGINT column type is from -9223372036854775808 to 9223372036854775807.

My question is for the INT column type do they mean the maximum storage capacity is a total of ten (10) digits with a maximum value of 2147483647 OR do they mean 2147483647 individual digits. In other words, if they mean the latter then that would be an extremely long string of digits.

My question is the same for the BIGINT column type. Do they mean the maximum value is the nineteen (19) digit number 9223372036854775807 or do they mean the column can hold a ga-zillion digits?

The reason I ask is that I am storing reference numbers in a column. The reference number consists of the date plus three additional digits. A typical number for my table column would look something like:

20040105002

which is eleven digits.

I would appreciate any clarification that anyone could provide.

Thanks.

[ahundiak]

An INT is acutally a 32 bit number (4 bytes) while a biginit is a 64 bit number. So you won't be able to store 20040105002 in an int field.

If you really need to use reference numbers in this format then you would probably be better off in the long run if you just use a 11 char field. I know it uses more storage space but you won't run into any number-character translation issues.

[Volitics]

ahundiak

Thank you for responding to my problem.

What I'm using the reference numbers for is to store articles. Probably there would be no more than maybe a couple of hundred articles or rows in the table at any given time.

The reason that I was leaning toward an integer storage column is that my program uses this number to sort the rows when extracting them from the database.

The script typically extracts the rows and sorts them in descending order. I was thinking that it might need an integer or number for the sorting to always work properly.

If I just use a char column type would I run into any problems with the column sorting?

Thanks.

[LordShryku]

If you're going to use an auto_increment, you need an int type field. To answer your original question, the INT type has a max value of 2147483647. So your first assumtion was correct.

[Volitics]

It's not an auto_increment. The first eight numbers stand for the date the article is to be published. The last three numbers are for the order in which the articles are to be displayed.

For example, if there were five articles to be published on January 10, 2004 each of the articles would have an individual number, 001, 002, 003, 004, 005.

So the five numbers would look like:

20040110001
20040110002
20040110003
20040110004
20040110005

What's got me worried is if I use a char column type then PHP might recognize the Reference number as a string instead of a number and not sort them right.

I guess the best way to find out is to change the column type to char and see if it works ok.

[LordShryku]

Bigint should be fine. Though, I really don't see why you string the date and the order in one number. You could seperate them, and use a date or timestamp field, then a int field

[Volitics]

LordShryku, ahundiak

As always, I appreciate your help with my problem(s).

Thanks again.

[ahundiak]

Feel free the mark the thread as being resolved if your questions have indeed been answered.

BTW, as long as the char string is fixed length then sorting is not a problem.