0

I have a very strange case where mariaDB (mysql) inserts (or updates) a different decimal value. In the example below I want to insert the value '387507.65' but in mysql it is registered as '387507.66'. If I use a different amount, say without the '3', then it is registered fine.

As an example:

CREATE TABLE IF NOT EXISTS tbltest (
  ID int(11) NOT NULL AUTO_INCREMENT,
  admin_id int(11) NOT NULL DEFAULT '0',
  opening_balance float(11,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO tbltest (ID, admin_id, opening_balance) VALUES (NULL,'1','387507.65');

I'm tested with 5.5.52-MariaDB (Linux) 10.1.26-MariaDB (Linux)

What must I do to be able to insert the correct value of 387507.65 ?

Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43
Patrick
  • 383
  • 1
  • 2
  • 19
  • Use decimal data type if you want exact numeric. Floating point data type are approximate. – jarlh Feb 28 '18 at 08:27
  • @jarlh I thought it was the contrary : https://stackoverflow.com/questions/5150274/difference-between-float-and-decimal-data-type – Daniel E. Feb 28 '18 at 08:40
  • The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html – P.Salmon Feb 28 '18 at 08:50

2 Answers2

2

For the FLOAT type, MySQL uses up to 24 bits in the significand (fraction portion) of the floating-point number. In this format, 387507.65 cannot be represented exactly. The two nearest values are 387507.625 and 387507.65625. Since the latter is closer to 387507.65, MySQL uses it.

Then, when 387507.65625 is printed with two decimal digits after the decimal point, it is rounded to 387507.66.

If you want to store 387507.65 exactly, you can use a DECIMAL type. (Note that although some people describe decimal floating-point as “exact,” that is incorrect. Decimal floating-point can store decimal numerals exactly, if they do not have too many significant digits, but it cannot calculate exactly, in general. For most possible input values, the elementary operations of addition, multiplication, division, and subtraction will return rounded results, not exact results.)

If you want to store 387507.65 with a more precise approximation but do not need exactness, you can use DOUBLE.

If you cannot change the database from using FLOAT, then you cannot store 387507.65 any more precisely.

Eric Postpischil
  • 195,579
  • 13
  • 168
  • 312
0

Mysql uses "round half away from zero" rounding and will therefore round automatically

For inserts into a DECIMAL or integer column, the target is an exact data type, so rounding uses “round half away from zero,” regardless of whether the value to be inserted is exact or approximate:

Precision Math Rounding

Edit : I thought it used Banker's Rounding, but this gives a different outcome.

ChrisBint
  • 12,773
  • 6
  • 40
  • 62
  • There is no half here—none of the roundings involved in this situation are exactly at the midway point. First, in rounding 387507.65, the two nearest values are 387507.625 and 387507.65625. The distance to the first is .025, and the distance to the second is .00625, so the second is closer, so it is chosen. Then, when rounding 387507.65625 to two decimal digits for display, the two nearest values are 387507.65 and 387507.66. The distance to the first is .00625, and the distance to the second is .00375. The second is closer, so it is chosen. – Eric Postpischil Feb 28 '18 at 14:21