如何利用MySQL INSERT触发器将含分隔符的单条记录拆分为多条？

通过MySQL触发器实现该拆分需求 具备可行性，但需要注意原生触发器的语法限制，需要配合字符串拆分逻辑改造实现。

一、核心前置说明 #

• 你示例中的入表语句存在SQL语法错误：字符串值John需要用单引号包裹，正确格式为：

INSERT INTO person (id, name, number)
VALUES (3, 'John', '2|5|6');

• 需优先明确：MySQL触发器按触发时机分为BEFORE INSERT和AFTER INSERT，仅AFTER INSERT支持对目标表执行额外的INSERT/DELETE操作。

二、实现逻辑步骤 #

要实现拆分+删除原始带分隔符行的效果，需要先解决字符串拆分能力的问题，再编写触发器逻辑：

1. 准备拆分依赖（MySQL 5.7+/8.0通用） #

由于MySQL无内置的直接拆分分隔字符串的函数，需要先创建辅助数字表和拆分计数函数：

-- 1. 创建辅助数字表（支持最多拆分1000个分段，可按需调整上限）
CREATE TABLE IF NOT EXISTS nums (n INT PRIMARY KEY);
INSERT IGNORE INTO nums (n) 
WITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM seq WHERE n<1000) 
SELECT n FROM seq;

-- 2. 创建拆分计数函数，返回指定字符串按|分隔后的分段总数
DELIMITER //
CREATE FUNCTION split_str_count(str VARCHAR(1000), delim VARCHAR(1)) 
RETURNS INT DETERMINISTIC
BEGIN
  RETURN LENGTH(str) - LENGTH(REPLACE(str, delim, '')) + 1;
END //
DELIMITER ;

2. 编写AFTER INSERT触发器 #

触发器逻辑完全匹配需求：捕获带|的原始入全行，删除原始行后，按分段拆分为多条新记录，第一条沿用原入行的id，后续分段自动用自增id：

DELIMITER //
CREATE TRIGGER trg_person_after_insert_split
AFTER INSERT ON person
FOR EACH ROW
BEGIN
  -- 仅当number字段包含|分隔符时执行逻辑
  IF NEW.number LIKE '%|%' THEN
    DECLARE v_idx INT DEFAULT 1;
    DECLARE v_split_total INT DEFAULT split_str_count(NEW.number, '|');
    DECLARE v_current_val VARCHAR(50);

    -- 先删除原始的带分隔符的入行
    DELETE FROM person WHERE id = NEW.id;

    -- 遍历所有分段生成新记录
    WHILE v_idx <= v_split_total DO
      -- 获取第v_idx个分段的值
      SET v_current_val = SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.number, '|', v_idx), '|', -1);
      
      IF v_idx = 1 THEN
        -- 第一个分段沿用原入行的id
        INSERT INTO person (id, name, number) VALUES (NEW.id, NEW.name, v_current_val);
      ELSE
        -- 后续分段用自增主键生成新id
        INSERT INTO person (name, number) VALUES (NEW.name, v_current_val);
      END IF;

      SET v_idx = v_idx + 1;
    END WHILE;
  END IF;
END //
DELIMITER ;

三、实现后的效果验证 #

执行原始入表语句后，会自动生成3条符合要求的记录：

-- 原始入表语句（带|分隔）
INSERT INTO person (id, name, number) VALUES (3, 'John', '2|5|6');

-- 最终表中存储的结果
SELECT * FROM person;
-- 输出：
-- id | name | number
-- 3  | John | 2
-- 4  | John | 5
-- 5  | John | 6

四、限制与注意事项 #

• 仅支持单字段拆分：如果需要同时拆分多个带分隔符的字段，需要扩展触发器逻辑，复杂度会提升。
• 拆分长度限制：辅助数字表的上限决定了单条记录最多能拆分的分段数，超过上限会导致拆分逻辑失效。
• 批量插入兼容：触发器为FOR EACH ROW模式，批量插入时每行带分隔符的记录都会独立触发拆分逻辑，无兼容问题。
• 主键依赖：后续分段的新行默认使用自增主键生成id，如果person表的id不是自增属性，需要在触发器中补充自定义id生成逻辑，避免主键冲突。
• 事务一致性：如果触发器执行过程中出错（如id冲突、字符串非法），整个插入事务会自动回滚，不会产生脏数据。

内容的提问来源于stack exchange，提问作者Tiago Pereira