CODEWITHSUNDEEP
mysql
Leo
Leo

Reputation: 6571

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

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?

Upvotes: 11

Views: 8233

Answers (4)

Divyanshu Pawar
Divyanshu Pawar

Reputation: 29

In Mysql, Decimal(3,2) means total 3 digits and 2 digits after decimal point like 3.42

Decimal(3,0) means in total 3 digits and no digit after decimal point like 345 Even if you write something beyond the given range in brackets of decimal Mysql will automatically update them to 000 or 999.

Upvotes: 2

Emil Vikström
Emil Vikström

Reputation: 91963

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.

Upvotes: 11

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

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)

Upvotes: 2

Matzi
Matzi

Reputation: 13925

In a biging you can only store a digit which is no larger than 18 446 744 073 709 551 615. This is 20 digits, but in a DECIMAL you can specify even a 65 digits to store. Also with int you can't constrait directly the number of digits to a low number (e.g. to one). So it is more flexible, and if you need to expand it on an existing database, it is easier.

Upvotes: 1

Related Questions