CODEWITHSUNDEEP
mysqldecimaldecimal-pointmysql-5.6
oldboy
oldboy

Reputation: 5954

DECIMAL(M), The Implementation Is Permitted to Decide the Value of M

The documentation states,

In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(M,0), where the implementation is permitted to decide the value of M. MySQL supports both of these variant forms of DECIMAL syntax.

The part regarding DECIMAL(M) or DECIMAL(M,0) that I find confusing is,

For DECIMAL(M,0), the implementation is permitted to decide the value of M.

If a column is set to DECIMAL(65), 4.321 would be stored as 4, so what exactly does the documentation mean?

Upvotes: 0

Views: 124

Answers (2)

Rick James
Rick James

Reputation: 142366

To phrase it a different way, and use the values that MySQL chose:

  • DECIMAL means DECIMAL(10,0) (which takes 5 bytes)
  • DECIMAL(12) means DECIMAL(12, 0) (where '12' can be any value up to 65)
  • That is, there is always an M and an N, either explicitly stated or not.

When N is 0, no decimals are stored. Hence, 4.321 would be stored as 4. The numbers you can store (without roundoff or truncation) are numbers with no more than N digits after the decimal point and no more than M-N digits before.

Upvotes: 1

Juan
Juan

Reputation: 5589

By running this test in MySQL 5.6:

create table t(
deci decimal, 
deci4 decimal(4),
deci40 decimal(4,0),
deci42 decimal(4,2)
);


insert into t(deci, deci4, deci40, deci42)
values
(1234.1234,1234.1234,1234.1234,1234.1234);

The insert fails because 1234.1234 doesn't fit in (4,2)

insert into t(deci, deci4, deci40, deci42)
values
(1234.1234,1234.1234,1234.1234,34.1234);

Runs with the following values inserted:

deci    deci4   deci40  deci42  
1234    1234    1234    34.12

In DECIMAL(M,N), M is the number of places the number can take, including the decimals, N is the number of decimal places. Maximum value for M is 65, and maximum value for N is 30. N has to be smaller than M.

If N=0 it is 0 decimal places.

If N is not defined, the decimal places seem to be defined by the number of places actually taken by the integer part of the number, which is less than M, and the the rest of the places left for the decimals as needed.

If neither are defined then M is defaulted to 10.

Info on default values taken from here: http://www.mysqltutorial.org/mysql-decimal/

Upvotes: 1

Related Questions