What is the use of DECIMAL(x,0)?

Question

In a DECIMAL(M, D) column MySQL gives the option for the range of D to be 0 to 30.

Is there a subtle reason that I'm missing for the option of 0? Isn't a decimal with nothing after the decimal point an integer?

When and why would I want to specify a DECIMAL that has no decimal places?

Answer 1

The number range of the DECIMAL type is much greater than for an INTEGER or BIGINT. The greatest number you are able to store in a DECIMAL(65, 0) is 65 nines. The largest number in a BIGINT is 18446744073709551615.

DECIMAL(x, 0) is often a little more expensive for small numbers. Consider using a defined INTEGER type if your numbers are in the range for one of those.

---

The storage requirement in bytes for a DECIMAL(x, 0) field depends on the x according to this formula:

Storage = x / 9 + Leftover
Leftover = round_up((x % 9) / 2) (i.e., about half of the leftover digits)

You can read more about storage requirements for numeric types in the MySQL manual and compare for yourself.

Answer 2

Besides allowing to store values bigger than BIGINT, you can use DECIMAL(x,0) if you want to:

• allow values in the range -9, ... , +9: use DECIMAL(1,0) (uses 1 byte)

• allow values in the range -99, ... , +99: use DECIMAL(2,0) (uses 1 byte)

• allow values in the range -999, ... , +999: use DECIMAL(3,0) (uses 2 bytes)

• allow values in the range -9999, ... , +9999: use DECIMAL(4,0) (uses 2 bytes)

...

• allow values in the range -999999999, ... , +999999999: use DECIMAL(9,0) (uses 4 bytes)

... etc (up to DECIMAL(65,0) which uses 29 bytes)