How Shall We Store it? – Datatypes - Database Design Manual: Using MySQL for Windows - page 20

Numeric Types

A numeric datatype is used to store numbers. There are normally specific functions that
you can perform on numbers and on most numeric datatypes.

Integers
An integer is an exact whole number. For example, the numbers 1 to 10 are all integers. If we
are creating unique IDs for rows in our database, the ID field is usually an integer, which
starts at 1 and gets bigger by one for each new row added. If you add, subtract and multiply
integers you will get an integer as an answer.
Integers normally range from a negative number, through zero to a positive number.
There are several datatypes that are used to store integers, depending on the range; the
greater the range, the larger the amount of storage that it takes to keep the integer in a table.
Table 3.1, adapted from the online MySQL manual, shows the approximate range that
each of the integer datatypes can represent.

Table 3.1 Integer datatype ranges.

Datatype From To Storage in bytes per row

TINYINT -128 +127 1

SMALLINT -32 thousand +32 thousand 2

MEDIUMINT -8.3 million +8.3 million 3

INT -2 billion (10 12 ) +2 billion 4

BIGINT -9 trillion (10 18 ) +9 trillion 8

If you look at Table 3.1 you can see that as we move down the table, the range of numbers
becomes vast, with little change in the amount of storage that is required. If you are using
an integer to store a primary key, the standard INT type will probably suffice if you are not
expecting to store more than approximately 2 000 000 000 000 rows!
To use these types when declaring a datatype, they just follow the name of the column,
for example:

columnname TINYINT

or:

columnname BIGINT

Formatting Integers
By specifying a number in brackets after an integer datatype, you can format the way that
that type is returned in queries. For example:

columnname INT(10)