46

I'm under the assumption that INT(1) is the exact same thing as TINYINT(1) but I really have no idea. Whenever I've had values that can only be a single integer (e.g. a value 0-9), I've always just used INT(1) to say it's an integer and it will only be one character, which I assume means that it could only be a value 0 through 9 (please explain this to me if I'm wrong). I've always just ignored the other types of INT that you can cast the number as. I'm no MySQL savvy and tend to avoid the more complicated things you can do with it.

So my question, is there any difference between the various integer types INT, TINYINT, SMALLINT, MEDIUMINT, and BIGINT if you define a length of 1 for each type;? If not, should I use them anyways (I can see using them for more semantic meaning, TINYINT being more specific than just INT)? If so, could I easily (and/or should I) just go through my database and change all my INT(1) fields to TINYINT(1) fields?

Improve this question
0

3 Answers 3

Reset to default
52

Here you'll understand it in a better way!

tinyint: 1 byte, -128 to +127 / 0 to 255 (unsigned) smallint: 2 bytes, -32,768 to +32,767 / 0 to 65,535 (unsigned) mediumint: 3 bytes, -8,388,608 to 8,388,607 / 0 to 16,777,215 (unsigned) int/integer: 4 bytes, -2,147,483,648 to +2,147,483,647 / 0 to 4,294,967,295 (unsigned) bigint: 8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 / 0 to 18,446,744,073,709,551,615 (unsigned)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

if you are using phpmyadmin,just hover over the select dropdown when selecting the type and it will show all these details automatically. :)
This answer is wrong because the user is trying to get the difference when the size is define, this is just showing the range of the field type
This answer is a 100% copy/paste from a https://dev.MySQL.com webpage, with no additions, addendums, annotations, or attributions.
47

The number in parentheses for integer column types is the "display width". This does not effect the storage requirements as they are pre-defined.

Further reading

Improve this answer

7 Comments

So even if I define INT(1) to only allow one character, it will still use up 4 bytes no matter what?
@animuson INT(1) does not allow only one character. It sets the display width for that column in result sets. Read the last link above, it explains it pretty well
Oh I see. Guess I should have continued on past link 2. o.o Also, would it harm anything if I merely changed a field from INT to TINYINT if all the values are 0s and 1s or should I just leave it alone?
@animuson That depends on the total sizes. If it'll save you 1% of the space consumed, leave it alone. If it saves you 20%, consider if it is really needed. If it saves you 50%, do the change.
The last link is broken.
|
8

To summarize the accepted answered :

The number in parentheses indicates the *number of characters to display that field*, **not** the storage size of the field.

But if you want to know the storage size, you should check the MySQL source documents.

Source: MySQL Docs: Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

Direct quote from source documentation :

TINYINT : 1 byte, -128 to 127 signed, 0 to 255 unsigned

SMALLINT : 2 bytes, -32768 to 32767 signed, 0 to 65535 unsigned

MEDIUMINT : 3 bytes, -8388608 to 8388607 signed, 0 to 16777215 unsigned

INT : 4 bytes, -2147483648 to 2147483647 signed, 0 to 4294967295 unsigned

BIGINT : 8 bytes, -2^63 to 2^63-1 signed, 0 to 2^64-1 unsigned

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.