MySQL 存儲過程實現分號分隔字符串解析批量入庫

業務場景

目標表 game_list（字段：uid、score1、score2、seat_id、last_update）。輸入參數 i_player_detail 為分號分隔的多用戶數據串，格式如：

"用戶id,分數,桌號,座位號;用戶id,分數,桌號,座位號;..."

需將各用戶屬性解析後批量插入目標表，last_update 記錄導入日期。

存儲過程完整實現

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

    DECLARE m_detail VARCHAR(500) DEFAULT i_player_detail;
    DECLARE m_num TINYINT;
    DECLARE sql_str VARCHAR(500);

    -- 2. 循環解析分號分隔的用戶數據
    SET m_num = LOCATE(';', m_detail);
    
    WHILE m_num > 0 DO
        -- 構建動態插入語句：取第一個分號前的子串
        SET sql_str = CONCAT('INSERT INTO temp_list VALUES (', 
                             SUBSTRING(m_detail, 1, m_num - 1), ')');
        
        -- 執行預處理語句
        PREPARE stmt1 FROM sql_str;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
        
        -- 移除已處理部分，繼續下一輪
        SET m_detail = SUBSTRING(m_detail, m_num + 1);
        SET m_num = LOCATE(';', m_detail);
    END WHILE;

    -- 3. 處理最後一段（無結尾分號的情況）
    IF LENGTH(TRIM(m_detail)) > 0 THEN
        SET sql_str = CONCAT('INSERT INTO temp_list VALUES (', m_detail, ')');
        PREPARE stmt1 FROM sql_str;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;

    -- 4. 從臨時表批量寫入目標表，補齊更新時間
    INSERT INTO `game_list`(`uid`, `score1`, `score2`, `seat_id`, `last_update`)
    SELECT `uid`, `score1`, `score2`, `seat_id`, CURRENT_DATE()
    FROM `temp_list`;
    
    -- 5. 清理
    DROP TEMPORARY TABLE IF EXISTS `temp_list`;
END$$
DELIMITER ;

關鍵技術點解析

1. 字符串定位與截取

• LOCATE(';', str)：查找分號位置，不存在返回 0
• SUBSTRING(str, start, len)：提取子串
• 循環條件 m_num > 0 精確控制迭代次數

2. 動態 SQL 預處理

使用 PREPARE / EXECUTE / DEALLOCATE PREPARE 三步驟，防止 SQL 注入並提升重複執行效率。

3. 臨時表中轉

先寫入臨時表再批量 INSERT ... SELECT，比單條插入性能高數量級，且便於統一補充 last_update 字段。

4. 邊界處理

循環結束後補充處理最後一段數據（無結尾分號），確保無遺漏。

調用示例

CALL game_c('1001,10,20,1;1002,15,25,2;1003,8,18,3');