# Biggest possible double/float for a column in my MySQL database

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!

Cheers.

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

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

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