PostgreSQL拆分分号分隔字段：选游标循环还是Pivot？

嘿，这个问题我太懂了！先直接给你结论：你既不需要用游标循环，也不需要Pivot——这俩要么效率拉胯，要么完全用错了场景。

先掰扯清楚为啥这俩不合适：

• 游标循环：这玩意儿是逐行逐次处理数据，不仅代码写得啰嗦，性能还特别差，尤其是数据量稍微大一点的时候，绝对是下下策，字符串拆分这种简单操作根本犯不上用它。
• Pivot：这货是用来行转列的（把多行数据合并成一列的不同字段），而你现在的需求是列转行（把一个字段里的分隔值拆成多行），完全是反向操作，用Pivot纯属南辕北辙。

那正确的做法是什么？优先用数据库自带的字符串拆分函数，不同数据库的实现略有不同，给你举几个常见的例子：

1. SQL Server（2016及以上版本） #

用STRING_SPLIT函数直接拆分，搭配CROSS APPLY关联原表，还能去掉末尾空值：

SELECT s.value AS client_no
FROM your_table t
CROSS APPLY STRING_SPLIT(t.client_no_all, ';') s
WHERE s.value <> '';

2. MySQL #

可以用递归CTE结合SUBSTRING_INDEX来实现拆分：

WITH RECURSIVE split_cte AS (
    SELECT 
        SUBSTRING_INDEX(client_no_all, ';', 1) AS client_no,
        TRIM(LEADING ';' FROM SUBSTRING(client_no_all, LENGTH(SUBSTRING_INDEX(client_no_all, ';', 1)) + 2)) AS remaining
    FROM your_table
    WHERE client_no_all <> ''
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(remaining, ';', 1) AS client_no,
        TRIM(LEADING ';' FROM SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ';', 1)) + 2)) AS remaining
    FROM split_cte
    WHERE remaining <> ''
)
SELECT client_no
FROM split_cte
WHERE client_no <> '';

3. PostgreSQL #

用STRING_TO_ARRAY把字符串转成数组，再用UNNEST把数组拆成多行：

SELECT unnest(string_to_array(client_no_all, ';')) AS client_no
FROM your_table
WHERE unnest(string_to_array(client_no_all, ';')) <> '';

4. Oracle #

用正则表达式拆分，结合CONNECT BY生成行：

SELECT REGEXP_SUBSTR(client_no_all, '[^;]+', 1, LEVEL) AS client_no
FROM your_table
CONNECT BY REGEXP_SUBSTR(client_no_all, '[^;]+', 1, LEVEL) IS NOT NULL
AND PRIOR client_no_all = client_no_all
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

如果你的数据库版本比较老，没有内置的拆分函数，那也优先用递归CTE（就像上面MySQL的例子那样），比游标循环效率高得多，代码也更简洁。

总结一下：字符串拆分转多行的核心是把分隔符分割的字符串拆成独立行，游标和Pivot都不是这个场景的正确工具，优先用数据库原生拆分函数，没有的话用递归CTE就搞定了！

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