MySQL 存儲過程循環解析分號分隔用戶數據並批量入庫

MySQL 存儲過程循環解析分號分隔用戶數據並批量入庫

場景：前端傳入單一字符串參數 i_player_detail，包含多組用戶數據，組間用分號 ; 分隔，組內字段用逗號分隔（如 uid,score1,score2,seat_id）。需將每組數據解析後插入目標表 game_list，並記錄更新日期。

存儲過程完整實現

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 DEFAULT 0;
    DECLARE @str VARCHAR(500);

    -- 2. 循環按分號切分
    SET m_num = LOCATE(';', m_detail);
    WHILE m_num > 0 DO
        -- 構建動態 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 = LOCATE(';', m_detail);
    END WHILE;

    -- 3. 處理最後一組（無分號結尾）
    IF LENGTH(TRIM(m_detail)) > 0 THEN
        SET @str = CONCAT('INSERT INTO temp_list VALUES (', m_detail, ')');
        PREPARE stmt1 FROM @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`;
END$$
DELIMITER ;

關鍵點解析

• LOCATE() 替代 POSITION() 兼容性更好
• 動態 SQL 用 PREPARE/EXECUTE 避免注入風險
• 臨時表隔離中間數據，最後一次性 INSERT ... SELECT 落地
• 循環結束後補充處理最後一段無分號數據

相關實用片段

跨表複製數據

INSERT INTO t2 (id, old, up, new, qq, time)
SELECT id, gold, 1, gold+1, qq, SYSDATE() FROM t1 WHERE qq IN (qq1, qq2, qq3);

多表關聯查詢

SELECT * FROM `order`
INNER JOIN `userinfo` ON `userinfo`.`userId` = `order`.`uid`;