MySQL MOD()已损坏:这是最好的选择吗? [英] MySQL MOD() is broken: Is this the best alternative?
问题描述
至少在MySQL v5.1.73(CentOS 6.6)中,MOD()函数返回假结果...除非有人可以解释这实际上是正确的.
At least in MySQL v5.1.73 (CentOS 6.6), MOD() function returns a bogus result... unless someone can explain how this is actually correct.
mysql> select MOD(-385.4784399 ,1440);
+-------------------------+
| MOD(-385.4784399 ,1440) |
+-------------------------+
| -385.4784399 |
+-------------------------+
1 row in set (0.01 sec)
这是最好的选择吗?
mysql> select -385.478439885319 - 1440 * FLOOR(-385.478439885319/1440);
+----------------------------------------------------------+
| -385.478439885319 - 1440 * FLOOR(-385.478439885319/1440) |
+----------------------------------------------------------+
| 1054.521560114681 |
+----------------------------------------------------------+
1 row in set (0.00 sec)
我认为这也可以,但是要做一些简单的事情都需要很长的路要走.
I think this will work as well, but it's all going the long way around to do something simple.
mysql> select MOD((MOD(-385.478439885319, 1440) + 1440), 1440);
+--------------------------------------------------+
| MOD((MOD(-385.478439885319, 1440) + 1440), 1440) |
+--------------------------------------------------+
| 1054.521560114681 |
+--------------------------------------------------+
1 row in set (0.00 sec)
推荐答案
MySQL并没有给您带来虚假的结果,它只是使用了与预期不同的模数实现.不幸的是,术语模数"似乎定义不明确,并且其实现因语言而异.从关于Modulo的维基百科中我可以看出,MySQL的实现使用了截断除法:
MySQL isn't giving you a bogus result, it's simply using a different implementation of modulus than you are expecting. Unfortunately the term modulus seems to have been defined somewhat ambiguously, and it's implementation varies from language to language. From what I can tell in the Wikipedia on Modulo, MySQL's implementation is using truncated division:
r = a - n * trunc(a / n)
您希望该实施使用地板除法:
r = a - n * floor(a / n)
由于这是您实施第一个解决方法的方式,因此我想这可能是Mod
运算符的最佳替代方法.
Since this how you implemented your first workaround, I'd say it's probably the best alternative to the Mod
operator.
从我所见(这是一个非常快速的不科学的分析!)看来,似乎更命令式的编程语言实现了截断除法,而更多的函数数学语言似乎使用了部门.
From what I've seen (and this is a very quick unscientific analysis!), it seems like more imperative programming languages implement truncated division and more functional mathematical languages seem to use floored division.
这篇关于MySQL MOD()已损坏:这是最好的选择吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!