What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?
MysqlIntegerMysql Problem Overview
What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?
In what cases should these be used?
Mysql Solutions
Solution 1 - Mysql
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:
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
Solution 2 - Mysql
The size of storage required and how big the numbers can be.
On SQL Server:
tinyint1 byte, 0 to 255smallint2 bytes, -215 (-32,768) to 215-1 (32,767)int4 bytes, -231 (-2,147,483,648) to 231-1 (2,147,483,647)bigint8 bytes, -263 (-9,223,372,036,854,775,808) to 263-1 (9,223,372,036,854,775,807)
You can store the number 1 in all 4, but a bigint will use 8 bytes, while a tinyint will use 1 byte.
Solution 3 - Mysql
Those seem to be MySQL data types.
According to the documentation they take:
tinyint= 1 bytesmallint= 2 bytesmediumint= 3 bytesint= 4 bytesbigint= 8 bytes
And, naturally, accept increasingly larger ranges of numbers.
Solution 4 - Mysql
When it gets to real world usage of these datatypes, it is very important that you understand that using certain integer types could just be an overkill or under used. For example, using integer datatype for employeeCount in a table say employee could be an overkill since it supports a range of integer values from ~ negative 2 billion to positive 2 billion or zero to approximately 4 billion (unsigned). So, even if you consider one of the US biggest employer such as Walmart with roughly about 2.2 million employees using an integer datatype for the employeeCount column would be unnecessary. In such a case you use mediumint (that supports from 0 to 16 million (unsigned)) for example. Having said that if your range is expected to be unusually large you might consider bigint which as you can see from Daniel's notes supports a range larger than I care to decipher.
Solution 5 - Mysql
The difference is the amount of memory allocated to each integer, and how large a number they each can store.
Solution 6 - Mysql
Data type Range Storage
bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes
int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes
smallint -2^15 (-32,768) to 2^15-1 (32,767) 2 Bytes
tinyint 0 to 255 1 Byte
Example
The following example creates a table using the bigint, int, smallint, and tinyint data types. Values are inserted into each column and returned in the SELECT statement.
CREATE TABLE dbo.MyTable
(
MyBigIntColumn bigint
,MyIntColumn int
,MySmallIntColumn smallint
,MyTinyIntColumn tinyint
);
GO
INSERT INTO dbo.MyTable VALUES (9223372036854775807, 214483647,32767,255);
GO
SELECT MyBigIntColumn, MyIntColumn, MySmallIntColumn, MyTinyIntColumn
FROM dbo.MyTable;