56,677
社区成员
发帖
与我相关
我的任务
分享
+-----------------+---------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| diggErr | int(11) unsigned | NO | | 0 | |
| diggDing | int(11) unsigned | NO | | NULL | |
| diggCai | int(11) unsigned | NO | | 0 | |
+-----------------+---------------------+------+-----+------------+-------------
SELECT * FROM tbname WHERE 1
RDER BY `diggDing`-`diggCai`-`diggErr` DESC
LIMIT 100 , 20
#1690 - BIGINT UNSIGNED value is out of range in '((`dbcom`.`tbname`.`diggDing` - `dbcom`.`tbname`.`diggCai`) - `dbcom`.`tbname`.`diggErr`)'
mysql> SELECT 34-16-49 FROM tbname LIMIT 10;
+----------+
| 34-16-49 |
+----------+
| -31 |
| -31 |
| -31 |
| -31 |
| -31 |
| -31 |
| -31 |
| -31 |
| -31 |
| -31 |
+----------+
10 rows in set (0.00 sec)
不是很明白,确实是小于0了。但是order by也限定?不是很理解
mysql> USE test;
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 */;
mysql> SELECT *,diggDing-diggCai-diggErr AS rating
-> 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结果是什么
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`)'
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);
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`)'
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
mysql> 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)
本地:WIN32 x86-wamp apache2.2 php5.4 mysql5.5
远程:LINUX ,PHP 5.3,MYSQL 5.0.51a(DreamHost)