2

My mysql: 5.6.16

my table info :

CREATE TABLE `xxx` (
  `uid` int(11) NOT NULL,
  `money` float(10,2) NOT NULL DEFAULT '0.00' ,
  `real_money` float(10,2) NOT NULL ,
  `available_invoice` float(10,2) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

my table content is :

1    100000.00      0.00    0.01
30   99992560.00    0.03    0.00
61   65216.00       0.03    0.00
79   10.00          0.00    0.00
80   10.00          0.00    0.00
81   -70.00         0.00    0.00
83   60.00          0.00    0.00
100  100.00         50.00   50.00
889  980.00         0.00    0.00
1234 99959040.00    0.00    99999.00
1239 40.00          0.00    0.00

when I execute the sql :

update `xxx` set `money`=`money`-20 where uid = 1234

the result is :

uid  money          real_money available_invoice
1    100000.00      0.00    0.01
30   99992560.00    0.03    0.00
61   65216.00       0.03    0.00
79   10.00          0.00    0.00
80   10.00          0.00    0.00
81   -70.00         0.00    0.00
83   60.00          0.00    0.00
100  100.00         50.00   50.00
889  980.00         0.00    0.00
1234 99959024.00    0.00    99999.00
1239 40.00          0.00    0.00

it is always miss 4 or 2 whatever I add or subtracts some to a float number which is nearly 100000000, why ?

freeeze king
  • 489
  • 2
  • 6
  • 13
  • 1
    I don't have an exact answer, but using `decimal(10,2)` might fix this problem. See [here](http://stackoverflow.com/questions/21226700/subtraction-of-two-equal-values-is-not-zero-in-mysql) for more information. – Tim Biegeleisen Jan 14 '17 at 06:32
  • Do you happen to have an insert script? So I can easily recreate your problem? – lonious Jan 14 '17 at 06:40
  • 1
    @gordlonious INSERT INTO `xxx` (`uid`, `money`, `real_money`, `available_invoice`) VALUES (1, 100000.00, 0.00, 0.01), (30, 312.00, 0.03, 0.00), (61, 65216.00, 0.03, 0.00), (79, 10.00, 0.00, 0.00), (80, 10.00, 0.00, 0.00), (81, -70.00, 0.00, 0.00), (83, 60.00, 0.00, 0.00), (100, 100.00, 50.00, 50.00), (889, 980.00, 0.00, 0.00), (1234, 100000.00, 0.00, 99999.00), (1239, 40.00, 0.00, 0.00); – freeeze king Jan 14 '17 at 06:46
  • Money is almost always DECIMAL – Strawberry Jan 14 '17 at 07:51
  • In [SQL Fiddle](http://www.sqlfiddle.com/#!9/cb128/1) it is working fine!! – shA.t Jan 14 '17 at 08:58
  • Try it in mysql. I reenacted the problem exactly. the `money` column should be either `decimal` or `numeric` – lonious Jan 14 '17 at 09:26

1 Answers1

1

Your problem has something to do with how large your floating point value is but I'm not 100% sure why. I tested updating a similar row with smaller numbers and it worked fine. You should also refrain from using quotes wherever possible as it requires extra processing. See this SO question: update a column by subtracting a value

Here is my easy work-around just like Tim Biegeleisen suggested:

CREATE TABLE `xxx` (
  `uid` int(11) NOT NULL,
  `money` decimal(10,2) NOT NULL DEFAULT '0.00' ,
  `real_money` float(10,2) NOT NULL ,
  `available_invoice` float(10,2) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Insert:

USE dbname;
insert into xxx Values(1234, 99959040.00, 0.00, 99999.00);

Update without strings:

update xxx set money = money - 20 where uid = 1234;

There are some odd things when calculating with large floating point values. Some of these issues are machine-dependent and are related to what type of processor you have. Read more here: http://dev.mysql.com/doc/refman/5.7/en/problems-with-float.html

Also, according to another SO question floating point values are NOT a good way to store money values. Difference between float and decimal data type (see the second answer).

It seems that using decimal and numeric data types are best for money columns in mysql.

Community
  • 1
  • 1
lonious
  • 676
  • 9
  • 25