Menu
What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?
In what cases should these be used?
474
The only difference is the range of the type. INT is a 32-bit long while BIGINT is 64-bit long, therefore it can store much larger numbers like 123456789123456789 (which cannot be stored as INT ).
Both data types are stored as signed binary integers. INTEGER values have 32 bits and can represent whole numbers from –2 31–1 through 2 31–1. SMALLINT values have only 16 bits. They can represent whole numbers from –32,767 through 32,767.
MEDIUMINT − A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits.
They take up different amounts of space and they have different ranges of acceptable values.
Here are the sizes and ranges of values for SQL Server, other RDBMSes have similar documentation:
NUMBER datatype really)Turns out they all use the same specification (with a few minor exceptions noted below) but support various combinations of those types (Oracle not included because it has just a NUMBER datatype, see the above link):
| SQL Server MySQL Postgres DB2 --------------------------------------------------- tinyint | X X smallint | X X X X mediumint | X int/integer | X X X X bigint | X X X X And they support the same value ranges (with one exception below) and all have the same storage requirements:
| Bytes Range (signed) Range (unsigned) -------------------------------------------------------------------------------------------- tinyint | 1 byte -128 to 127 0 to 255 smallint | 2 bytes -32768 to 32767 0 to 65535 mediumint | 3 bytes -8388608 to 8388607 0 to 16777215 int/integer | 4 bytes -2147483648 to 2147483647 0 to 4294967295 bigint | 8 bytes -9223372036854775808 to 9223372036854775807 0 to 18446744073709551615 The "unsigned" types are only available in MySQL, and the rest just use the signed ranges, with one notable exception: tinyint in SQL Server is unsigned and has a value range of 0 to 255
117
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
