MySQL: What field types for large numbers?

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

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

StackExchange Database Administrators Q#13012, answer score: 8