應用場景

網站改版或資料遷移時，常需將多筆用戶資料以單一字串參數傳入，經解析後批次寫入資料表。本文示範一個儲存程序，接收分號分隔的多用戶資料，循環解析並插入目標表 game_list。

資料結構與參數說明

• 目標表： game_list（欄位：uid, score1, score2, seat_id, last_update）
• 輸入參數： i_player_detail VARCHAR(500)
• 資料格式： 用戶id,score1,score2,seat_id;用戶id,score1,score2,seat_id;...
• 處理邏輯： 以分號 ; 分隔每筆用戶資料，解析後寫入暫存表，最後批次寫入正式表並補上更新日期。

完整儲存程式碼

DELIMITER $$

USE `log_pdk`$$

DROP PROCEDURE IF EXISTS `game_c`$$

CREATE PROCEDURE `game_c` (IN i_player_detail VARCHAR(500))
SQL SECURITY INVOKER
BEGIN
    -- 1. 建立暫存表
    DROP TEMPORARY TABLE IF EXISTS `temp_list`;
    CREATE TEMPORARY TABLE `temp_list` (
        `uid` INT(10) UNSIGNED NOT NULL,
        `score1` INT(10) UNSIGNED NOT NULL,
        `score2` INT(10) UNSIGNED NOT NULL,
        `seat_id` TINYINT(3) UNSIGNED NOT NULL
    );

    -- 2. 宣告變數
    DECLARE m_detail VARCHAR(500);
    DECLARE m_num TINYINT;

    -- 3. 初始化
    SET m_detail = i_player_detail;
    SET m_num = POSITION(';' IN m_detail);

    -- 4. 循環解析每筆用戶資料
    WHILE m_num >= 1 DO
        BEGIN
            -- 動態建構 INSERT 語句
            SET @str = CONCAT('INSERT INTO temp_list VALUES (', SUBSTRING(m_detail, 1, m_num - 1), ')');
            PREPARE stmt1 FROM @str;
            EXECUTE stmt1;
            DEALLOCATE PREPARE stmt1;

            -- 移除已處理部分，繼續找下一個分號
            SET m_detail = SUBSTRING(m_detail, m_num + 1);
            SET m_num = POSITION(';' IN m_detail);
        END;
    END WHILE;

    -- 5. 批次寫入正式表，補上當日日期
    INSERT INTO `game_list` (`uid`, `score1`, `score2`, `seat_id`, `last_update`)
    SELECT `uid`, `score1`, `score2`, `seat_id`, CURRENT_DATE()
    FROM `temp_list`;
END$$

DELIMITER ;

關鍵點解析

1. 暫存表隔離： 使用 TEMPORARY TABLE 避免髒資料殘留，會話結束自動銷毀。
2. 動態 SQL： 透過 PREPARE / EXECUTE 實現字串拼接執行，支援靈活解析。
3. 循環控制： POSITION() 定位分號，SUBSTRING() 切割字串，直到無分號為止。
4. 批次寫入： 最後以 INSERT ... SELECT 一次性寫入，效率遠高於逐筆 INSERT。

擴充建議

• 加入例外處理（DECLARE CONTINUE HANDLER）捕捉格式錯誤。
• 若資料量極大，可考慮分批次提交或使用 LOAD DATA INFILE。
• 生產環境建議加入交易控制（START TRANSACTION / COMMIT）確保原子性。