Simple INT data type question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Simple INT data type question

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

The nick is volka, Hooky.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post by alex.barylski »

volka wrote:The nick is volka, Hooky.
LOL. Nice...sorry, it's a typo...someone rearranged my keys :P
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Post 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.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post 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 :)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Thanks matthijs, that was something that was not clear to me until just a few a moments ago.
Post Reply