[mysql] What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?

What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?

In what cases should these be used?

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.

Those seem to be MySQL data types.

According to the documentation they take:

1. tinyint = 1 byte
2. smallint = 2 bytes
3. mediumint = 3 bytes
4. int = 4 bytes
5. bigint = 8 bytes

And, naturally, accept increasingly larger ranges of numbers.

The size of storage required and how big the numbers can be.

On SQL Server:

• tinyint 1 byte, 0 to 255
• smallint 2 bytes, -2¹⁵ (-32,768) to 2^15-1 (32,767)
• int 4 bytes, -2³¹ (-2,147,483,648) to 2^31-1 (2,147,483,647)
• bigint 8 bytes, -2⁶³ (-9,223,372,036,854,775,808) to 2^63-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.

The difference is the amount of memory allocated to each integer, and how large a number they each can store.

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;