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

前言</ h2>

我通过php中的mysql_query()执行查询后< / p>


“更新交易设定金额=金额100,其中id = 123”</ p>
</ blockquote>

如果以前的值 数量为101,在减去100之后,该值应为1.
但我以不同的方式检查数据:</ p>

1。 直接在mysql中查询:</ h2>

它返回0.780009989 </ strong>(有些像这样,“0.78”部分是实际的,之后只是我的样本, 我不记得exatcly,但我觉得没关系)</ p>

2.使用php:</ h2>

在php中使用mysql_fetch_object()时 ,它仍然会返回正确的答案“1”</ strong>。</ p>

测试</ h2>

我运行了一个执行测试 在循环中查询100次,但“错误”并不总是发生,有时只是</ strong>。</ p>

问题:</ h2>

< p>它可能是由未定义的长度和“数量”cloumn的十进制长度引起的吗?
为什么php函数可以正确地得到它?
请告诉我那里是否出错。</ p>

表格和列的详细信息如下:</ h2>


CREATE TABLE trade </ code>(
id </ code> int( 11)NOT NULL AUTO_INCREMENT,
amount </ code> double NOT NULL,
PRIMARY KEY( id </ code>))
ENGINE = InnoDB AUTO_IN CREMENT = 123 DEFAULT CHARSET = utf8; </ p>
</ blockquote>
</ div>

展开原文

原文

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 tradeid int(11) NOT NULL AUTO_INCREMENT, amountdouble NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=utf8;

2个回答



是的,它是由 double </ code>类型的amount字段引起的,如果你需要精确使用 DECIMAL </代码>。 更改类型,看看你是否得到了你想要的东西:</ p>

  ALTER TABLE交易修改金额DECIMAL(10,2); 
</ code> </ pre>

这将存储10位数字(小数点前8位,后2位)。 更改此项以反映您将存储在数据库中的数据。</ p>
</ div>

展开原文

原文

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.

du5114
du5114 谢谢@pajaja我会尝试这个。但我想知道“为什么”,你会更进一步,为什么php可以正确检索它?为什么mysql存储错误没有长度的定义(十进制长度)?
5 年多之前 回复



试试这个:</ p>

  update trade set amount = CAST(amount AS SIGNED)  -100,其中id = 123; 
</ code> </ pre>

参考: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast </ p>
</ DIV>

展开原文

原文

Try this:

 update trade set amount=CAST(amount AS SIGNED)-100 where id=123;

Ref: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐