6

I need to store some high numbers, some with decimal points (2 digits).

  • 965.630,5 INT
  • 47.320.500 INT
  • -62.188,0 INT
  • 32,8% FLOAT
  • 225.165,7 DOUBLE
  • 715.100,7 DOUBLE
  • 6.929.850,0 DOUBLE

I am not sure what types are right. I don't get the difference between DOUBLE and FLOAT. And when do I use DECIMAL/NUMERIC? Hope someone can help. Please don't link to MySQL docs. I don't understand what they wrote :-(

Improve this question

1 Answer 1

Reset to default
8

First, if you are wanting to use decimal points, you can scratch INT off your list.

Next, you need to understand that FLOAT and DOUBLE are similar because they store the value in approximate value, but that DOUBLE is 8-bytes, and FLOAT is 4-bytes. So for larger numbers you would want DOUBLE instead of FLOAT.

Now, the choice is between DOUBLE and DECIMAL (DECIMAL can have 65 digits). MySQL tells us that for DOUBLE columns, the value will be rounded to fit into the column size. It also says that for DECIMAL, the behavior is left up to the operating system. It will either ROUND or truncate the digits that don't fit into the column.

My suggestion is to choose the column type from the above description that you think meets your needs (either DECIMAL or DOUBLE), and insert some test data to see if the storage behavior is as you expect.

Also, if you are doing any comparison on these columns, make sure the comparison is as you expect: 2.145<>2.140

Improve this answer
6
  • The difference between DOUBLE and FLOAT is not (only) how big values you can store, but what precision you get. You can store pretty big values in FLOAT columns, too.
    – ypercubeᵀᴹ
    Commented Feb 14, 2012 at 16:26
  • 1
    Thanks! Not all numbers will contain decimal points, perhaps I'll choose INT for those. What makes it hard to understand (for me), is how "large numbers" are defined. What is bigger? 5.100.100 or 2500,3849037464622387? At what part di i have to look - the maximum number of digits or the higher value?
    – suntrop
    Commented Feb 14, 2012 at 16:41
  • 1
    For the first number, you only need precision of about 5 digits (5100100 = 51001 * 100). So FLOAT or DOUBLE is fine. For the second, you'll need precision of 20 digits, which means you can't use FLOAT or DOUBLE. You'll need DECIMAL(20,16) or higher.
    – ypercubeᵀᴹ
    Commented Feb 14, 2012 at 16:54
  • Why 20 digits? 47.320.500 = 8 digits. That's pretty confusing :-)
    – suntrop
    Commented Feb 14, 2012 at 17:31
  • @suntrop because the number 2500,3849037464622387 is 20 digits, with 16 of them after the decimal.
    – Derek Downey
    Commented Feb 14, 2012 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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