多进程swoole批量更新之case when坑

有个需求是通过货号；去csv批量导入去更新某个客户的库存表数据(真实库存等信息核心表)；因为涉及到表比较大；使用了多进程swoole来实现，再结合需求进行case when过滤操作；如果客户有多个门店某个字段不填写某人不修改之前的字段信息。

然后就出现了很神奇的一幕。按道理来说：为啥2317828172 stock会被改掉呢？全部stock都被改成了0；打印日志发现。执行的sql为

UPDATE `xcxmall_inventory` SET `stock` = CASE goods_no  WHEN '64837483' THEN 0 WHEN '434728392' THEN 0 WHEN '5674232' THEN 0 END,  `status` = CASE goods_no WHEN '64837483' THEN 0 WHEN '434728392' THEN 0 WHEN '2317828172' THEN 0 WHEN '5674232' THEN 0 END,  `price` = CASE goods_no WHEN '64837483' THEN 0.1 WHEN '434728392' THEN 0.2 WHEN '2317828172' THEN 0.3 WHEN '5674232' THEN 0.4 END  WHERE `goods_no` IN ('64837483','434728392','5674232','2317828172') AND store_id=6

思来想去问题出现在哪呢？我明明2317828172的stock没有WHEN XXTHEN的呀？后面得到了好朋友的指点，发现了操！如果不存在where后面的2317828172会被WHEN THEN 之后的ELSE也就是false替换入库也就是0入库 了！！改造sql:加else为原字段！

UPDATE `xcxmall_inventory` SET `stock` = CASE goods_no  WHEN '64837483' THEN 1 WHEN '434728392' THEN 1 WHEN '5674232' THEN 1 ELSE `stock` END,  `status` = CASE goods_no WHEN '64837483' THEN 0 WHEN '434728392' THEN 0 WHEN '2317828172' THEN 0 WHEN '5674232' THEN 0 ELSE `status` END,  `price` = CASE goods_no WHEN '64837483' THEN 0.1 WHEN '434728392' THEN 0.2 WHEN '2317828172' THEN 0.3 WHEN '5674232' THEN 0.4 ELSE `price` END  WHERE `goods_no` IN ('64837483','434728392','5674232','2317828172') AND store_id=6

顺便记录下when多个条件then

SELECT r.id,r.user_id ,u.nickname, CASE r.type 
WHEN 0 THEN '其他类型' 
WHEN 1 THEN '商城签到' 
WHEN 2 THEN '购物抵扣'
WHEN 3 THEN '平台调整'
WHEN 4 THEN '订单退还'
WHEN 5 THEN '购物赠送'
WHEN 6 THEN '积分兑换'
WHEN 10 THEN '积分兑换'
WHEN 7 THEN '抽奖消费'
WHEN 8 THEN '线下平台同步' //不要想着用in没效果的！
WHEN 20 THEN '线下平台同步' //
WHEN 11 THEN '积分兑换'
WHEN 12 THEN '订单退还'
WHEN 17 THEN '抽奖消费'
END,
r.integral, FROM_UNIXTIME(r.addtime,'%Y-%m-%d %H:%i') addtime FROM `xcxmall_register` r LEFT JOIN xcxmall_user u on r.user_id=u.id  WHERE (r.`store_id`=100) AND (r.`integral` <> 0)  AND r.user_id=803208;