PostgreSQL如何用regexp_split_to_table拆分字段生成原子行

假设你的表名为room_facilities，包含字段room_id（房间唯一ID）、facility_list（逗号拼接的设施字段），可直接使用以下语句：

SELECT
  room_id,
  TRIM(facility) AS facility_name
FROM
  room_facilities,
  regexp_split_to_table(facility_list, ',') AS t(facility)
WHERE
  facility <> '';

• 语句中TRIM()用于处理设施前后的多余空格，适配wifi, tv, aircon这类带空格的拼接格式
• WHERE条件用于过滤空字符串，避免字段为空、首尾带逗号时产生无效空行
• 逗号连接等价于CROSS JOIN LATERAL，为简化写法的通用格式

MySQL无内置regexp_split_to_table函数，8.0及以上版本可使用递归CTE实现拆分：

WITH RECURSIVE split_facilities AS (
  SELECT
    room_id,
    TRIM(SUBSTRING_INDEX(facility_list, ',', 1)) AS facility_name,
    SUBSTRING(facility_list, INSTR(facility_list, ',') + 1) AS remaining_facilities
  FROM room_facilities
  WHERE facility_list IS NOT NULL AND facility_list <> ''
  UNION ALL
  SELECT
    room_id,
    TRIM(SUBSTRING_INDEX(remaining_facilities, ',', 1)) AS facility_name,
    SUBSTRING(remaining_facilities, INSTR(remaining_facilities, ',') + 1) AS remaining_facilities
  FROM split_facilities
  WHERE remaining_facilities <> ''
)
SELECT DISTINCT room_id, facility_name
FROM split_facilities
WHERE facility_name <> ''
ORDER BY room_id;

如果是MySQL 5.x版本，可借助临时表+数字辅助表实现，核心逻辑和递归CTE一致，按拆分次数循环截取即可。

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