1

Hello sorry for this noob question but why DECIMAL(10,9) give me maximum 9.999999999 ? 10 is not length in left of decimal ? Like 9999999999.999999999

What is the good solution for store number like 9999999999.999999999 ? Double(9,9) or Decimal(9999999999,9) ? Thanks you

Column  Type    Null    Default Comments    MIME
id      int(1)  No           
value   decimal(19,9)   No           
value2  decimal(10,9)   No
soooo
  • 135
  • 1
  • 1
  • 12

2 Answers2

5

The precision is number of significant digits, e.g. total number of digits, and scale is number after the decimal point, so if you want 10 before and 9 after, then you want DECIMAL(19,9)

DBug
  • 2,502
  • 1
  • 12
  • 25
  • It's worked with 19,9 Thanks you ! but why 19 work and 10 give me 9.99.. max ? – soooo Nov 22 '15 at 16:01
  • 1
    DECIMAL(10,9) means 10 is the total number of digits on both sides, 9 is the number of digits on the right, leaving just 1 on the left. With DECIMAL(19,9), 9 are still on the right, but 19-9=10 are now available on the left. See also the manual: https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html – Willem Renzema Nov 22 '15 at 16:10
  • Thanks you all for reply ! – soooo Nov 22 '15 at 16:57
3

Decimal(p,s) - p is the precision and s is the scale (in your case decimal(10,9) :9999999999.999999999 is max value)

Note: if use to store exact numeric data values use decimal type. Actually it's quite different. DOUBLE causes rounding issues. And if you do something like 0.1 + 0.2 it gives you something like 0.30000000000000004.

Diffrence between float, double and decimal stack1, stack2

Community
  • 1
  • 1
Ramin Darvishov
  • 1,043
  • 1
  • 15
  • 30
  • Ah ? But if i edit value `UPDATE `db`.`stats` SET `value` = '10' WHERE `stats`.`id` =0;` i have this error `Warning: #1264 Out of range value for column 'value' at row 1` – soooo Nov 22 '15 at 15:58
  • post table stucture (crate statement) – Ramin Darvishov Nov 22 '15 at 16:03
  • I edit my post, value(19,9) work with 9999999999.999999999, value2(10,9) not give me maximum 9.999999999 – soooo Nov 22 '15 at 16:06
  • Standard SQL requires that DECIMAL(10,9) be able to store any value with 10 digits and 9 decimals, so values that can be stored in the column range from -9.999999999 to 9.999999999. – Ramin Darvishov Nov 22 '15 at 16:11
  • Don't forget to mark as Answered (with green check mark) which solves your problem or answers – Ramin Darvishov Nov 22 '15 at 16:31