How To Update A Floating Point Number In Mysql

Floating Point Rounding Errors in MySQL

Sep 3, 2021 by Robert Gravelle

Although MySQL DECIMAL and NUMERIC data types are both stock-still-bespeak values, they are still susceptible to rounding errors. The reason is that, no thing how many digits a blazon tin can adapt (the maximum number of digits for DECIMAL is 65!) that number is yet fixed. Moreover, DECIMAL columns can be assigned a precision or calibration that could have the potential affect of truncation to the allowed number of digits.

decimal_column_in_navicat (43K) — Decimal Column In Navicat Tabular array Designer

I became aware of potential rounding errors in MySQL when a reader asked me why a couple of similar queries were returning slightly different DECIMAL values in calculations. This prompted me to go along a journey of discovery. In today's weblog, I would like to share some of what I learned most floating point rounding in MySQL.

A Tale of Two Queries

Here are the queries I used to bear witness the discrepancy:

subquery (37K) — Hourly Payments Calculation Using a Subquery

group_by (57K) — Hourly Payments Calculation Using Group By With Rollup

Annotation: some rows were removed from the Grouping By With Rollup query to reduce the meridian of the image.

The reader was calculating employee salaries, but I did non have an identical table to query, then I used the virtually similar table that I could detect, and that was the payments table of the classicmodels sample database:

payments_table (114K)

In this context, perchance computing hourly payments does not make much sense, merely the queries did highlight the rounding differences between the 2 SELECT statements (4256.653475 vs. 4256.65347six).

And then, why does SUM using a subquery and the GROUP BY WITH ROLLUP produce different results?

Floating-point Judge Values Versus Stock-still-point Exact Values

The floating-point (approximate value) types are FLOAT, Real, and DOUBLE, while the stock-still-bespeak (exact value) types are INTEGER, SMALLINT, DECIMAL, and NUMERIC. Floating-point means the decimal point can exist placed anywhere relative to the significant digits of the number with the actual position being indicated separately. Meanwhile, a fixed-point value is an integer that is scaled by a specific factor.

Back in version five.5, MySQL added back up for precision math, which included a library for fixed-point arithmetics that replaced the underlying C library and allowed operations to exist handled in the same manner across different platforms. Since this update, if no approximate values or strings are existence used in a calculation, expressions are evaluated using DECIMAL exact value arithmetic with precision of 65 digits. For Group Past functions, STDDEV() and VARIANCE() return DOUBLE, an judge floating-signal type, while SUM() and AVG() render a DECIMAL for verbal-value arguments and a DOUBLE for estimate value.

Another ramification of the new MySQL library for fixed-point arithmetics is that type conversion is now handled using floating-point values. Thus, the results of type conversion may vary and can be afflicted by factors such as computer architecture, the compiler version or fifty-fifty the optimization level. One way to avoid these problems is to utilize an explicit Bandage() rather than the implicit conversion.

Which Result is Correct?

Getting dorsum to the initial queries, which value is more than accurate and which one is the right way to obtain the SUM? The truth is, neither is exactly accurate, merely, by using a trivial algebra, the query can be simplified to yield an accurate result:

simplified_query (30K)