Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 :-(

share|improve this question
add comment

1 Answer

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

share|improve this answer
    
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 Feb 14 '12 at 16:26
    
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 Feb 14 '12 at 16:41
    
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 Feb 14 '12 at 16:54
    
Why 20 digits? 47.320.500 = 8 digits. That's pretty confusing :-) –  suntrop Feb 14 '12 at 17:31
    
@suntrop because the number 2500,3849037464622387 is 20 digits, with 16 of them after the decimal. –  Derek Downey Feb 14 '12 at 18:23
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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