MySQL中使用正则拆分字段后存入另一张表多个对应列的实现

直接使用INSERT SELECT语句同步全量数据，同时写入拆分后的姓名字段：

INSERT INTO Table2 (FirstName, LastName, 其他Table1与Table2共有的字段名)
SELECT 
  REGEXP_SUBSTR(Name, '[^_]+', 1, 1) AS FirstName, -- 取下划线分隔的第一段作为名
  REGEXP_SUBSTR(Name, '[^_]+', 1, 2) AS LastName, -- 取下划线分隔的第二段作为姓
  Table1中其他需要同步的字段名
FROM Table1;

通过关联两张表的唯一主键更新对应字段，不同数据库语法略有差异：

MySQL 语法 #

UPDATE Table2 t2
INNER JOIN Table1 t1 ON t2.唯一关联主键 = t1.唯一关联主键 -- 此处替换为两张表匹配同一行数据的唯一字段，如用户ID、自增ID
SET 
  t2.FirstName = REGEXP_SUBSTR(t1.Name, '[^_]+', 1, 1),
  t2.LastName = REGEXP_SUBSTR(t1.Name, '[^_]+', 1, 2);

Oracle 语法 #

UPDATE Table2 t2
SET (FirstName, LastName) = (
  SELECT 
    REGEXP_SUBSTR(t1.Name, '[^_]+', 1, 1),
    REGEXP_SUBSTR(t1.Name, '[^_]+', 1, 2)
  FROM Table1 t1
  WHERE t1.唯一关联主键 = t2.唯一关联主键
)
WHERE EXISTS (
  SELECT 1 FROM Table1 t1 WHERE t1.唯一关联主键 = t2.唯一关联主键
);

• 执行写操作前先单独执行SELECT查询验证你的拆分逻辑是否符合预期，避免数据写入错误
• 支持事务的数据库建议先开启事务，验证执行结果无误后再提交，出错可直接回滚
• 若Name字段存在多个下划线的场景（如First_Middle_Last），需调整正则匹配规则匹配你的业务要求，比如要取第一个下划线之后所有内容作为LastName，可将对应拆分逻辑替换为REGEXP_SUBSTR(Name, '_(.*)', 1, 1, NULL, 1)

内容的提问来源于stack exchange，提问作者Richard Zhang