今天在执行SQL的时候,脑子转不过玩来了
update email_stat_base_send_20120416
set SEND_STATUS='4',SEND_RESULT=-1,BACK_TIME=now(),ADD_TIME=now()
where SEND_STATUS='0' and ACTIVITY_ID=9671;
commit;
select SEND_STATUS ,SEND_RESULT,BACK_TIME from email_stat_base_send_20120416
where SEND_STATUS='4' and ACTIVITY_ID=9671;
查询结果中为什么SEND_RESULT有-1 有0,有1 ,没有成功改成-1呢?
这是因为表中原来有SEND_STATUS='4'的SEND_RESULT是别的值, 而我们更新的只是SEND_STATUS='0' and ACTIVITY_ID=9671;的记录
select ...where SEND_STATUS='4' and ACTIVITY_ID=9671;选择的范围较大,包括了原来SEND_STATUS是4的记录和刚刚由0改为4的记录。
所以看到查询结果中 SEND_RESULT并不全部是-1!!!!
举例说明。
CREATE TABLE `testbobo` (
`ITEM_SEND_ID` bigint(20) NOT NULL AUTO_INCREMENT,
`MESSAGE_NAME` varchar(100) DEFAULT NULL,
`MESSAGE_ID` varchar(100) DEFAULT NULL,
`ACTIVITY_ID` bigint(20) NOT NULL,
`COM_ID` bigint(20) NOT NULL,
`LINKMAN_EMAIL` varchar(100) NOT NULL,
`EMAIL_DOMAIN` varchar(100) NOT NULL,
`SEND_RESULT` int(11) DEFAULT NULL,
`SEND_STATUS` char(1) NOT NULL COMMENT '默认为0 0:未发送,1:发送成功,2:发送终止,3:硬退,4:软退',
`BACK_TIME` datetime DEFAULT NULL,
`ADD_TIME` datetime NOT NULL DEFAULT '1900-01-01 00:00:00' COMMENT '用户第一次添加信息的时间',
`SEND_TIME` datetime DEFAULT NULL,
`LINKMAN_ID` bigint(20) NOT NULL,
`UPDATE_COUNT` int(11) DEFAULT NULL,
PRIMARY KEY (`ITEM_SEND_ID`),
KEY `index_addtime` (`ADD_TIME`) USING BTREE,
KEY `idx_EMAIL_STAT_BASE_SEND_1` (`LINKMAN_ID`,`ACTIVITY_ID`,`COM_ID`),
KEY `idx_testbobo _1` (`COM_ID`,`ACTIVITY_ID`,`ADD_TIME`),
KEY `idx_testbobo _3` (`ACTIVITY_ID`,`SEND_STATUS`,`ADD_TIME`),
KEY `idx_testbobo_2` (`MESSAGE_ID`,`ACTIVITY_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=176580756 DEFAULT CHARSET=utf8
insert into testbobo (SEND_STATUS, ACTIVITY_ID,SEND_RESULT) values (0,9671,5);
insert into testbobo (SEND_STATUS, ACTIVITY_ID,SEND_RESULT) values (4,9671,0);
insert into testbobo (SEND_STATUS, ACTIVITY_ID,SEND_RESULT) values (4,9671,0);
insert into testbobo (SEND_STATUS, ACTIVITY_ID,SEND_RESULT) values (4,9671,0);
insert into testbobo (SEND_STATUS, ACTIVITY_ID,SEND_RESULT) values (4,9671,0);
mysql> select SEND_STATUS, ACTIVITY_ID,SEND_RESULT from testbobo ;
+-------------+-------------+-------------+
| SEND_STATUS | ACTIVITY_ID | SEND_RESULT |
+-------------+-------------+-------------+
| 4 | 9671 | -1 |
| 4 | 9671 | 0 |
| 0 | 9671 | 5 |
| 4 | 9671 | 0 |
| 4 | 9671 | 0 |
| 4 | 9671 | 0 |
| 4 | 9671 | 0 |
+-------------+-------------+-------------+
7 rows in set (0.00 sec)
update testbobo
set SEND_STATUS='4',SEND_RESULT=-1,BACK_TIME=now(),ADD_TIME=now()
where SEND_STATUS='0' and ACTIVITY_ID=9671;
mysql> select SEND_STATUS, ACTIVITY_ID,SEND_RESULT from testbobo
+-------------+-------------+-------------+
| SEND_STATUS | ACTIVITY_ID | SEND_RESULT |
+-------------+-------------+-------------+
| 4 | 9671 | -1 |
| 4 | 9671 | 0 |
| 4 | 9671 | -1 |
| 4 | 9671 | 0 |
| 4 | 9671 | 0 |
| 4 | 9671 | 0 |
| 4 | 9671 | 0 |
+-------------+-------------+-------------+
7 rows in set (0.00 sec)
看到,满足条件where SEND_STATUS='0' and ACTIVITY_ID=9671;的成功更新了(SEND_RESULT由5改成了-1)
其他 SEND_STATUS='4‘的没有更新(SEND_RESULT 还是0没有改成-1)。因为不满足条件
update email_stat_base_send_20120416
set SEND_STATUS='4',SEND_RESULT=-1,BACK_TIME=now(),ADD_TIME=now()
where SEND_STATUS='0' and ACTIVITY_ID=9671;
commit;
select SEND_STATUS ,SEND_RESULT,BACK_TIME from email_stat_base_send_20120416
where SEND_STATUS='4' and ACTIVITY_ID=9671;
查询结果中为什么SEND_RESULT有-1 有0,有1 ,没有成功改成-1呢?
这是因为表中原来有SEND_STATUS='4'的SEND_RESULT是别的值, 而我们更新的只是SEND_STATUS='0' and ACTIVITY_ID=9671;的记录
select ...where SEND_STATUS='4' and ACTIVITY_ID=9671;选择的范围较大,包括了原来SEND_STATUS是4的记录和刚刚由0改为4的记录。
所以看到查询结果中 SEND_RESULT并不全部是-1!!!!
举例说明。
CREATE TABLE `testbobo` (
`ITEM_SEND_ID` bigint(20) NOT NULL AUTO_INCREMENT,
`MESSAGE_NAME` varchar(100) DEFAULT NULL,
`MESSAGE_ID` varchar(100) DEFAULT NULL,
`ACTIVITY_ID` bigint(20) NOT NULL,
`COM_ID` bigint(20) NOT NULL,
`LINKMAN_EMAIL` varchar(100) NOT NULL,
`EMAIL_DOMAIN` varchar(100) NOT NULL,
`SEND_RESULT` int(11) DEFAULT NULL,
`SEND_STATUS` char(1) NOT NULL COMMENT '默认为0 0:未发送,1:发送成功,2:发送终止,3:硬退,4:软退',
`BACK_TIME` datetime DEFAULT NULL,
`ADD_TIME` datetime NOT NULL DEFAULT '1900-01-01 00:00:00' COMMENT '用户第一次添加信息的时间',
`SEND_TIME` datetime DEFAULT NULL,
`LINKMAN_ID` bigint(20) NOT NULL,
`UPDATE_COUNT` int(11) DEFAULT NULL,
PRIMARY KEY (`ITEM_SEND_ID`),
KEY `index_addtime` (`ADD_TIME`) USING BTREE,
KEY `idx_EMAIL_STAT_BASE_SEND_1` (`LINKMAN_ID`,`ACTIVITY_ID`,`COM_ID`),
KEY `idx_testbobo _1` (`COM_ID`,`ACTIVITY_ID`,`ADD_TIME`),
KEY `idx_testbobo _3` (`ACTIVITY_ID`,`SEND_STATUS`,`ADD_TIME`),
KEY `idx_testbobo_2` (`MESSAGE_ID`,`ACTIVITY_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=176580756 DEFAULT CHARSET=utf8
insert into testbobo (SEND_STATUS, ACTIVITY_ID,SEND_RESULT) values (0,9671,5);
insert into testbobo (SEND_STATUS, ACTIVITY_ID,SEND_RESULT) values (4,9671,0);
insert into testbobo (SEND_STATUS, ACTIVITY_ID,SEND_RESULT) values (4,9671,0);
insert into testbobo (SEND_STATUS, ACTIVITY_ID,SEND_RESULT) values (4,9671,0);
insert into testbobo (SEND_STATUS, ACTIVITY_ID,SEND_RESULT) values (4,9671,0);
mysql> select SEND_STATUS, ACTIVITY_ID,SEND_RESULT from testbobo ;
+-------------+-------------+-------------+
| SEND_STATUS | ACTIVITY_ID | SEND_RESULT |
+-------------+-------------+-------------+
| 4 | 9671 | -1 |
| 4 | 9671 | 0 |
| 0 | 9671 | 5 |
| 4 | 9671 | 0 |
| 4 | 9671 | 0 |
| 4 | 9671 | 0 |
| 4 | 9671 | 0 |
+-------------+-------------+-------------+
7 rows in set (0.00 sec)
update testbobo
set SEND_STATUS='4',SEND_RESULT=-1,BACK_TIME=now(),ADD_TIME=now()
where SEND_STATUS='0' and ACTIVITY_ID=9671;
mysql> select SEND_STATUS, ACTIVITY_ID,SEND_RESULT from testbobo
+-------------+-------------+-------------+
| SEND_STATUS | ACTIVITY_ID | SEND_RESULT |
+-------------+-------------+-------------+
| 4 | 9671 | -1 |
| 4 | 9671 | 0 |
| 4 | 9671 | -1 |
| 4 | 9671 | 0 |
| 4 | 9671 | 0 |
| 4 | 9671 | 0 |
| 4 | 9671 | 0 |
+-------------+-------------+-------------+
7 rows in set (0.00 sec)
看到,满足条件where SEND_STATUS='0' and ACTIVITY_ID=9671;的成功更新了(SEND_RESULT由5改成了-1)
其他 SEND_STATUS='4‘的没有更新(SEND_RESULT 还是0没有改成-1)。因为不满足条件