MySQL BIGINT UNSIGNED 值超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5605085/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
BIGINT UNSIGNED value is out of range
提问by Joyce Babu
I am getting the error
我收到错误
BIGINT UNSIGNED value is out of range in '(1301980250 -
mydb.news_articles.date)'
BIGINT UNSIGNED 值超出“(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?
删除 ORDER BY 条件也会删除错误。我该如何解决？
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
更新：日期字段包含 unix 时间戳（例如：1298944082）。我将 MySQL 从 5.0.x 升级到 5.5.x 后开始出现错误
Any help please?
请问有什么帮助吗？
回答by ab5tract
I recently ran into this and found the most reasonable solution to simply cast any UNSIGNED ints as SIGNED.
我最近遇到了这个问题，并找到了最合理的解决方案，将任何 UNSIGNED int 简单地转换为 SIGNED。
SELECT *, ((1 / log(1301980250 - cast(date as signed)) * 175) as weight FROM news_articles ORDER BY weight
回答by Joyce Babu
The problem was caused by unsigned integer overflow as suggested by wallyk. It can be solved by
该问题是由 wallyk 建议的无符号整数溢出引起的。可以通过以下方式解决
- using
SELECT *, ((1 / log((date - 1301980250) * -1)) * 175) as weight FROM news_articles ORDER BY weight;(This one worked for me) ` - Changing sql_mode parameter in my.cnf to
NO_UNSIGNED_SUBTRACTION(haven't checked this)
- 使用
SELECT *, ((1 / log((date - 1301980250) * -1)) * 175) as weight FROM news_articles ORDER BY weight;（这个对我有用）` - 将 my.cnf 中的 sql_mode 参数更改为
NO_UNSIGNED_SUBTRACTION（尚未检查）
回答by Ian Chadwick
This 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)
使用 IFNULL 设置默认值（时间戳可能为 0 是一个糟糕的默认值，实际上在这种情况下，您最好在 WHERE 子句中排除日期和空日期）
SELECT (123456 - IFNULL(date, 0)) AS leVar
SELECT (123456 - IFNULL(date, 0)) AS leVar
回答by wallyk
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.
2011-04-04 22:10:50 PDT (2011-04-05 05:10:50 UTC) 之后的任何日期值都会导致此错误，因为这会使表达式为负。
回答by fairjm
maybe you can use cast
也许你可以使用 cast
SELECT *, ((1 / log(1301980250 - cast(date AS SIGNED))) * 175) as weight FROM news_articles ORDER BY weight;
SELECT *, ((1 / log(1301980250 - cast(date AS SIGNED))) * 175) as weight FROM news_articles ORDER BY weight;
回答by Fabien Haddadi
Nobody mentionned that the log() function is only defined for strictly positive arguments. Watch for this when using substractions inside of log().
没有人提到 log() 函数仅针对严格的正参数定义。在 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.
至于最初的问题，解决的一个关键因素是告诉我们日期列的数据类型。如果它是 UNSIGNED，MySQL 可能不喜欢它。
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.
规则是 MySQL 的算术算法很差，当 A 的编码字节数少于 B AND B > A 时，无法弄清楚如何从另一个 A (= do AB) 中减去操作数 B。
e.g. A = 12 and is SMALLINT, B = 13 AS INT, then MySQL can't figure out what A-B is (-1 !)
例如 A = 12 并且是 SMALLINT，B = 13 AS INT，那么 MySQL 无法弄清楚 AB 是什么（-1 ！）
To make MySQL content, just expand the coding length of operand A. How? Using CAST(), or multiplying A by a decimal number.
要制作MySQL内容，只需扩展操作数A的编码长度即可。如何？使用 CAST()，或将 A 乘以十进制数。
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...
可以看出，在 MySQL 算法中处理符号的问题与其说是溢出问题，不如说是处理问题。微处理器，或者更好的人类，执行这种算术没有问题......
Using CAST() is the way, or for short, just provoke the implicit cast by multiplying operand A by 1. (or 1.0):
使用 CAST() 是一种方式，或者简而言之，只是通过将操作数 A 乘以 1（或 1.0）来激发隐式转换：
e.g
例如
1.*A - B
回答by kojow7
I just came across this issue doing an update on a field where the result ended up being less than 0.
我刚刚在对结果小于 0 的字段进行更新时遇到了这个问题。
Solution: Verify that none of your updates cause your result to be less than 0 on an unsigned field.
解决方案：验证您的任何更新都不会导致未签名字段上的结果小于 0。
