【工作遇到】update为什么没有更新

80 阅读 0 评论 53 点赞

我是靠谱客的博主刻苦石头，最近开发中收集的这篇文章主要介绍【工作遇到】update为什么没有更新，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

今天在执行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）。因为不满足条件