2

I am developing a PHP application in which I am in need to store values bigger than 1 million, but with the flexibility of having floating points. I know the traditional float and double. I am looking to store only up to two decimal points, and will only handle operations with numbers that have 2 decimals exclusively.

I have chosen my column type of double and unsigned, but whenever I store a row with a value bigger than one million, it gets truncated to 999999.99

Why is this? I am looking for the right datatype/solution. If you could point me in the right direction, I will appreciate it a lot!

Thank you for your time in advance!

Cheers.

  • Did you use DOUBLE or DECIMAL(8,2)? Looks like the latter. – ypercubeᵀᴹ Jul 26 '16 at 10:57
4

Do not use (m,n) on the end of FLOAT or DOUBLE. That causes a rounding (at the bottom) or a truncation (at the top). If you want (m,n), you probably should use DECIMAL(m,n).

FLOAT stores 24 significant bits of data (equivalent to about 7 decimal digits; storage=4 bytes), with an exponent ranging over about 10 ** +/-38.

DOUBLE stores 53 bits (about 16 digits, 8 bytes) and has a huge range.

Your question is vague -- are you storing "money"? If so, then you really should use DECIMAL(m,2). m=8 lets you store up to a million dollars (or Euros, etc). But m can be as big as you want. (Remember to subtract the 2 before seeing how big the numbers can be.) (14,2) would store up to a trillion dollars, with precision to the penny. It will take 8 bytes of storage.

  • Yes indeed. I am storing "money". I would like to do operations in that column. As in retrieve the value from the DB, operate on it (server side using PHP) and then storing the result again. What do you mean by subtracting the 2? I read a little bit about DECIMAL, but it is unclear to me still. I have never had the need to work with such big quantities. So if I declare my COLUMN as DECIMAL(12,2), it will go up to the billion (or thousand million, depending on where you are from) with 2 decimal points? Again, thank you for your response! – idelara Jul 26 '16 at 17:53
  • (12,2) is 10 digits to left of decimal point. – Rick James Jul 26 '16 at 17:55
  • Beware of PHP -- the numbers are probably converted to DOUBLE inside PHP. In MySQL, they say as DECIMAL -- no rounding errors except where it is obvious (such as with division, AVG, etc.) – Rick James Jul 26 '16 at 17:56
  • FLOAT will lose/gain pennies for values bigger than $167,772.15 (I think). – Rick James Jul 26 '16 at 17:59
1

Floating point numbers are not always stored as you would wish, due to the way CPUs deal with floating point numbers.

If you're always storing numbers that have 2 decimal points, store it as an integer and add the decimal point in the presentation layer.

  • This is what I was thinking about. But what about if it is a must to operate on the numbers I need to store? I was reading and I came across the "DECIMAL" datatype. – idelara Jul 26 '16 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.