Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In MySQL table definitions is there a different between INT(5) and SMALLINT(5)? Or do they both represent the same size?

share|improve this question
    
I find this very usefull, the lenght is only matter of visualization, i am not confused anymore, indeed changing a lot of fields to their respective and correct data type has made my database faster and more efficient –  user11077 Aug 23 '12 at 17:25
add comment

1 Answer

up vote 21 down vote accepted

No, they don't represent the same size that is allocated for the field type.

An int can be between -2147483648 and 2147483647 signed, or 0 and 4294967295 unsigned.

A smallint is between -32768 and 32767 signed, or 0 and 65535 unsigned.

The (5) represents the display width of the field. From the manual, it states:

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

The display width, from what I can tell, can be used to left-pad numbers that are less than the defined width. So 00322, instead of 322. TBH, I've never used it.

But it doesn't affect the storage size of the column. An int will take up more space than a smallint.

share|improve this answer
    
If a field hasn't got the ZEROFILL parameter, what is the display width for then? see dba.stackexchange.com/questions/29557/… –  rubo77 Nov 30 '12 at 0:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.