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
mahmud ahsan

Computer programmer and hobbyist photographer from Bangladesh, lives in Malaysia. My [Github | Business | Twitter | Linkedin | Youtube | Instagram | 500px]

You may also like

1 Comment

  • Naseer Ahmad Mughal
    August 21, 2008 at 11:45 am

    nice and very informative for me…
    Thanks for sharing

Comments are closed here.