how can I stop mysql from rounding float values?

Question

I'm storing monetary values in a mysql table as floats.

problem being, mysql is rounding the numbers up or down.

ex. 12345.68 gets rounded to 12345.7

How can I stop this or is there a better data type I should be using?

I would like to retain the original values within the 2 decimal places.

Fortunate

Answer 1

Do not use FLOAT type. Use DECIMAL instead. Float converts decimal numbers to binary which results in rounding (loss of precision). Decimal stores the numbers as decimals - no conversion.

In your case defining the column as DECIMAL(12,2) should be ok. Chose the width (first number) based on the expected size of the numbers. In the example, the expected size is 12 digits (including the digits after the decimal point).

Mareld

Answer 2

Change the definition of the applicable column from its current setting to:

FLOAT(9,2)

The 2 in the previous snippet instructs MySQL to maintain values up to 2 decimal places. It's likely set to 1 at the moment; thus the observed behavior. Feel free to change the 9 to a more applicable value.

More on floats and precision.

Related answer, which advises on not using floats, but instead decimal.

Morita

@Ivan Georgiev's answer should be the accepted one. – Discuss