MySQL中批量更新數(shù)據(jù)的幾種常用方法

更新時間：2025年10月17日 09:42:46 作者：學亮編程手記

本文總結(jié)了MySQL批量更新數(shù)據(jù)的多種方法及優(yōu)化建議,推薦使用CASE WHEN語句,強調(diào)索引、分批處理與事務(wù)控制,提醒注意備份和環(huán)境驗證,選擇方法需根據(jù)實際需求與性能要求,需要的朋友可以參考下

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 price
    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. 使用臨時表方式

-- 創(chuàng)建臨時表
CREATE TEMPORARY TABLE temp_updates (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 插入要更新的數(shù)據(jù)
INSERT INTO temp_updates VALUES 
(1, '張三', 'zhangsan@email.com'),
(2, '李四', 'lisi@email.com'),
(3, '王五', 'wangwu@email.com');

-- 執(zhí)行批量更新
UPDATE users u
JOIN temp_updates t ON u.id = t.id
SET 
    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_status;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 根據(jù)業(yè)務(wù)邏輯更新
        UPDATE users 
        SET status = 'processed', processed_at = NOW() 
        WHERE id = user_id;
        
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;

-- 調(diào)用存儲過程
CALL BatchUpdateUsers();

性能優(yōu)化建議

添加索引：在 WHERE 條件的字段上添加索引
分批處理：大量數(shù)據(jù)時建議分批更新
事務(wù)控制：使用事務(wù)確保數(shù)據(jù)一致性

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;