MySQL中如何解析点分隔字符串列并为新增列赋值
从分段格式字符串提取值填充MySQL新增列的实现方案
原测试语句的问题修正
- 列引用错误:原测试语句中通过
LOCATE('.', text)查找分隔符,实际分隔符存在于actsceneline列,与存储正文内容的text列无关 - 语法逻辑冲突:更新同一张表时,禁止直接在SET赋值位置嵌套针对当前表的子查询,否则会触发MySQL“不能在更新操作中引用目标表作为子查询来源”的报错。直接在赋值表达式中写计算规则即可,不需要嵌套查询。
全版本兼容实现
不依赖正则能力,仅通过原生字符串函数即可完成提取,兼容所有MySQL 5.x/8.x版本:
- 用
LOCATE定位第一个.的索引:LOCATE('.', actsceneline) - 用
LOCATE定位第二个.的索引:从第一个.的下一个字符开始,继续向后查找下一个.的位置 - 用
SUBSTRING按定位到的位置截取对应片段,分别存入两个新增列
对应的全量更新SQL:
UPDATE mytable SET -- 截取字符串开头到第一个`.`之前的内容,存入act列 act = SUBSTRING(actsceneline, 1, LOCATE('.', actsceneline) - 1), -- 截取第一个`.`和第二个`.`之间的内容,存入scene列 scene = SUBSTRING( actsceneline, LOCATE('.', actsceneline) + 1, LOCATE('.', actsceneline, LOCATE('.', actsceneline) + 1) - LOCATE('.', actsceneline) - 1 );
注意:由于新增的
act、scene列设置了NOT NULL约束,执行更新前需提前校验actsceneline列的数据格式,确保所有值都符合x.y.z的分段结构,不存在缺分隔符、空值的异常情况,避免因截取到空字符串触发非空约束报错。
MySQL 8.0+ 简化实现
如果使用MySQL 8.0及以上版本,可直接通过内置正则提取函数大幅简化逻辑:
UPDATE mytable SET act = REGEXP_SUBSTR(actsceneline, '^[^.]+'), scene = REGEXP_SUBSTR(actsceneline, '(?<=\\.)[^.]+(?=\\.)')
两个匹配规则的说明:
^[^.]+:匹配字符串开头到第一个.之前的所有非点字符(?<=\\.)[^.]+(?=\\.):匹配被前后两个.包裹的非点字符片段
内容的提问来源于stack exchange,提问作者user6631314
相关产品推荐
相关产品推荐

