-- 低效的單條更新示例
UPDATE users SET status = 1 WHERE id = 1;
UPDATE users SET status = 1 WHERE id = 2;
UPDATE users SET status = 1 WHERE id = 3;
-- ...

UPDATE users
SET status = CASE 
    WHEN id = 1 THEN 2
    WHEN id = 2 THEN 3
    WHEN id = 3 THEN 1
    ELSE status -- 保持其他記錄不變
END
WHERE id IN (1, 2, 3);

-- 1. 創(chuàng)建臨時(shí)表并插入更新數(shù)據(jù)
CREATE TEMPORARY TABLE temp_updates (
    id INT PRIMARY KEY,
    new_status INT
);

INSERT INTO temp_updates VALUES 
(1, 2), (2, 3), (3, 1), (4, 2), (5, 3);

-- 2. 執(zhí)行批量更新
UPDATE users u
JOIN temp_updates t ON u.id = t.id
SET u.status = t.new_status;

-- 3. 刪除臨時(shí)表（可選，會(huì)話結(jié)束自動(dòng)刪除）
DROP TEMPORARY TABLE IF EXISTS temp_updates;

-- 1. 準(zhǔn)備CSV文件 updates.csv
-- 內(nèi)容示例：
-- id,new_status
-- 1,2
-- 2,3
-- 3,1

-- 2. 創(chuàng)建臨時(shí)表并導(dǎo)入數(shù)據(jù)
CREATE TEMPORARY TABLE temp_updates (
    id INT PRIMARY KEY,
    new_status INT
);

LOAD DATA INFILE '/path/to/updates.csv' 
INTO TABLE temp_updates 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 跳過標(biāo)題行

-- 3. 執(zhí)行批量更新（同臨時(shí)表方法）
UPDATE users u
JOIN temp_updates t ON u.id = t.id
SET u.status = t.new_status;

START TRANSACTION;
-- 批量更新語句
COMMIT;

DELIMITER //
CREATE PROCEDURE batch_update_users(IN ids TEXT, IN new_status INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE id_count INT;
    DECLARE current_id INT;
    DECLARE id_array TEXT DEFAULT ids;
    
    -- 計(jì)算ID數(shù)量（簡單實(shí)現(xiàn)，實(shí)際可用更高效方法）
    SET id_count = LENGTH(id_array) - LENGTH(REPLACE(id_array, ',', '')) + 1;
    
    WHILE i <= id_count DO
        -- 提取當(dāng)前ID（簡化示例，實(shí)際需更健壯的解析）
        SET current_id = SUBSTRING_INDEX(SUBSTRING_INDEX(id_array, ',', i), ',', -1);
        
        -- 執(zhí)行更新
        UPDATE users SET status = new_status WHERE id = current_id;
        
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 調(diào)用存儲(chǔ)過程
CALL batch_update_users('1,2,3,4,5', 2);