smallint,mediumint,int type length?

Posted on 2008-11-07
Last Modified: 2013-12-13
in my table

i created columns like below

city_id smallint(11) unsigned,
user_id mediumint (11) unsigned,
message_id int (11) unsigned,

inside of parantheses what means 11 if i write something less or big.

if i created
city_id smallint(5) unsigned,
user_id mediumint (5) unsigned,
message_id int (5) unsigned,

is there will be any effect in memory or performance. Shortly what is different.
i readed in mysql site but do not understant.

Question by:phparmy
    LVL 6

    Accepted Solution


     Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

    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. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

    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.
    LVL 59

    Assisted Solution

    by:Kevin Cross
    Hello phparmy,

    The parameter is the amount of padding to give to the field.  So if you specify 5, then only numbers lower than 5 digits will get padded.  Numbers over that will be displayed as is from my understanding.  Doesn't impact the range of numbers used or memory needed to store the values.


    LVL 59

    Expert Comment

    by:Kevin Cross
    Ah, openshac linked it for you too.  The text in that post is from the linked manual for MySQL.  Hopefully that explains it.
    LVL 11

    Assisted Solution

    The parenthesis () is simply a display width. A smallint would always be 2 bytes in the range -32768 to 32767.
    A smallint(3) would display the number 99 as ' 99' or '099' depending on whether you used the optional extension attribute ZEROFILL. In the same example, 1234 would always be displayed as '1234'.
    1234 in an type defined as smallint(11) would display '       1234' or '00000001234'.
    Hope this helps

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now