BIGINT无符号值超出了SQL的范围

I'm getting the following error

我得到了下面的错误。

#1690 - BIGINT UNSIGNED value is out of range in '(legends.spawns.quantity - tmp_field)'

#1690 - BIGINT UNSIGNED value is out of range in '(传说，spawns)。数量- tmp_field)”

Here is my query

这是我的查询

SELECT drops.common, drops.uncommon, drops.rare, drops.legendary, spawns . *
     , ( quantity - COUNT( game_moblist.spawn_id ) ) AS quantity_to_spawn
     , mobs . * 
FROM spawns
     LEFT JOIN mobs
          USING ( mob_id ) 
     LEFT JOIN game_moblist
          USING ( spawn_id ) 
     LEFT JOIN drops ON ( 
               SELECT MAX( level ) 
                 FROM drops
                WHERE drops.type = mobs.drop_list
                  AND drops.level <= spawns.level ) 
GROUP BY spawn_id
HAVING quantity_to_spawn >=0
       AND next_spawn <=0

I've been staring at it a while the query is long I'm sorry.

我一直盯着它看，查询时间很长，很抱歉。

spawns table - count game_moblist.spawn_id is 0 for all possible rows but 1 (I deleted a row to test the query)

生成表格-计数game_moblist。对于所有可能的行，spawn_id为0，但是是1(删除一行以测试查询)

The data otherwise is quite long and irrelevant to my question I think

否则的话，数据就会很长，和我的问题无关

Any idea how to get around this error?

你知道怎么解决这个错误吗?

7 个解决方案

#1

Please read "Out-of-Range and Overflow Handling".
It says:

请阅读“超出范围和溢出处理”。它说:

As of MySQL 5.5.5, overflow during numeric expression evaluation results in an error. For example, the largest signed BIGINT value is 9223372036854775807, so the following expression produces an error.

从MySQL 5.5.5开始，数值表达式计算期间溢出导致错误。例如，最大签名的BIGINT值是9223372036854775807，因此下面的表达式产生一个错误。

mysql> SELECT 9223372036854775807 + 1;

ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'

To enable the operation to succeed in this case, convert the value to unsigned;

为了使操作在这种情况下成功，将值转换为unsigned;

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
+-------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) + 1 |
+-------------------------------------------+
|                       9223372036854775808 |
+-------------------------------------------+

A change to part of your query, as following, would solve the issue.

对查询的一部分进行更改，如下所示，将解决这个问题。

( CAST( quantity AS SIGNED ) - COUNT( game_moblist.spawn_id ) ) AS quantity_to_spawn

Otherwise you may require to change the sql_mode on unsigned operations.

否则，您可能需要在无符号操作上更改sql_mode。

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

and then run your query to get desired output.

然后运行查询以获得所需的输出。

See also a similar posting answered on a forum here.

在这里的论坛上也可以看到类似的帖子。

#2

I actualy found that question why I was searching for solution. If you have same problem as I do, try disabling "unsigned" parameter.

我发现了我为什么要寻找解决方案的问题。如果您有和我一样的问题，请尝试禁用“unsigned”参数。

It is quite possible that your code fails here:

您的代码很可能在这里失败:

(
quantity - COUNT( game_moblist.spawn_id )
)

because if result of that matematic operation is less than zero it will fail with "unsigned" parameter.

因为如果该操作的结果小于零，则它将以“未签名”参数失败。

#3

I had the same problem, it occurred on a JOIN and couldn't figure out what was going on, in the end it was typo in the ON clause where I placed a minus sign instead of an equal sign. Might be stupid but I just didn't see it for about 30 minutes and maybe this could help someone!!!

我也遇到了同样的问题，它发生在一个连接上，无法弄清楚发生了什么，最后它出现在on子句中，在那里我放了一个负号而不是等号。可能很傻，但是我有30分钟没看到它了，也许这能帮助某些人!!

#4

Additional way is to use MySQL IF operator. This helped me when both columns were BIGINT and Unsigned.

另外一种方法是使用MySQL IF操作符。当两个列都是BIGINT和Unsigned时，这对我很有帮助。

#5

Another possible cause seems to be how the type of the result variable is allocated.

另一个可能的原因似乎是如何分配结果变量的类型。

eg.

如。

mysql> select (total_balance_06 * 0.045/(1-(1/1.045)^term_06) + unsec_instalments)/income from tbl_EUR_PDH;

fails with

失败与

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(1 - ((1 / 1.045) ^ markov.tbl_EUR_PDH.term_06))'

错误1690(22003):长整型数字无符号值的范围”(1 -((1/1.045)^ markov.tbl_EUR_PDH.term_06))”

whereas

而

mysql> select (total_balance_06 * 0.045/(1.0-(1.0/1.045)^term_06) + unsec_instalments)/income from tbl_EUR_PDH;

does what one would expect (note that I simply replace "1" by "1.0")

是否实现了人们的期望(注意，我只是将“1”替换为“1.0”)

Philippe

菲利普

#6

To generalise the rule, MySQL will now refuse to substract an UNSIGNED operand from a SIGNED one.

为了推广这一规则，MySQL现在将拒绝从已签名的操作数减去未签名的操作数。

Example : SELECT A - B; will fail if A is SIGNED whereas B is UNSIGNED.

示例:选择A - B;如果A是有符号的，而B是无符号的，则会失败。

Workarounds: Add 1.0 factor to the signed operand, so it implicitly casts it to FLOAT, or use CAST (B AS SIGNED), or even swap (B - A) and change the algorithm accordingly.

变通方法:向已签名的操作数添加1.0因子，因此它隐式地将它转换为浮点数，或者使用CAST (B作为签名)，甚至交换(B - A)，并相应地更改算法。

#7

sql_mode worked in the MySQL client and Adminer, but not in CodeIgniter, where it counts. Casting didn't help either.

sql_mode可以在MySQL客户端和Adminer中工作，但不能在CodeIgniter中工作，因为它在CodeIgniter中很重要。铸造现状没有任何帮助。

A simple arithmetic operation did the trick:

一个简单的算术运算就成功了:

error

id - id_ex*1000000000 = id_sm

works

id_sm + id_ex*1000000000 = id