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

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

You may also like

1 Comment

LEAVE A COMMENT

About Me

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

Flickr

Flickr Feed
Flickr Feed
Flickr Feed
Flickr Feed
Flickr Feed
Flickr Feed
Flickr Feed
Flickr Feed
Flickr Feed

Twitter Feed

Fanpage