php从mysql访问数据时的奇怪查询

Question

Preface

After i executed the query through mysql_query() in php

"update trade set amount=amount-100 where id=123"

If the previous value of amount was 101,after minus 100，the value is supposed to be 1. But i checked the data in different ways:

1. Directly query in mysql:

it returns with 0.780009989(some figure like this,the "0.78" part is actual,after that is just a sample from me,i can't remember exatcly,but i think it doesn't matter)

2.Using php:

while using mysql_fetch_object() in php,it still returns the correct answer of "1".

Test

I've run a test of execute the query 100 times in a loop,but the "error" does not always occur,just sometimes.

Question:

Is it possibly casued by undefined length and decimal length of the "amount" cloumn? Why php funciton can get it correctly? Pls tell me whether something went wrong there.

The detail info of the table and the column as below:

CREATE TABLE trade（ id int(11) NOT NULL AUTO_INCREMENT, amountdouble NOT NULL, PRIMARY KEY (id) ） ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=utf8;

Answer 1

Yes, it is caused by the double type of amount field, and if you need precision use DECIMAL. Change the type and see if you get what you want:

ALTER TABLE trade MODIFY amount DECIMAL(10,2);

That will store 10 digit numbers (8 before decimal point and 2 after). Change this to reflect the data you will store in your DB.