如何更新MySQL column = column-number

Question

I have this mysql columns:

points_on | points_off
    0          1.36

And this SQL command:

UPDATE table SET points_off = points_off-{$cash}, points_on = points_on+{$cash} WHERE ...

$cash = "1.36"

If I run this, the table update wrong like this:

points_on | points_off
   1.36      0.0000000143051 

How to update this correctly?

I need to move $cash number from points_off to points_on.

Answer 1

Is the MySQL field type float or double?

According to http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html, the problem is that float values are stored approximated.

You should use decimal, defined as decimal(M,D), where M is the maximum number of digits in the number (e.g., for 0.00 to 9.99, M = 3), and D is the number of digits after the point (e.g., for 0.00 to 9.99, D = 2).

I performed the same operations you tried, got the same problem, and fixed it with decimal.

Beware though that decimal fields require much more storage space than float.