Geek Answers Handbook

MySQL integer unsigned arithmetic problems?

Can MySQL (5.0.45) do weird internal tricks with unsigned mathematicians? I store unsigned integers, but when choosing 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)

I suppose this is due to the fact that the subtraction is negative and thus it is trying to match the results with unsigned and overcrowded? I can solve this, apparently changing everything to signed ones, but I would rather have a slightly more positive space with my 32-bit integers.

I haven't come across this before in MySQL, and I'm sure I did a lot with unsigned MySQL arithmetic; is this a common problem?

+5
3

, , . , NO_UNSIGNED_SUBTRACTION SQL.

, , .

+7

:

mysql> select cast(cast(a-b as unsigned) as signed)/c from tt;

+-----------------------------------------+
| cast(cast(a-b as unsigned) as signed)/c |
+-----------------------------------------+
|                       -659.771639688953 | 
+-----------------------------------------+
1 row in set (0.00 sec)

: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

+3

Yep, 64- . 32- , 64, unsigned.

+2

More articles:


All Articles