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.

Ivan Georgiev · Accepted Answer · 2017-01-05 18:41:51Z

14

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).

edited Jan 5, 2017 at 18:41

answered Jan 5, 2017 at 18:36

Ivan Georgiev

1,1857 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:53:46Z

3

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.

1 Comment

Tiois Over a year ago

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

Collectives™ on Stack Overflow

how can I stop mysql from rounding float values?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related