BIGINT UNSIGNED值超出范围

时间:2022-03-21 16:44:47

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?

有什么帮助吗?

6 个解决方案

#1


59  

I recently ran into this and found the most reasonable solution to simply cast any UNSIGNED ints as SIGNED.

我最近遇到了这个并找到了最合理的解决方案,只需将任何UNSIGNED整数转换为SIGNED。

 SELECT *, ((1 / log(1301980250 - cast(date as signed)) * 175) as weight FROM news_articles ORDER BY weight

#2


15  

The problem was caused by unsigned integer overflow as suggested by wallyk. It can be solved by

问题是由wallyk建议的无符号整数溢出引起的。它可以解决

  1. using SELECT *, ((1 / log((date - 1301980250) * -1)) * 175) as weight FROM news_articles ORDER BY weight; (This one worked for me) `
  2. 使用SELECT *,((1 / log((date - 1301980250)* -1))* 175)作为权重来自news_articles ORDER BY权重; (这个对我有用)`
  3. Changing sql_mode parameter in my.cnf to NO_UNSIGNED_SUBTRACTION (haven't checked this)
  4. 将my.cnf中的sql_mode参数更改为NO_UNSIGNED_SUBTRACTION(尚未检查)

#3


3  

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)之后的任何日期值都会导致此错误,因为这会使表达式为负。

#4


2  

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子句中的null和null日期)

SELECT (123456 - IFNULL(date, 0)) AS leVar

SELECT(123456 - IFNULL(date,0))AS leVar

#5


1  

maybe you can use 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)作为权重来自news_articles ORDER BY权重;

#6


0  

Nobody mentionned that the log() function is only defined for strictly positive arguments. Watch for this when using substractions inside of log().

没有人提到log()函数只是为严格的正参数定义的。在log()中使用substractions时要注意这一点。

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可能不喜欢它。

#1


59  

I recently ran into this and found the most reasonable solution to simply cast any UNSIGNED ints as SIGNED.

我最近遇到了这个并找到了最合理的解决方案,只需将任何UNSIGNED整数转换为SIGNED。

 SELECT *, ((1 / log(1301980250 - cast(date as signed)) * 175) as weight FROM news_articles ORDER BY weight

#2


15  

The problem was caused by unsigned integer overflow as suggested by wallyk. It can be solved by

问题是由wallyk建议的无符号整数溢出引起的。它可以解决

  1. using SELECT *, ((1 / log((date - 1301980250) * -1)) * 175) as weight FROM news_articles ORDER BY weight; (This one worked for me) `
  2. 使用SELECT *,((1 / log((date - 1301980250)* -1))* 175)作为权重来自news_articles ORDER BY权重; (这个对我有用)`
  3. Changing sql_mode parameter in my.cnf to NO_UNSIGNED_SUBTRACTION (haven't checked this)
  4. 将my.cnf中的sql_mode参数更改为NO_UNSIGNED_SUBTRACTION(尚未检查)

#3


3  

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)之后的任何日期值都会导致此错误,因为这会使表达式为负。

#4


2  

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子句中的null和null日期)

SELECT (123456 - IFNULL(date, 0)) AS leVar

SELECT(123456 - IFNULL(date,0))AS leVar

#5


1  

maybe you can use 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)作为权重来自news_articles ORDER BY权重;

#6


0  

Nobody mentionned that the log() function is only defined for strictly positive arguments. Watch for this when using substractions inside of log().

没有人提到log()函数只是为严格的正参数定义的。在log()中使用substractions时要注意这一点。

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可能不喜欢它。