SELECT *,`diggDing`-`diggCai`-`diggErr` FROM tbname 结果是什么
结果是正常的,有结果。但是加上了ORDER BY 这几个就报错#1690. -- 1正常 SELECT *,`diggDing`-`diggCai`-`diggErr` AS rating FROM tbname -- 2报错 SELECT *,`diggDing`-`diggCai`-`diggErr` AS rating FROM tbname ORDER BY rating DESC
检查ID=1015的记录SELECT *,diggDing-diggCai-diggErr AS rating FROM tbname WHERE id<>1015 ORDER BY rating DESC LIMIT 30
id=1015的记录就是那个“(1015, 49, 34, 16),”没有其他的字段。SELECT *,diggDing-diggCai-diggErr AS rating FROM tbname WHERE id<>1015 ORDER BY rating DESC LIMIT 30 --还是会报错,数据库不止这些数据。不知道为什么加unsigned会有报错 #1690 - BIGINT UNSIGNED value is out of range in '((`test`.`tbname`.`diggDing` - `test`.`tbname`.`diggCai`) - `test`.`tbname`.`diggErr`)'
结果是什么
结果是正常的,有结果。但是加上了ORDER BY 这几个就报错#1690.
-- 1正常
SELECT *,`diggDing`-`diggCai`-`diggErr` AS rating FROM tbname -- 2报错
SELECT *,`diggDing`-`diggCai`-`diggErr` AS rating FROM tbname ORDER BY rating DESC
Query OK, 0 rows affected (0.02 sec)mysql> insert into test3 values(1);
Query OK, 1 row affected (0.00 sec)mysql> select * from test3;
+------------+
| a |
+------------+
| 0000000001 |
+------------+
1 row in set (0.00 sec)
INT(XX) 不管后面的XX数字是多少都没有实际作用。一个INT只占四个字节而已。
奇怪的是我本地这样是错误的,但是在远程却是可以的。本地:WIN32 x86-wamp apache2.2 php5.4 mysql5.5
远程:LINUX ,PHP 5.3,MYSQL 5.0.51a(DreamHost)
没有,最大的不超过100mysql> use dbcom
Database changed
mysql> SELECT MAX(diggDing), MAX(diggCai),MAX(diggErr) FROM tbname
-> ;
+---------------+--------------+--------------+
| MAX(diggDing) | MAX(diggCai) | MAX(diggErr) |
+---------------+--------------+--------------+
| 12 | 0 | 3 |
+---------------+--------------+--------------+
1 row in set (0.00 sec)
是否有<0的情况
insert into
语句这样别人可以直接在自己的机器上模拟测试你的问题。
很奇怪,现在我把那个unsigned去掉之后就可以了。mysql> DESC gk_info_infosite;
+-----------------+---------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| diggErr | int(11) | NO | | 0 | |
| diggDing | int(11) | NO | | NULL | |
| diggCai | int(11) | NO | | 0 | |
+-----------------+---------------------+------+-----+------------+----------------+
--正常了!
SELECT *,`diggDing`-`diggCai`-`diggErr` AS rating
FROM gk_info_infosite
WHERE 1
ORDER BY rating DESC
LIMIT 100 , 20
`id` int(11) NOT NULL AUTO_INCREMENT,
`diggErr` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '浏览者提交访问失败的统计',
`diggDing` int(11) unsigned NOT NULL,
`diggCai` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '踩',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1626 ;--
-- 转存表中的数据 `tbname`
--INSERT INTO `tbname` (`id`, `diggErr`, `diggDing`, `diggCai`) VALUES
(1001, 33, 246, 68),
(1002, 31, 111, 22),
(1003, 83, 839, 104),
(1004, 99, 821, 126),
(1005, 24, 599, 103),
(1006, 70, 543, 64),
(1007, 3, 883, 129),
(1008, 23, 789, 82),
(1009, 6, 292, 95),
(1010, 99, 106, 6),
(1011, 24, 633, 11),
(1012, 64, 852, 148),
(1013, 99, 354, 150),
(1014, 2, 218, 17),
(1015, 49, 34, 16),
(1016, 6, 504, 149),
(1017, 77, 424, 127),
(1018, 94, 607, 20),
(1019, 85, 764, 126),
(1020, 64, 1001, 104),
(1021, 54, 697, 90),
(1022, 38, 491, 16),
(1023, 40, 364, 98),
(1024, 8, 346, 63),
(1025, 86, 639, 2),
(1026, 51, 153, 77),
(1027, 2, 851, 81),
(1028, 66, 795, 98),
(1029, 29, 423, 73),
(1030, 57, 728, 57);SELECT *,diggDing-diggCai-diggErr AS rating
FROM tbname
ORDER BY rating DESC LIMIT 30--查询结果:
#1690 - BIGINT UNSIGNED value is out of range in '((`test`.`tbname`.`diggDing` - `test`.`tbname`.`diggCai`) - `test`.`tbname`.`diggErr`)'
也么有,后来发现去掉unsigned就可以了。但是unsigned为什么不能用呢,好奇怪。
创建和测试数据在 #12楼有。
FROM tbname WHERE id<>1015
ORDER BY rating DESC LIMIT 30
id=1015的记录就是那个“(1015, 49, 34, 16),”没有其他的字段。SELECT *,diggDing-diggCai-diggErr AS rating
FROM tbname WHERE id<>1015
ORDER BY rating DESC LIMIT 30
--还是会报错,数据库不止这些数据。不知道为什么加unsigned会有报错
#1690 - BIGINT UNSIGNED value is out of range in '((`test`.`tbname`.`diggDing` - `test`.`tbname`.`diggCai`) - `test`.`tbname`.`diggErr`)'
-> FROM tbname WHERE id<>1015
-> ORDER BY rating DESC LIMIT 30;
+------+---------+----------+---------+--------+
| id | diggErr | diggDing | diggCai | rating |
+------+---------+----------+---------+--------+
| 1020 | 64 | 1001 | 104 | 833 |
| 1027 | 2 | 851 | 81 | 768 |
| 1007 | 3 | 883 | 129 | 751 |
| 1008 | 23 | 789 | 82 | 684 |
| 1003 | 83 | 839 | 104 | 652 |
| 1012 | 64 | 852 | 148 | 640 |
| 1028 | 66 | 795 | 98 | 631 |
| 1030 | 57 | 728 | 57 | 614 |
| 1011 | 24 | 633 | 11 | 598 |
| 1004 | 99 | 821 | 126 | 596 |
| 1021 | 54 | 697 | 90 | 553 |
| 1019 | 85 | 764 | 126 | 553 |
| 1025 | 86 | 639 | 2 | 551 |
| 1018 | 94 | 607 | 20 | 493 |
| 1005 | 24 | 599 | 103 | 472 |
| 1022 | 38 | 491 | 16 | 437 |
| 1006 | 70 | 543 | 64 | 409 |
| 1016 | 6 | 504 | 149 | 349 |
| 1029 | 29 | 423 | 73 | 321 |
| 1024 | 8 | 346 | 63 | 275 |
| 1023 | 40 | 364 | 98 | 226 |
| 1017 | 77 | 424 | 127 | 220 |
| 1014 | 2 | 218 | 17 | 199 |
| 1009 | 6 | 292 | 95 | 191 |
| 1001 | 33 | 246 | 68 | 145 |
| 1013 | 99 | 354 | 150 | 105 |
| 1002 | 31 | 111 | 22 | 58 |
| 1026 | 51 | 153 | 77 | 25 |
| 1010 | 99 | 106 | 6 | 1 |
+------+---------+----------+---------+--------+
29 rows in set (0.00 sec)mysql>检查ID=1015,diggDing-diggCai-diggErr结果是什么
Database changed--报错了
mysql> SELECT *,diggDing-diggCai-diggErr AS rating
-> FROM tbname WHERE id=1015;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(('34' - '16') - '
49')'
--也报错了
mysql> SELECT *,diggDing-diggCai-diggErr AS rating
-> FROM tbname WHERE id<>1015;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '((`test`.`tbname`.
`diggDing` - `test`.`tbname`.`diggCai`) - `test`.`tbname`.`diggErr`)'我自己的电脑的数据库服务器 服务器: localhost via TCP/IP
软件: MySQL
软件版本: 5.5.24-log - MySQL Community Server (GPL)
协议版本: 10
用户: root@localhost
服务器字符集: UTF-8 Unicode (utf8)网站服务器 Apache/2.2.22 (Win32) PHP/5.4.3
数据库客户端版本: libmysql - mysqlnd 5.0.10 - 20111026 - $Id: b0b3b15c693b7f6aeb3aa66b646fee339f175e39 $
PHP 扩展: mysqli 文档
显示 PHP 信息CREATE TABLE IF NOT EXISTS `tbname` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`diggErr` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '浏览者提交访问失败的统计',
`diggDing` int(11) unsigned NOT NULL,
`diggCai` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '踩',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1626 ;--
-- 转存表中的数据 `tbname`
--INSERT INTO `tbname` (`id`, `diggErr`, `diggDing`, `diggCai`) VALUES
(1001, 33, 246, 68),
(1002, 31, 111, 22),
(1003, 83, 839, 104),
(1004, 99, 821, 126),
(1005, 24, 599, 103),
(1006, 70, 543, 64),
(1007, 3, 883, 129),
(1008, 23, 789, 82),
(1009, 6, 292, 95),
(1010, 99, 106, 6),
(1011, 24, 633, 11),
(1012, 64, 852, 148),
(1013, 99, 354, 150),
(1014, 2, 218, 17),
(1015, 49, 34, 16),
(1016, 6, 504, 149),
(1017, 77, 424, 127),
(1018, 94, 607, 20),
(1019, 85, 764, 126),
(1020, 64, 1001, 104),
(1021, 54, 697, 90),
(1022, 38, 491, 16),
(1023, 40, 364, 98),
(1024, 8, 346, 63),
(1025, 86, 639, 2),
(1026, 51, 153, 77),
(1027, 2, 851, 81),
(1028, 66, 795, 98),
(1029, 29, 423, 73),
(1030, 57, 728, 57),
(1031, 18, 371, 113),
(1032, 23, 671, 133),
(1033, 73, 242, 150),
(1034, 80, 185, 150),
(1035, 59, 206, 141),
(1036, 95, 476, 135),
(1037, 67, 762, 96),
(1038, 19, 377, 3),
(1039, 54, 602, 92),
(1040, 45, 869, 61),
(1041, 69, 542, 35),
(1042, 8, 106, 103),
(1043, 20, 903, 140),
(1044, 6, 202, 77),
(1045, 37, 280, 48),
(1046, 47, 805, 59),
(1047, 56, 179, 88),
(1048, 27, 484, 85),
(1049, 3, 888, 65),
(1050, 8, 962, 16),
(1051, 29, 160, 19),
(1052, 76, 916, 58),
(1053, 68, 97, 34),
(1054, 10, 736, 122),
(1055, 76, 388, 55),
(1056, 53, 734, 85),
(1057, 22, 490, 62),
(1058, 39, 258, 108),
(1059, 43, 820, 148),
(1060, 65, 318, 38),
(1061, 32, 135, 126),
(1062, 24, 711, 99),
(1063, 59, 154, 145),
(1064, 7, 638, 63),
(1065, 89, 729, 27),
(1066, 25, 729, 101),
(1067, 62, 454, 10),
(1068, 50, 85, 40),
(1069, 83, 61, 54),
(1070, 30, 55, 64),
(1071, 23, 74, 130),
(1072, 63, 198, 80),
(1073, 79, 772, 50),
(1074, 31, 274, 82),
(1075, 81, 44, 56),
(1076, 45, 402, 16),
(1077, 21, 851, 105),
(1078, 87, 70, 34),
(1079, 55, 788, 5),
(1080, 53, 733, 80),
(1081, 10, 300, 131),
(1082, 6, 301, 102),
(1083, 22, 607, 10),
(1084, 67, 130, 20),
(1085, 65, 830, 127),
(1086, 29, 742, 135),
(1087, 64, 232, 72),
(1088, 48, 931, 143),
(1089, 33, 957, 119),
(1090, 97, 994, 71),
(1091, 46, 95, 130),
(1093, 95, 496, 23),
(1094, 90, 199, 9),
(1095, 57, 484, 103),
(1096, 70, 833, 67),
(1097, 12, 716, 41),
(1098, 100, 77, 22),
(1099, 75, 238, 44),
(1100, 22, 958, 35),
(1101, 48, 73, 107);/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+----------+
| 34-16-49 |
+----------+
| -31 |
| -31 |
| -31 |
| -31 |
| -31 |
| -31 |
| -31 |
| -31 |
| -31 |
| -31 |
+----------+
10 rows in set (0.00 sec)不是很明白,确实是小于0了。但是order by也限定?不是很理解
检查:
id diggErr diggDing diggCai
1070 30 55 64
1071 23 74 130
1075 81 44 56
1091 46 95 130
1101 48 73 107
看看diggDing-diggCai是什么结果
是UNSIGNED
49')'结果超出 UNSIGNED 的值域了。UNSIGNED 的取值范围是 0 - 4294967295