smallint,mediumint,int type length?

Posted on 2008-11-07
Medium Priority
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
  • 2

Accepted Solution

openshac earned 800 total points
ID: 22904798
From: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

 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 61

Assisted Solution

by:Kevin Cross
Kevin Cross earned 400 total points
ID: 22904814
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 61

Expert Comment

by:Kevin Cross
ID: 22904820
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

Louis01 earned 800 total points
ID: 22904869
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

621 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