当前位置：首页 > article >正文

MySQL 根据条件多值更新

article 2025/2/27 2:07:44

MySQL多条件批量更新核心语法：

UPDATE 表名
SET 
    字段1 = CASE 
        WHEN 条件1 THEN 值1
        WHEN 条件2 THEN 值2
        ELSE 字段1 
    END,
    字段2 = CASE 
        WHEN 条件1 THEN 值3
        WHEN 条件2 THEN 值4
        ELSE 字段2
    END
WHERE 整体过滤条件;

示例场景：更新商品库存和价格

 
UPDATE products
SET
    stock = CASE 
        WHEN category_id = 5 AND price > 100 THEN stock - 10
        WHEN create_date < '2024-01-01' THEN stock + 5
        ELSE stock
    END,
    price = CASE 
        WHEN category_id = 5 THEN price * 0.9
        WHEN stock > 1000 THEN price * 0.8
        ELSE price
    END
WHERE status = 'active';

高效写法技巧：

批量更新逻辑

UPDATE users
SET 
    vip_level = CASE
        WHEN score >= 1000 THEN 'Diamond'
        WHEN score >= 500 THEN 'Gold'
        ELSE vip_level
    END,
    discount = CASE 
        WHEN score >= 1000 THEN 0.7
        WHEN score >= 500 THEN 0.8
        ELSE discount
    END
WHERE registration_year = 2024;

关键注意事项：

条件优先级：WHEN子句按书写顺序匹配
必须包含ELSE：防止意外覆盖数据，建议保留原值
性能优化：WHERE子句需使用索引字段
事务处理：大批量更新建议分批次执行
多表关联：可用JOIN实现跨表条件更新

UPDATE orders o
JOIN payments p ON o.id = p.order_id
SET 
    o.status = CASE 
        WHEN p.paid = 1 THEN 'completed'
        ELSE 'pending'
    END,
    p.processed_at = NOW()
WHERE o.create_date > '2024-06-01';

扩展方案：使用IF函数简化二元判断

UPDATE employees
SET
    salary = IF(performance > 90, salary * 1.2, salary),
    bonus = IF(join_year < 2020, bonus + 5000, bonus)
WHERE department = 'Engineering';