Oracle中不使用REGEXP实现行转列（REGEXP耗时过长）

MySQL 场景 #

8.0及以上版本（推荐） #

用JSON_TABLE把字符串转成JSON数组后展开，原生函数性能远胜正则：

SELECT value
FROM JSON_TABLE(
  CONCAT('["', REPLACE('111_122,111_142,111_222,111_322,142_342,212_232,222_242,312_332,322_342", ', '", "'), '"]'),
  '$[*]' COLUMNS (value VARCHAR(20) PATH '$')
) AS jt;

低版本MySQL #

如果值的数量固定，直接用UNION ALL配合SUBSTRING_INDEX拆分，比正则快：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('111_122,111_142,111_222,111_322,142_342,212_232,222_242,312_332,322_342', ',', 1), ',', -1) AS value
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('111_122,111_142,111_222,111_322,142_342,212_232,222_242,312_332,322_342', ',', 2), ',', -1)
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('111_122,111_142,111_222,111_322,142_342,212_232,222_242,312_332,322_342', ',', 3), ',', -1)
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('111_122,111_142,111_222,111_322,142_342,212_232,222_242,312_332,322_342', ',', 4), ',', -1)
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('111_122,111_142,111_222,111_322,142_342,212_232,222_242,312_332,322_342', ',', 5), ',', -1)
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('111_122,111_142,111_222,111_322,142_342,212_232,222_242,312_332,322_342', ',', 6), ',', -1)
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('111_122,111_142,111_222,111_322,142_342,212_232,222_242,312_332,322_342', ',', 7), ',', -1)
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('111_122,111_142,111_222,111_322,142_342,212_232,222_242,312_332,322_342', ',', 8), ',', -1)
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('111_122,111_142,111_222,111_322,142_342,212_232,222_242,312_332,322_342', ',', 9), ',', -1);

PostgreSQL 场景 #

用原生的STRING_TO_ARRAY+UNNEST组合，这是PostgreSQL处理字符串拆分的最优方式：

SELECT unnest(string_to_array('111_122,111_142,111_222,111_322,142_342,212_232,222_242,312_332,322_342', ',')) AS value;

SQL Server 场景 #

2016及以上版本直接用STRING_SPLIT，微软专门优化的拆分函数，性能碾压正则：

SELECT value
FROM STRING_SPLIT('111_122,111_142,111_222,111_322,142_342,212_232,222_242,312_332,322_342', ',');

通用优化建议 #

• 优先用数据库原生拆分函数，这类函数都是底层实现，比正则表达式的字符匹配效率高几个量级
• 如果需要频繁处理这类数据，建议在数据入库时就拆分成多行存储，彻底避免查询阶段的拆分开销

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