MySQL – Choose exact numeric data type

When we use a relational database system, we should know some important things. Numeric data type is very essential for a table. Here I describe some important points of Numeric Data type in MySQL:

MySQL has 9 numeric data types

• Integer:            TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
• Floating Point:  FLOAT, DOUBLE
• Fixed Point:      DECIMAL
• Other:              BIT, (ENUM maybe)‫‏‬

Some poor design:

• INT(1)‫‏‬
• BIGINT AUTO_INCREMENT
• no UNSIGNED used
• DECIMAL(31,0)‫‏‬

INT(1) – 1 does not mean 1 digit.   (1) represents client output display format only. INT is 4 Bytes. TINYINT is 1 Byte . TINYINT UNSIGNED can store from 0 – 255. BIT is even better when values are 0 – 1

BIGINT is not needed for AUTO_INCREMENT

• INT UNSIGNED stores 4.3 billion values
• You should be partitioning when at billions of rows
• BIGINT is applicable for some columns e.g. summation of values

Best Practice

• All integer columns UNSIGNED unless there is a reason otherwise.
• Adds a level of data integrity for negative values