MySQL中批量更新数据的几种常用方法

目录

• 1. 使用 CASE WHEN 语句（推荐）
• 2. 使用多个 WHEN THEN 条件
• 3. 使用 VALUES 和 JOIN 方式
• 4. 使用临时表方式
• 5. 使用 INSERT … ON DUPLICATE KEY UPDATE
• 6. 批量更新相同值
• 7. 使用存储过程进行复杂批量更新
• 性能优化建议
• 注意事项

本文介绍mysql中批量更新数据的几种常用方法：

1. 使用 CASE WHEN 语句（推荐）

UPDATE users 
SET 
    status = CASE id 
        WHEN 1 THEN 'active'
        WHEN 2 THEN 'inactive'
        WHEN 3 THEN 'pending'
    END,
    updated_at = CASE id 
        WHEN 1 THEN '2024-01-01'
        WHEN 2 THEN '2024-01-02'
        WHEN 3 THEN '2024-01-03'
    END
WHERE id IN (1, 2, 3);

2. 使用多个 WHEN THEN 条件

UPDATE products 
SET 
    price = CASE 
        WHEN id = 1 THEN 19.99
        WHEN id = 2 THEN 29.99
        WHEN id = 3 THEN 39.99
        ELSE pric编程客栈e
    END,
    stock = CASE 
        WHEN id = 1 THEN 100
        WHEN id = 2 THEN 50
        WHEN id = 3 THEN 200
        ELSE stock
    END
WHERE id IN (1, 2, 3);

3. 使用 VALUES 和 JOIN 方式

UPDATE orders o
JOIN (
    SELECT 1 as id, 'shipped' as status, '2024-01-01' as ship_date
    UNION ALL
    SELECT 2, 'processing', '2024-01-02'
    UNION ALL
    SELECT 3, 'delivered', '2024-01-03'
) AS temp ON o.id = temp.id
SET 
    o.status = temp.status,
    o.ship_date = temp.ship_date,
    o.updated_at = NOW();

4. 使用临时表方式

-- 创建临时表
CREATE编程客栈 TEMPORARY TABLE temp_updates (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 插入要更新的数据
INSERT INTO temp_updates VALUES 
(1, '张三', 'zhangsan@email.com'),
(2, '李四', 'lisi@email.com'),
(3, '王五', 'wangwu@email.com');

-- 执行批量更新
UPDATE users u
JOIN temp_updates t ON u.id = t.id
SET 
php    u.name = t.name,
    u.email = t.email,
    u.updated_at = NOW();

-- 删除临时表
DROP TEMPORARY TABLE temp_updates;

5. 使用 INSERT … ON DUPLICATE KEY UPDATE

适用于主键或唯一索引冲突时的更新：

INSERT INTO users (id, name, email, status, updated_at) 
VALUES 
(1, '张三', 'zhangsan@email.com', 'active', NOW()),
(2, '李四', 'lisi@email.com', 'inactive', NOW()),
(3, '王五', 'wangwu@email.com', 'active', NOW())
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    email = VALUES(email),
    status = VALUES(status),
    updated_at = NOW();

6. 批量更新相同值

-- 更新所有符合条件的记录为相同值
UPDATE products 
SET 
    category = 'electronics',
    updated_at = NOW()
WHERE id IN (1, 2, 3, 4, 5);

-- 基于条件的批量更新
UPDATE employees 
SET 
    salary = salary * 1.1,  -- 涨薪10%
    last_raise_date = NOW()
WHERE department = 'Engineering' 
AND performance_rating >= 4;

7. 使用存储过程进行复杂批量更新

DELIMITER //

CREATE PROCEDURE BATchUpdateUsers()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_id INT;
    DECLARE user_status VARCHAR(20);
    DECLARE cur CURSOR FOR SELECT id, status FROM users WHERE status = 'pending';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO user_id, user_http://www.devze.comstatus;
     android   IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 根据业务逻辑更新
        UPDATE users 
        SET status = 'processed', processed_at = NOW() 
        WHERE id = user_id;
        
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;

-- 调用存储过程
CALL BatchUpdateUsers();

性能优化建议

1. 添加索引：在 WHERE 条件的字段上添加索引
2. 分批处理：大量数据时建议分批更新
3. 事务控制：使用事务确保数据一致性

START TRANSACTION;

UPDATE large_table 
SET status = 'updated'
WHERE id BETWEEN 1 AND 10000;

UPDATE large_table 
SET status = 'updated'
WHERE id BETWEEN 10001 AND 20000;

COMMIT;

注意事项

• 批量更新前建议先备份数据
• 在生产环境执行前先在测试环境验证
• 注意 WHERE 条件，避免误更新
• 大量数据更新时考虑在业务低峰期执行

选择哪种方法取决于具体需求、数据量和性能要求。CASE WHEN 方式通常是最常用且性能较好的选择。

到此这篇关于MySQL中批量更新数据的几种常用方法的文章就介绍到这了,更多相关MySQL批量更新数据内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！