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.

Please could someone explain to me how the MySQL integer types and lengths work as it is somewhat confusing.

Which different integer types and lengths should I use for each of the following ranges to keep them as efficient as possible?

0 - 1
0 - 700,000
0 - 9,999,999,999

What is the difference between TINYINT(5), SMALLINT(5) and INT(5), are they the same?

share|improve this question
    
    
Do you think I didn't look at that first? Does that answer my question? No. –  Alasdair Mar 30 '13 at 11:37
2  
I don't understand if you go through this link then why you asked that question ? In a single answer to your question your comment changed your question completely ...For this question Understanding MySQL Integer Types? only that link can be useful resource available. –  rahularyansharma Mar 30 '13 at 11:41
    
If I choose BOOLEAN it defaults to TINYINT(1) but only allows 0 or 1, which is not the same as 0-255. And then all the integer sizes allow for a length... so obviously it is not as simple as that table on the MySQL site. –  Alasdair Mar 30 '13 at 11:45
add comment

migrated from stackoverflow.com Mar 30 '13 at 22:39

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote accepted

For 0-1, a bit is perfect.

For 0 - 7,00,000 you can have UNSIGNED MEDIUMINT

and for the last one UNSIGNED INT will work.


Refer this page for information about the ranges they provide.

Type    Storage Minimum Value   Maximum Value
    (Bytes) (Signed/Unsigned)   Signed/Unsigned)
TINYINT 1   -128    127
        0   255
SMALLINT    2   -32768  32767
        0   65535
MEDIUMINT   3   -8388608    8388607
        0   16777215
INT 4   -2147483648 2147483647
        0   4294967295
BIGINT  8   -9223372036854775808    9223372036854775807
        0   18446744073709551615

There is no difference if you use INT(5) or INT(10) or even INT(1). The number in parenthesis is just width to display when fetching results.


This link will help you further.

share|improve this answer
    
Yes, yes I know that. But what is the difference between MEDIUMINT(5) and TINYINT(5), as both have 5 specified as length? –  Alasdair Mar 30 '13 at 11:38
    
@Alasdair Check the edit. Also, MEDIUMINT and TINYINT with any number in parenthesis will still have the same ranges as in the link above. Just their default display width will be set to 5. –  hjpotter92 Mar 30 '13 at 11:47
    
OK, that explains it. Thank you. –  Alasdair Mar 30 '13 at 11:54
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.