如何编写SQL查询拆分多值actions列生成独立数据行？

问题背景 #

现有日志表user_actions，数据结构与内容如下：

需要编写SQL查询，将actions列中用; 分隔的多值操作拆分为独立行，提取出操作类型（如added/removed）和对应物品，同时支持按用户、时间范围筛选。比如查询1月份用户dave的操作，期望结果如下：

分数据库解决方案 #

以下针对主流数据库给出具体实现：

1. PostgreSQL

利用string_to_table拆分字符串，结合split_part提取操作与物品：

SELECT
    ua.date,
    ua.user,
    split_part(action_item, ':', 1) AS action,
    split_part(action_item, ':', 2) AS item
FROM
    user_actions ua,
    string_to_table(ua.actions, '; ') AS action_item
WHERE
    ua.user = 'dave'
    AND ua.date BETWEEN '2023-01-01' AND '2023-01-31'
    AND action_item LIKE '%:%' -- 过滤无物品的单纯changed操作
ORDER BY
    ua.date;

2. MySQL 8.0+

用递归CTE逐行拆分操作项，再提取内容：

WITH RECURSIVE split_actions AS (
    SELECT
        date,
        user,
        actions,
        1 AS pos,
        SUBSTRING_INDEX(SUBSTRING_INDEX(actions, '; ', pos), '; ', -1) AS action_item
    FROM user_actions
    WHERE user = 'dave' AND date BETWEEN '2023-01-01' AND '2023-01-31'
    UNION ALL
    SELECT
        date,
        user,
        actions,
        pos + 1 AS pos,
        SUBSTRING_INDEX(SUBSTRING_INDEX(actions, '; ', pos + 1), '; ', -1) AS action_item
    FROM split_actions
    WHERE pos <= LENGTH(actions) - LENGTH(REPLACE(actions, '; ', ''))
)
SELECT
    date,
    user,
    SUBSTRING_INDEX(action_item, ':', 1) AS action,
    SUBSTRING_INDEX(action_item, ':', -1) AS item
FROM split_actions
WHERE action_item LIKE '%:%'
ORDER BY date;

3. SQL Server 2016+

通过STRING_SPLIT拆分，结合字符串函数提取操作与物品：

SELECT
    ua.date,
    ua.user,
    LEFT(action_item, CHARINDEX(':', action_item) - 1) AS action,
    RIGHT(action_item, LEN(action_item) - CHARINDEX(':', action_item)) AS item
FROM user_actions ua
CROSS APPLY STRING_SPLIT(ua.actions, '; ') AS split(action_item)
WHERE
    ua.user = 'dave'
    AND ua.date >= '2023-01-01' AND ua.date < '2023-02-01'
    AND action_item LIKE '%:%'
ORDER BY ua.date;

通用提示 #

• 如果actions列的分隔符不统一（比如有的是;不带空格），先通过REPLACE(actions, ';', '; ')标准化分隔符
• 若这类查询频率高，建议将非结构化的actions数据提前拆分存储为结构化关联表，避免频繁字符串操作影响性能

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