mysql update if/case

发表于

MySQL update可以使用 IF / CASE 更新

不解释，直接看SQL

# 常规用法
UPDATE goods SET goods_num = 5 WHERE goods_id = 1
UPDATE goods SET goods_num = goods_num - 5 WHERE goods_id = 1

# 防止 goods_num 减成负数
UPDATE goods SET goods_num = IF(goods_num > 5, goods_num - 5, 0) WHERE goods_id = 1

# 防止 goods_num 减成负数 CASE 用法
UPDATE goods SET goods_num = CASE WHEN goods_num > 5 THEN  goods_num - 5 ELSE 0 END WHERE goods_id = 1