MySQL统计逗号分隔字段时如何去重计算总数量

问题原因 #

你当前使用的统计语句仅累加了每行room_id字段拆分后的元素总数，没有对全局重复的room_id做去重处理，所以示例数据中重复出现的4被计数了2次，最终得到错误结果5。 #

方案1：MySQL 8.0及以上版本（推荐） #

使用递归CTE拆分所有room_id为单行数据后去重计数：

WITH RECURSIVE split_rooms AS (
    SELECT 
        id,
        TRIM(room_id) AS room_str,
        LOCATE(',', room_id) AS comma_pos
    FROM booking
    UNION ALL
    SELECT 
        id,
        TRIM(SUBSTRING(room_str, comma_pos + 1)),
        LOCATE(',', SUBSTRING(room_str, comma_pos + 1))
    FROM split_rooms
    WHERE comma_pos > 0
)
SELECT COUNT(DISTINCT TRIM(SUBSTRING_INDEX(room_str, ',', 1))) AS total
FROM split_rooms;

如果是MySQL 8.0.19及以上版本，还可以用JSON_TABLE更简便地实现：

SELECT COUNT(DISTINCT TRIM(room_id_val)) AS total
FROM booking,
JSON_TABLE(
    CONCAT('["', REPLACE(room_id, ',', '","'), '"]'),
    '$[*]' COLUMNS (room_id_val VARCHAR(255) PATH '$')
) AS t;

方案2：MySQL 5.x低版本兼容方案 #

借助自定义数字序列实现拆分去重：

SELECT COUNT(DISTINCT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(b.room_id, ',', nums.n), ',', -1))) AS total
FROM booking b
JOIN (
    -- 数字序列的最大值需要大于单条room_id最多的元素个数，可按需扩展
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
) nums ON nums.n <= LENGTH(b.room_id) - LENGTH(REPLACE(b.room_id, ',', '')) + 1;

优化建议 #

逗号分隔存储关联ID的设计不符合数据库第一范式，后续数据量上升后查询、统计、关联操作的性能都会大幅下降，建议拆分为独立的booking_room关联表存储预订对应的房间ID，能大幅简化同类统计逻辑。

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