MySQL整数无符号算术问题？ [英] MySQL integer unsigned arithmetic problems?

问题描述

MySQL（5.0.45）是否喜欢使用无符号数学进行奇怪的内部类型转换？我存储未签名的整数，但在选择基本算术时，我得到了令人发指的数字：

Does MySQL (5.0.45) like to do strange internal typecasts with unsigned maths? I am storing integers unsigned but when selecting basic arithmetic I get outrageous numbers:

mysql> create table tt ( a integer unsigned , b integer unsigned , c float );
Query OK, 0 rows affected (0.41 sec)

mysql> insert into tt values (215731,216774,1.58085);
Query OK, 1 row affected (0.00 sec)

mysql> select a,b,c from tt;
+--------+--------+---------+
| a      | b      | c       |
+--------+--------+---------+
| 215731 | 216774 | 1.58085 |
+--------+--------+---------+
1 row in set (0.02 sec)

mysql> select (a-b)/c from tt;
+---------------------+
| (a-b)/c             |
+---------------------+
| 1.1668876878652e+19 |
+---------------------+
1 row in set (0.00 sec)

mysql> -- WHAT?
mysql> select a-b from tt;
+----------------------+
| a-b                  |
+----------------------+
| 18446744073709550573 |
+----------------------+
1 row in set (0.02 sec)

我认为这与减法为负的事实有关，因此它试图将结果映射到无符号和溢出？我可以通过将所有内容更改为已签名来解决这个问题，但我更倾向于使用32位整数来获得更多正空格。

I assume this has to do with the fact that the subtraction is negative and thus it is trying to map the results into an unsigned and overflowing? I can solve this apparently by changing everything to signed, but I'd prefer to have a little more positive space with my 32-bit integers.

我还没遇到过之前在MySQL上，我很确定我已经完成了很多未签名的MySQL算法;这是一个常见问题吗？

I have not run into this before on MySQL and I'm pretty certain I've done lots with unsigned MySQL arithmetic; is this a common problem?

推荐答案

如果减法运算符的左侧或右侧是无符号的，结果也是无符号的。您可以通过设置 <$ c $来更改此设置c> NO_UNSIGNED_SUBTRACTION SQL模式。

If either the left-hand side or the right-hand side of the subtraction operator is unsigned, the result is unsigned as well. You can change this by setting the NO_UNSIGNED_SUBTRACTION SQL mode.

或者，您也可以显式地将无符号值转换为有符号的bigint值，然后进行减法。

Alternatively, you can also explicitly cast your unsigned values to be signed bigint values and then do the subtraction.

这篇关于MySQL整数无符号算术问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！