Is there a difference in using INT(1) vs TINYINT(1) in MySQL?

mysql types integer

32,757

Solution 1

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

Solution 2

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)

Solution 3

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

32,757

animuson

I work for Stack Overflow as a Senior Product Support Specialist.

Updated on August 01, 2022

Comments

animuson almost 2 years

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?
animuson over 12 years

So even if I define INT(1) to only allow one character, it will still use up 4 bytes no matter what?
Phil over 12 years

@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
animuson over 12 years

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?
glglgl almost 11 years

@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.
Rowan Freeman over 10 years

The last link is broken.
Abdul Mannan almost 8 years

if you are using phpmyadmin,just hover over the select dropdown when selecting the type and it will show all these details automatically. :)
albanx about 7 years

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
Fr0zenFyr about 5 years

Here you go with another link that perhaps could be a replacement for the one you removed: thisinterestsme.com/mysql-numbers-in-brackets. There's a reason link only answers are discouraged (looking at your rep, I understand you had figured that long ago). Anyway, just wanted to add that the connectors may play a role in the choice of (display width). For instance, Connector/J (i guess Connector/Net also) will attempt to convert the value in TINYINT(1) to a boolean true/false or 0/1 unless you use tinyInt1isBit=false in connection string. Crucial for a developer! :)
Fr0zenFyr about 5 years

Yep! I can confirm that it is the case with Connector/Net as well and the workaround is to use TreatTinyAsBoolean=false in connection string, as per docs.
HoldOffHunger over 3 years

This answer is a 100% copy/paste from a https://dev.MySQL.com webpage, with no additions, addendums, annotations, or attributions.