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

  • Floating Point:  FLOAT, DOUBLE
  • Fixed Point:      DECIMAL
  • Other:              BIT, (ENUM maybe)‫‏‬

Some poor design:

  • INT(1)‫‏‬
  • 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

3 Responses to “MySQL – Choose exact numeric data type”

  1. Naseer Ahmad Mughal Says:

    nice and very informative for me…
    Thanks for sharing

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: