如何按指定分隔符拆分字符串值并将结果插入数据表

需求说明 #

• 输入为包含多组值的拼接字符串，一级分隔符为|，二级分隔符为,，示例输入：'1,2,ABC|3,4,XYZ'
• 拆分后每组值对应一条记录插入目标表，示例输入最终插入2条记录：(1,2,'ABC')、(3,4,'XYZ')

实现方案 #

通用逻辑 #

• 第一步：用一级分隔符|拆分输入字符串，得到每条记录对应的字符串片段
• 第二步：遍历所有记录片段，用二级分隔符,拆分得到单个字段的值，按需做类型转换
• 第三步：批量执行插入操作，可选择在数据库SQL层完成拆分，也可在应用业务层处理后插入

数据库原生实现（MySQL 8.0+） #

依托递归CTE实现无需应用层处理的直接插入：

-- 目标表结构示例，可根据实际需求调整字段类型
CREATE TABLE IF NOT EXISTS target_table (
    col1 INT,
    col2 INT,
    col3 VARCHAR(100)
);

-- 拆分插入语句
INSERT INTO target_table (col1, col2, col3)
WITH RECURSIVE split_records AS (
    SELECT 
        1 AS idx,
        SUBSTRING_INDEX('1,2,ABC|3,4,XYZ', '|', 1) AS record_str,
        IF(LOCATE('|', '1,2,ABC|3,4,XYZ') > 0, SUBSTRING('1,2,ABC|3,4,XYZ', LOCATE('|', '1,2,ABC|3,4,XYZ') + 1), '') AS remaining_str
    UNION ALL
    SELECT 
        idx + 1,
        SUBSTRING_INDEX(remaining_str, '|', 1),
        IF(LOCATE('|', remaining_str) > 0, SUBSTRING(remaining_str, LOCATE('|', remaining_str) + 1), '')
    FROM split_records
    WHERE remaining_str != ''
)
SELECT 
    SUBSTRING_INDEX(record_str, ',', 1) + 0 AS col1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(record_str, ',', 2), ',', -1) + 0 AS col2,
    SUBSTRING_INDEX(record_str, ',', -1) AS col3
FROM split_records;

应用层实现（Python示例） #

应用层处理兼容性更高，不依赖数据库版本特性：

import pymysql

# 输入字符串
input_str = '1,2,ABC|3,4,XYZ'
# 两级拆分得到结构化的记录列表
record_list = [item.split(',') for item in input_str.split('|')]

# 批量插入数据库
db_conn = pymysql.connect(host="你的数据库地址", user="用户名", password="密码", database="库名")
cursor = db_conn.cursor()
cursor.executemany("INSERT INTO target_table (col1, col2, col3) VALUES (%s, %s, %s)", record_list)
db_conn.commit()
cursor.close()
db_conn.close()

内容的提问来源于stack exchange，提问作者sagar badiger