MYSQL 竟然 溢出了 第一次发现 呵呵

[wdvill]

竟然还有这情况

SELECT `bxd_id` , ABS( ent_id - area_id ) AS `wang`
FROM `gx_bxd_iw`
WHERE 1
LIMIT 0 , 30

上面是我的SQL语句

第一次看到这样

[vga]

能否确定是 mysql 问题还是客户端软件问题？

[feixianxxx]

ent_id - area_id
这2个什么字段类型

[ACMAIN_CHM]

显然不是MYSQL的问题。

用MYSQL命令行直接试一下，排除你软件的干扰，另外desc `gx_bxd_iw` 贴出来看一下。

[loveflea]

有unsigned的,默认模式下,可能会溢出,呵呵!

mysql> create table unsigned_test(end_id integer unsigned,start_id integer unsig
ned);
Query OK, 0 rows affected (0.16 sec)

mysql> insert into unsigned_test values(0,15);
Query OK, 1 row affected (0.03 sec)

mysql> select * from unsigned_test;
+--------+----------+
| end_id | start_id |
+--------+----------+
|      0 |       15 |
+--------+----------+
1 row in set (0.00 sec)

mysql> select end_id,start_id,(end_id-start_id) from unsigned_test;
+--------+----------+----------------------+
| end_id | start_id | (end_id-start_id)    |
+--------+----------+----------------------+
|      0 |       15 | 18446744073709551601 |
+--------+----------+----------------------+
1 row in set (0.02 sec)
mysql> select cast(0 as unsigned)-15;
+------------------------+
| cast(0 as unsigned)-15 |
+------------------------+
|   18446744073709551601 |
+------------------------+
1 row in set (0.00 sec)

[wdvill]

[Quote=引用 4 楼 loveflea 的回复:]

有unsigned的,默认模式下,可能会溢出,呵呵!

SQL code

mysql> create table unsigned_test(end_id integer unsigned,start_id integer unsig
ned);
Query OK, 0 rows affected (0.16 sec)

mysql> insert into unsigned_test ……
[/Quote]
顶LS
实践者万岁

[loveflea]

参考
no_unsigned_subtraction
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_no_unsigned_subtraction

MySQL supports arithmetic with both signed and unsigned 64-bit values. If you are using numeric operators (such as + or -) and one of the operands is an unsigned integer, the result is unsigned. You can override this by using the SIGNED and UNSIGNED cast operators to cast the operation to a signed or unsigned 64-bit integer, respectively.
mysql> SELECT CAST(1-2 AS UNSIGNED)
-> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
-> -1
Note that if either operand is a floating-point value, the result is a floating-point value and is not affected by the preceding rule. (In this context, DECIMAL column values are regarded as floating-point values.)
mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;
-> -1.0
If you are using a string in an arithmetic operation, this is converted to a floating-point number.

[feixianxxx]

[Quote=引用 4 楼 loveflea 的回复:]

有unsigned的,默认模式下,可能会溢出,呵呵!

SQL code

mysql> create table unsigned_test(end_id integer unsigned,start_id integer unsig
ned);
Query OK, 0 rows affected (0.16 sec)

mysql> insert into unsigned_test ……
[/Quote]
学习下

[netxuning]

[Quote=引用 5 楼 wdvill 的回复:]

引用 4 楼 loveflea 的回复:

有unsigned的,默认模式下,可能会溢出,呵呵!

SQL code

mysql> create table unsigned_test(end_id integer unsigned,start_id integer unsig
ned);
Query OK, 0 rows affected (0.16 sec)

mysq……
[/Quote]

对，实践者万岁！