mysql float type

Answer 1

Are you dealing with currency with floating point values?

Please don't.

Floating point values cannot represent exact decimal values, as they are representation of binary fractions. Unless you want to end up with values such as $1.0000000000001, use a data type that is integral, such as a decimal type.

The reason why comparison using an == operator does not work as intended is because many numbers that can be represented in decimal cannot be represented in floating point, therefore, there is no exact match.

Here's a little example:

System.out.println(1.00000001f == 1.00000002f);
System.out.println(200000.99f - 0.50f);

Outputs:

true
200000.48

Those two examples should show that relying on floating point values for currency would not be such a good idea.

As for the storage of currency values, it appears that there is a DECIMAL type in MySQL as well, so I would think that would be a better choice, unless there is some CURRENCY type -- I'm not familiar enough with SQL databases to give any informed opinion here.

Here's a little information from the MySQL 5.1 Reference Manual, Section 10.2: Numeric Types:

The DECIMAL and NUMERIC data types are used to store exact numeric data values. In MySQL, NUMERIC is implemented as DECIMAL. These types are used to store values for which it is important to preserve exact precision, for example with monetary data.

Here's a related question:

• How do I round up currency values in Java?
• What is the best way to store a money value in the database?
• Representing Monetary Values in Java
• What is the best datatype for currencies in MySQL?
• Is a double really unsuitable for money?

Answer 2

When you compare floating point numbers you must always compare them within a small range, say plus or minus half a penny when dealing with currency. If you compare them with exact equality, you will always get errors because floating point does not represent most decimal numbers precisely.

It is because of this problem that money is usually stored in mySql as DECIMAL rather than floating point. DECIMAL does not suffer from the same inaccuracies.