# 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. Jul 26, 2016 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! Jul 26, 2016 at 17:53
• (12,2) is 10 digits to left of decimal point. Jul 26, 2016 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.) Jul 26, 2016 at 17:56
• FLOAT will lose/gain pennies for values bigger than \$167,772.15 (I think). Jul 26, 2016 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. Jul 26, 2016 at 9:41
• The IEEE Standard for Floating-Point Arithmetic (IEEE 754) was made for fast math on CPU/ALUs and has limitations. It has more to do with the binary representation of the numbers. Nov 21, 2022 at 23:09