14

Does anyone know about this?

user198729
  • 61,774
  • 108
  • 250
  • 348

3 Answers3

27

As the docs say:

M is the maximum number of digits (the precision). It has a range of 1 to 65. (Older versions of MySQL allowed a range of 1 to 254.)

D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

So M stands for Maximum (number of digits overall), D stands for Decimals (number of digits to the right of the decimal point).

Isidro Martínez
  • 816
  • 7
  • 15
Alex Martelli
  • 854,459
  • 170
  • 1,222
  • 1,395
  • Very useful explanation, thank you for breaking down the relationship of M and D. i was having a difficult time picking it up from the docs. – Xenology Mar 26 '14 at 07:24
  • The documentation also states that does `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.` Does this mean that `DECIMAL(M)` will apply as many decimal points (up to 30) as needed??? – oldboy Oct 16 '18 at 17:39
8

https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.1 are as follows:

  • M is the maximum number of digits (the precision). It has a range of 1 to 65. (Older versions of MySQL allowed a range of 1 to 254.)
  • D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

[Note: the link above has been updated to point to the MySQL 5.7 docs, but the text was quoted from the MySQL 5.1 docs.]

Nate C-K
  • 5,744
  • 2
  • 29
  • 45
  • The documentation also states that does `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.` Does this mean that `DECIMAL(M)` will apply as many decimal points (up to 30) as needed??? – oldboy Oct 16 '18 at 17:39
  • @Anthony: No. DECIMAL(M) will not allow any decimal places. As the version 8.0 documentation says (just after the part that you quoted): `If the scale is 0, DECIMAL values contain no decimal point or fractional part.` – Nate C-K Oct 16 '18 at 19:23
  • Ok, yeah, I ran some tests and you're right. But then what exactly does the documentation mean by ***the implementation is permitted to decide the value of M***??? – oldboy Oct 16 '18 at 19:32
  • @Anthony: The part that you cut off at the beginning, `In standard SQL`, tells us that it's talking about the SQL standard. What it means is that, for example, MySQL might choose one default value for M, Oracle might choose a different value, PostgreSQL a different one, etc. For MySQL, the v8.0 docs state that `The default value of M is 10.` So, `DECIMAL` means `DECIMAL(10,0)`. – Nate C-K Oct 18 '18 at 01:08
2

The doc says:

The declaration syntax for a DECIMAL column remains DECIMAL(M,D), although the range of values for the arguments has changed somewhat:

  • M is the maximum number of digits (the precision). It has a range of 1 to 65. This introduces a possible incompatibility for older applications, because previous versions of MySQL allow a range of 1 to 254. (The precision of 65 digits actually applies as of MySQL 5.0.6. From 5.0.3 to 5.0.5, the precision is 64 digits.)

  • D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

Isidro Martínez
  • 816
  • 7
  • 15
gimel
  • 83,368
  • 10
  • 76
  • 104
  • The documentation also states that does `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.` Does this mean that `DECIMAL(M)` will apply as many decimal points (up to 30) as needed??? – oldboy Oct 16 '18 at 17:39