MySQL存储过程：通过分号分隔循环截取数据并批量插入

小石头

发布时间：2026-10-14| 1人浏览过

原创

在处理包含多个用户数据的字符串参数时，MySQL提供了一种基于分号分隔符的处理方案。本文将详细讲解如何编写一个存储过程，循环截取传入的多组用户信息（如uid、score、seat_id），将其暂存至临时表，并最终批量更新到游戏列表表中。

问题分析与解决思路

在实际开发中，常遇到需要将一批结构化的数据封装成单个字符串参数传给后端接口的情况。例如，将多行玩家数据拼接为“uid,score1,score2,seat_id;uid,score1,score2,seat_id...”格式传递给数据库。

为了高效处理此类非结构化输入，我们采用以下策略：

动态SQL预处理：利用MySQL的Prepare语句动态构建INSERT命令。
循环截取：使用位置定位函数找到分隔符，逐段提取数据。
中间表中转：将截取的碎片数据先存入临时表，减少频繁的磁盘IO开销。
批量入库：从临时表SELECT数据后统一写入目标表。

环境准备

源/目标表： game_list，需包含 uid, score1, score2, seat_id 及自动更新的 last_update 字段。
入参格式： 单个字符串，每组用户数据内部用逗号隔开，组间用分号隔开。示例："1001,500,450,1;1002,600,580,2;"
库名假设： log_pdk（代码中已预设，可按需修改）。

具体操作步骤

第1步：定义存储过程基础结构

首先创建存储过程入口，设置字符集为单引号或反引号的转义安全模式。这里使用 DELIMITER 改变结束符，避免过程体内的分号导致程序误判执行结束。

-- 声明分隔符变化
delimiter $$
use `log_pdk`;$$
-- 删除旧过程以防重复创建
DROP PROCEDURE IF EXISTS `process_player_data`$$
-- 定义新过程
CREATE PROCEDURE `process_player_data` (
    IN i_player_detail VARCHAR(500)
) 
SQL SECURITY INVOKER
BEGIN
    -- 逻辑体将在下面展开
end$$
delimiter ;

第2步：建立临时表用于数据缓冲

创建一个内存型临时表 temp_list，其字段结构与最终目标一致，仅保留业务核心字段（不含时间戳）。每次调用前清空该表。

-- 清理可能残留的历史记录
DROP 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
);

第3步：构建循环解析逻辑

这是核心部分。我们需要定义变量来保存剩余待处理的字符串，并查找分号的位置。注意原文代码存在多处变量命名错误（如未定义的str与m_detail混用），此处进行修正。

-- 变量声明
DECLARE m_remainder VARCHAR(500);
DECLARE m_pos INT DEFAULT 0;

-- 初始化：将传入的长参数复制到工作变量中
SET m_remainder = i_player_detail;

-- 循环条件：只要字符串中还包含分号，就继续截取
-- 注意：position()在未找到字符时返回0，找到返回起始位置
WHILE POSITION(';' IN m_remainder) > 0 DO
    BEGIN
        SET m_pos = POSITION(';' IN m_remainder);
        
        -- 构造动态SQL：截取第一个分号前的内容作为一组值
        -- 假设输入格式严格符合：'id,val1,val2,seat'
        SET @sql_cmd = CONCAT(
            'INSERT INTO temp_list VALUES (',
            SUBSTRING(m_remainder, 1, m_pos - 1),
            ')'
        );
        
        -- 预编译并执行动态SQL
        PREPARE stmt FROM @sql_cmd;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- 更新工作变量：去掉已处理的部分（包括那个分号）
        SET m_remainder = SUBSTRING(m_remainder, m_pos + 1);
    END;
END WHILE;

注意风险： 上述循环逻辑在处理完所有带分号的数据后停止。如果最后一组数据末尾没有分号，它将留在 m_remainder 中未被处理。实际工程中通常建议强制在结尾加一个分号，或者增加一次对 m_remainder 的非空判断处理。

第4步：批量导入至目标表

当所有片段都通过循环进入 temp_list 后，只需一条简单的 INSERT SELECT 即可将数据落盘到业务主表，同时附带当前日期作为更新时间。

INSERT INTO `game_list` (`uid`, `score1`, `score2`, `seat_id`, `last_update`)
SELECT `uid`, `score1`, `score2`, `seat_id`, CURRENT_DATE()
FROM `temp_list`;

-- 可选：如果是事务处理，在此处 COMMIT；否则依赖autocommit
END$$
delimiter ;