CODEWITHSUNDEEP
mysqlselectprecisiondecimal-point
Jimmix
Jimmix

Reputation: 6506

MySQL SELECT Precision of calculation (digits after decimal point)

I'm trying to achieve the same amount of precision (number of digits after decimal point) for results coming from a SELECT that does some equations.

  1. Is there any difference in terms of performance, good practice of use in case of achieving wanted precision between CAST() or ROUND() besides rounding by the last one? Is there any better alternative?

For the sake of simplicity values in all examples below are hardcoded, they may or may not come from table's columns

I'm using MySQL 8.

If you run example E1 :

-- E1
SELECT (41/99);
-- 0.4141

you'll get 4 digits after decimal point.

  1. Is there any MySQL setting that can bring higher precision right out of the box so I don't need to use:

    -- E2 SELECT ROUND((41/99), 20); -- 0.41414141400000000000

    -- or

    SELECT CAST((41/99) AS DECIMAL(21,20)); -- 0.41414141400000000000

  2. How to get more decimal points precision from E2 in case data used for calculation is int?

If you supply data with decimal point you get much higher precision:

-- E3
SELECT ROUND((41.0/99.0), 20);
-- 0.41414141414141414100

SELECT CAST((41.0/99.0) AS DECIMAL(21,20));
-- 0.41414141414141414100

It is important to me to avoid floating point datatypes due to their approximation of decimals. If the data be coming from table column the column will be decimal datatype. But data for calculations may be also hardcoded.

Upvotes: 2

Views: 8438

Answers (1)

O. Jones
O. Jones

Reputation: 108706

MySQL uses 64-bit IEEE-754 floating point (aka DOUBLE) for its internal computations unless you specifically get it to use integer or decimal arithmetic by casting your constants. (So does Javascript.)

When displaying numbers it does its best to render them in decimal as accurately as possible. With DOUBLE, that requires a conversion to decimal. If you don't want the default rendering accuracy, but rather want to control it yourself, you may use the ROUND() or TRUNCATE() functions. That's how you control it.

There's not much performance penalty for using these functions. Many programmers who use plain SQL (rather than SQL retrieved by an application program in Java, PHP, or another language) always use one of the functions to retain control over the rendering.

Test carefully if you're depending on DECIMAL-data-type accuracy in computations: MySQL really wants to do them in DOUBLE. Or, better yet, use a strongly typed language to gain precise control over your arithmetic.

Upvotes: 4

Related Questions