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
Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: