sql UPDATE,一个计算多次使用,是否可以只计算一次?
使用:
UPDATE `play`
SET `counter1` = `counter1` + LEAST(`maxchange`, FLOOR(`x` / `y`) ),
`counter2` = `counter2` - LEAST(`maxchange`, FLOOR(`x` / `y`) ),
`x` = MOD(`x`, `y`)
WHERE `x` > `y`
AND `maxchange` > 0
如您所见,LEAST(maxchange, FLOOR(x / y) )
使用了多次,但它应该始终具有相同的值。有没有办法优化这个,只计算一次?
我正在用 PHP 编写此代码,以供记录。
Using:
UPDATE `play`
SET `counter1` = `counter1` + LEAST(`maxchange`, FLOOR(`x` / `y`) ),
`counter2` = `counter2` - LEAST(`maxchange`, FLOOR(`x` / `y`) ),
`x` = MOD(`x`, `y`)
WHERE `x` > `y`
AND `maxchange` > 0
As you can see, LEAST(maxchange, FLOOR(x / y) )
is used multiple times, but it should always have the same value. Is there a way to optimize this, to only calculate once?
I'm coding this in PHP, for the record.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
数据库引擎查询优化器应该优化它。
如果您想确定,请检查解释计划 。我不认为 MySQL 支持解释更新,但它使用与 SELECT 相同的查询优化器,因此您可能必须将其转换为选择。
The database engine query optimizer should optimize that away.
If you want to be sure check the explain plan. I do not think MySQL supports explaining updates, but it uses the same query optimizer as SELECT so you might have to translate it to a select.