Geek asks and answers

MySQL around a strange error

Am I facing a really weird mistake? on mysql + php right now. It is a simple choice, in the following example I use several fields to try to explain my problem:

  • "field" - 11.5
  • $ phpvar - 1.15

MySQL query:

select round(field * " . $phpvar . " ,2) as a1, 
       round(field * 1.15 ,2) as a2, 
       round(11.5 * " . $phpvar . " ,2) as a3, 
       round(11.5 * 1.15 ,2) as a4, 
       field * " . $phpvar . " as a5 
from ...

OK, I'm trying to get 13.23. "field" * $phpvar = 13.225, therefore, using round (13.225,2), I have to get 13.23, right? Well, yes and no.

query result:

  • a1 [round (field * ". $ phpvar.", 2)] => 13.22
  • a2 [round (field * 1.15, 2)] => 13.22
  • a3 [round (11.5 * ". $ phpvar.", 2)] => 13.23
  • a4 [round (11.5 * 1.15, 2)] => 13.23
  • a5 [field * ". $ phpvar." ] => 13.225 (no round)

? , "", ?

+5
2

, DOUBLE FLOAT.

( ), , 11,5 22,475 , , 11.499999999999 ~ 22.475000000000000001, .

float DECIMAL coulmn, .

+6

( 1.15 11.5 ) MySQL DECIMAL.

field ( DOUBLE) DOUBLE, 8- IEEE (.. binary64). 13.225 0x402A733333333333, :

Sign           : 0b0

Biased exponent: 0b10000000010
               =   1026 (representation includes bias of +1023, therefore exp = 3)

Significand    : 0b[1.]1010011100110011001100110011001100110011001100110011
               =   [1.]6531249999999999555910790149937383830547332763671875
                    ^ hidden bit, not stored in binary representation

:

  (-1)^0 * 1.6531249999999999555910790149937383830547332763671875 * 2^3
=         13.2249999999999996447286321199499070644378662109375000

13.22 not 13.23.

DECIMAL, , , DECIMAL. 13.225 - , 13.23, .

MySQL:

, . , SQL, , , . . . FLOAT DOUBLE . DECIMAL MySQL , .

, DECIMAL . / DOUBLE, , CONVERT DECIMAL .

+5

More articles:


All Articles