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?

  • 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

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.

| 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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