MySQL BIGINT UNSIGNED VALUE 超出范围我的 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11698613/
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 My SQL
提问by Shawn
I'm getting the following error
我收到以下错误
#1690 - BIGINT UNSIGNED value is out of range in '(
legends.spawns.quantity-tmp_field)'
#1690 - BIGINT UNSIGNED 值超出 '(
legends.spawns.quantity-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_idis 0for all possible rows but 1 (I deleted a row to test the query)
spawns table - countgame_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?
知道如何解决这个错误吗？
回答by Ravinder Reddy
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;
在这种情况下，要使操作成功，请将值转换为无符号；
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_modeon 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.
另请参阅此处在论坛上回答的类似帖子。
回答by zwadder
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!!!
我遇到了同样的问题，它发生在 JOIN 上并且无法弄清楚发生了什么，最后是 ON 子句中的错字，我在其中放置了一个减号而不是等号。可能是愚蠢的，但我只是在大约 30 分钟内没有看到它，也许这可以帮助某人！！！
回答by Fabien Haddadi
To generalise the rule, MySQL will now refuse to substract an UNSIGNED operand from a SIGNED one.
为了概括该规则，MySQL 现在将拒绝从 SIGNED 操作数中减去 UNSIGNED 操作数。
Example : SELECT A - B;will fail if A is SIGNED whereas B is UNSIGNED.
示例：SELECT A - B;如果 A 是 SIGNED 而 B 是 UNSIGNED，则会失败。
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 因子添加到有符号操作数，因此它隐式地将其强制转换为 FLOAT，或者使用CAST (B AS SIGNED)，甚至交换 (B - A) 并相应地更改算法。
回答by Gonzi
I actualy found that question why I was searching for solution. If you have same problem as I do, try disabling "unsigned" parameter.
我实际上发现了这个问题，为什么我正在寻找解决方案。如果您遇到与我相同的问题，请尝试禁用“无符号”参数。
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.
因为如果该 matematic 操作的结果小于零，它将以“无符号”参数失败。
回答by Andron
回答by Asif Asghar
I had similar problem, This error also come if our column have 0value and we try to update it with -1value.
我有类似的问题，如果我们的列有0值并且我们尝试用-1值更新它，也会出现此错误。
In my case MySQL query were Failing if column1is already have value 0, i.e column1 = 0
在我的情况下，MySQL 查询失败，如果column1已经有值0，即column1 = 0
For example:
例如：
UPDATE `table1` SET `column1` = `column1` - 1 WHERE `column2` = XYZ
This give error
这给错误
BIGINT UNSIGNED value is out of range in ....
BIGINT UNSIGNED value is out of range in ....
To counter this problem
为了解决这个问题
UPDATE `table1`
SET `column1` = (
CASE WHEN `column1` < 1
THEN 0
ELSE (`column1` - 1)
end)
WHERE `column2` = 1
LIMIT 1
回答by hu wentao
I don't quite understand why everyone is saying unsigned. I have a special value in sql and also reported this error. I did it by converting this value to decimal.
我不太明白为什么每个人都说未签名。我在sql中有一个特殊值，也报了这个错误。我通过将此值转换为十进制来做到这一点。
cast(1000000000000000000 AS DECIMAL ( 35, 2 ))
回答by kklepper
sql_modeworked in the MySQL client and Adminer, but not in CodeIgniter, where it counts. Casting didn't help either.
sql_mode在 MySQL 客户端和 Adminer 中工作，但不在 CodeIgniter 中工作，它很重要。铸造也没有帮助。
A simple arithmetic operation did the trick:
一个简单的算术运算就成功了：
error
错误
id - id_ex*1000000000 = id_sm
works
作品
id_sm + id_ex*1000000000 = id
回答by Philippe
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)：BIGINT UNSIGNED 值超出范围在 '(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
菲利普
