MySQL中批量更新数据的几种常用方法_数据库_开发者

开发者 https://www.devze.com 2025-10-18 08:57 出处：网络作者：学亮编程手记

目录1. 使用 CASE WHEN 语句（推荐）2. 使用多个 WHEN THEN 条件3. 使用 VALUES 和 JOIN 方式4. 使用临时表方式5. 使用 INSERT … ON DUPLICATE KEY UPDATE6. 批量更新相同值7. 使用存储过程进行复杂批量更新性

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();

性能优化建议

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

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;