mysql - BIGINT UNSIGNED VALUE 超出范围 Mysql

Question

我收到以下错误

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

这是我的问题

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

我一直盯着它看，查询很长，对不起。

spawns 表 - 计数 game_moblist.spawn_id 对于所有可能的行都是 0 但 1(我删除了一行以测试查询)

否则数据很长，我认为与我的问题无关

知道如何解决这个错误吗？

Answer 1

请阅读“Out-of-Range and Overflow Handling”。
它说:

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> SELECT 9223372036854775807 + 1;

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

为了使操作在这种情况下能够成功，将值转换为无符号；

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

如下所示，对您的部分查询进行更改可以解决该问题。

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

否则，您可能需要更改未签名操作的 sql_mode。

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

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

另请参阅论坛上回答的类似帖子 here 。