Page 1 of 1

Simple INT data type question

Posted: Thu Oct 26, 2006 7:18 pm
by alex.barylski
int(10)

I've always wondered...does that mean an integer of 11 bytes wide? Typically intergers are 4 bytes and possibly 8 on newer machines...I use that type for my PKID's and I wonder how many are allowed with such a number?

Is it the 4 billion mark of an unsigned 4 byte integer or much much greater? :P

Posted: Thu Oct 26, 2006 8:45 pm
by feyd
10 characters when written as a string. For how much space the type will use, take a look at the database's documentation. They typically list it.

Posted: Fri Oct 27, 2006 3:13 am
by volka
feyd wrote:take a look at the database's documentation
In this case http://dev.mysql.com/doc/refman/5.0/en/ ... types.html

Posted: Fri Oct 27, 2006 1:02 pm
by alex.barylski
Ok thanks vodka, thats what I thought...

But...why then does PMA(phpMyAdmin) by default create INT(11) what is the point of the 11 then? Can I remove that 11 in a generic SQL statement?

Posted: Fri Oct 27, 2006 1:05 pm
by volka
The nick is volka, Hooky.

Posted: Fri Oct 27, 2006 1:15 pm
by feyd
As I already said, it's the number of characters used when the number is written as a string. In the case of signed numerics, that includes the negative too.

Posted: Fri Oct 27, 2006 2:19 pm
by alex.barylski
volka wrote:The nick is volka, Hooky.
LOL. Nice...sorry, it's a typo...someone rearranged my keys :P

Posted: Fri Oct 27, 2006 6:43 pm
by RobertGonzalez
Professionals don't need to see the keys to know what keys they press :twisted:.

I believe there is a default field size for int() fields in mysql, and I believe it defaults to 10. I could be way wrong on this, but I think that is how it works.

Posted: Fri Oct 27, 2006 10:20 pm
by alex.barylski
Not critically important...I just never bothered to read up on that...ummm...feature? Why you would want to represent an integer...as a series of characters...is beyond me :?

Why not just use VARCHAR for you PKID if thats the case? I assume SQL engines ensure your INT(11) never accepts any alpha characters?

Meh...no biggie...just confusing

Posted: Fri Oct 27, 2006 10:30 pm
by feyd
Hockey wrote:Not critically important...I just never bothered to read up on that...ummm...feature? Why you would want to represent an integer...as a series of characters...is beyond me :?
Because all data is represented by either a string or null when returned?
Hockey wrote:Why not just use VARCHAR for you PKID if thats the case? I assume SQL engines ensure your INT(11) never accepts any alpha characters?
I think you're confusing storage versus result data.

Posted: Fri Oct 27, 2006 10:56 pm
by alex.barylski
I'm still missing something...MySQL returns all data as strings or NULL? So the INT(10) is basically saying when you pull the integer from the database (I assume as 4 bytes?) it's converted into a string representation so it's not bound to any physical architecture? So if I used INT(2)...the result data, despite being 32 bits in length...would only return 2 number characters? So I might have the the actual number: 1,354,467,635 stored but when returning results I would only get 35???

Hmm...ok...makes sense why the number 10 then for an INT...32 bits...4 billion...10 sigfigs...

Am I way off or is that the gist of the idea? If I am correct...not sure why I would want to specify that in SQL...???

Seems more logical to handle precision, etc of a number using sprintf or similar...but whatever :P

Posted: Fri Oct 27, 2006 11:43 pm
by feyd
That's the basic idea, however the database will likely not let you store a number outside the boundaries of your own creation. In all likelihood, the database would optimized INT(2) to TINYINT(2), which only uses one byte of storage.

Posted: Sat Oct 28, 2006 2:39 am
by matthijs
I had the same confusion yesterday. The thing is you always see INT(2) or VARCHAR(50) being used. I always assumed that that meant how much characters are allowed/stored. But the number between brackets does not mean the same for integers as for strings (varchar etc). From the manual:
http://dev.mysql.com/doc/refman/5.0/en/ ... types.html
Another extension is supported by MySQL for optionally specifying the display width of an integer value in parentheses following the base keyword for the type (for example, INT(4)). This optional display width specification is used to left-pad the display of values having a width less than the width specified for the column.

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column.

When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, because in these cases MySQL assumes that the data fits into the original column width.
and from http://dev.mysql.com/doc/refman/5.0/en/char.html
The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.
That's something I didn't know.

Posted: Sat Oct 28, 2006 3:01 am
by alex.barylski
I knew that VARCHAR was setting the number of characters I wanted to store...it just seemed obvious...but I never paid attention to the INT(10)...as it's pointless for me personally...I'd do all formatting and such inside PHP...

Nice to know...as useless as that feature will likely be for me personally :P

Silly MySQL developers...trix are for kids :)

Posted: Sat Oct 28, 2006 9:23 am
by RobertGonzalez
Thanks matthijs, that was something that was not clear to me until just a few a moments ago.