BIGINT UNSIGNED 值超出范围
我收到错误
BIGINT UNSIGNED 值超出“(1301980250 -
mydb.news_articles.date)”中的范围
当我运行查询
SELECT *, ((1 / log(1301980250 - date)) * 175) as weight FROM news_articles ORDER BY weight;
删除 ORDER BY 条件 ，也消除了错误。我该如何修复它？
更新：日期字段包含unix时间戳（例如：1298944082）。我将 MySQL 从 5.0.x 升级到 5.5.x 后开始出现该错误
请帮忙吗？
I am getting the error
BIGINT UNSIGNED value is out of range in '(1301980250 -
mydb.news_articles.date)'
When I run the query
SELECT *, ((1 / log(1301980250 - date)) * 175) as weight FROM news_articles ORDER BY weight;
Removing the ORDER BY condition, removes the error too. How can I fix it?
Update: The date field contains unix timestamp (ex: 1298944082). The error started appearing after I upgraded MySQL from 5.0.x to 5.5.x
Any help please?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我最近遇到了这个问题，并找到了最合理的解决方案，将任何无符号整数强制转换为有符号整数。
I recently ran into this and found the most reasonable solution to simply cast any UNSIGNED ints as SIGNED.
该问题是由 wallyk 所建议的无符号整数溢出引起的。可以通过
`
NO_UNSIGNED_SUBTRACTION（尚未检查此项）The problem was caused by unsigned integer overflow as suggested by wallyk. It can be solved by
SELECT *, ((1 / log((date - 1301980250) * -1)) * 175) as weight FROM news_articles ORDER BY weight;(This one worked for me)`
NO_UNSIGNED_SUBTRACTION(haven't checked this)这有时可能是由数据中的空值引起的。
使用 IFNULL 设置默认值（时间戳的默认值可能为 0，实际上在这种情况下，您最好在 WHERE 子句中排除日期和空日期）
SELECT (123456 - IFNULL(date, 0)) AS leVarThis can sometimes be caused by nulls in the data.
Use IFNULL to set a default value (probably 0 for a timestamp is a poor default and actually in this case you might be better off excluding and null dates in the WHERE clause)
SELECT (123456 - IFNULL(date, 0)) AS leVar2011-04-04 22:10:50 PDT (2011-04-05 05:10:50 utc) 之后的任何日期值都会导致此错误，因为这会使表达式为负数。
Any date value after 2011-04-04 22:10:50 PDT (2011-04-05 05:10:50 utc) will cause this error since that would make the expression negative.
没有人提到 log() 函数仅针对严格的正参数定义。在 log() 中使用减法时请注意这一点。
至于最初的问题，解决问题的一个关键因素是告诉我们日期列的数据类型。如果它是 UNSIGNED，MySQL 可能不喜欢它。
规则是 MySQL 的算术算法很差，当 A 的编码字节数少于 B 并且 B > 时，无法弄清楚如何从另一个 A 中减去操作数 B（= do AB）。 A.
例如A = 12并且是SMALLINT，B = 13 AS INT，那么MySQL无法计算出AB是什么（-1！）
要使MySQL内容，只需扩展操作数的编码长度答：如何？使用 CAST()，或将 A 乘以十进制数。
可以看出，这与其说是溢出问题，不如说是 MySQL 算术中处理符号的问题。
微处理器，或者更好的是人类，可以毫无问题地执行这种算术...
使用 CAST() 是一种方法，或者简而言之，只需通过将操作数 A 乘以 1 来引发隐式转换。（或者1.0)：
例如
Nobody mentionned that the log() function is only defined for strictly positive arguments. Watch for this when using substractions inside of log().
As for the original question, a key factor for resolution was to tell us the data type for the date column. If it is UNSIGNED, MySQL might not like it.
The rule is that MySQL has a poor arithmetic algo, and can't figure out how to substract an operand B FROM another A (= do A-B) when A is coded on less bytes than B AND B > A.
e.g. A = 12 and is SMALLINT, B = 13 AS INT, then MySQL can't figure out what A-B is (-1 !)
To make MySQL content, just expand the coding length of operand A. How? Using CAST(), or multiplying A by a decimal number.
As one can see, it is less a problem of overflow than a problem of handling the sign in the arithmetics of MySQL.
A microprocessor, or better, a human, has no problems to perform this kind of arithmetics...
Using CAST() is the way, or for short, just provoke the implicit cast by multiplying operand A by 1. (or 1.0):
e.g
也许你可以使用
castSELECT *, ((1 / log(1301980250 - cast(date AS SIGNED))) * 175) 作为重量 FROM news_articles ORDER BY Weight;maybe you can use
castSELECT *, ((1 / log(1301980250 - cast(date AS SIGNED))) * 175) as weight FROM news_articles ORDER BY weight;我刚刚在对结果小于 0 的字段进行更新时遇到了这个问题。
解决方案：验证没有任何更新会导致无符号字段上的结果小于 0。
I just came across this issue doing an update on a field where the result ended up being less than 0.
Solution: Verify that none of your updates cause your result to be less than 0 on an unsigned field.