MySQL为相似记录分配组ID：请求编写SQL查询语句

嘿，这个问题本质是要找出数据里的连通分量——也就是通过相似关系传递连接起来的所有记录组。在MySQL 8.0及以上版本，咱们用递归CTE（公共表表达式）就能轻松搞定，下面给你两种实用的实现方式：

方式一：每条记录对应一行带组标识 #

这种方式会为每条记录分配一个唯一的组标识（这里用组内最小的记录ID作为标识，直观且满足非整数要求）：

首先，先确认你的表结构和测试数据（如果还没建表可以用这段）：

CREATE TABLE similar_records (
    record VARCHAR(10),
    similar_record VARCHAR(10)
);

INSERT INTO similar_records VALUES
('rec_1', 'rec_2'),
('rec_3', 'rec_4'),
('rec_2', 'rec_3'),
('rec_5', 'rec_7');

然后是核心查询语句：

WITH RECURSIVE record_groups AS (
    -- 初始步骤：把所有记录和相似记录都列出来，用两者中的最小值作为初始组ID
    SELECT 
        record AS node,
        LEAST(record, similar_record) AS group_id
    FROM similar_records
    UNION
    SELECT 
        similar_record AS node,
        LEAST(record, similar_record) AS group_id
    FROM similar_records
    UNION
    -- 递归步骤：遍历所有连通的节点，合并组ID为更小的那个，确保同一组的ID统一
    SELECT 
        rg2.node,
        LEAST(rg1.group_id, rg2.group_id) AS group_id
    FROM record_groups rg1
    JOIN similar_records sr ON rg1.node = sr.record
    JOIN record_groups rg2 ON sr.similar_record = rg2.node
    WHERE rg1.group_id != rg2.group_id
)
-- 去重并最终为每个记录确定唯一的组ID
SELECT DISTINCT
    node AS record,
    MIN(group_id) OVER (PARTITION BY node) AS group_id
FROM record_groups
ORDER BY group_id, record;

执行后输出结果：

方式二：按组聚合输出（用GROUP_CONCAT） #

如果需要直接输出每个组的所有成员，用GROUP_CONCAT聚合即可：

WITH RECURSIVE record_groups AS (
    SELECT 
        record AS node,
        LEAST(record, similar_record) AS group_id
    FROM similar_records
    UNION
    SELECT 
        similar_record AS node,
        LEAST(record, similar_record) AS group_id
    FROM similar_records
    UNION
    SELECT 
        rg2.node,
        LEAST(rg1.group_id, rg2.group_id) AS group_id
    FROM record_groups rg1
    JOIN similar_records sr ON rg1.node = sr.record
    JOIN record_groups rg2 ON sr.similar_record = rg2.node
    WHERE rg1.group_id != rg2.group_id
)
SELECT 
    MIN(group_id) AS group_id,
    GROUP_CONCAT(DISTINCT node ORDER BY node SEPARATOR ', ') AS group_members
FROM record_groups
GROUP BY MIN(group_id)
ORDER BY group_id;

执行后输出结果：

补充说明 #

• 这个方案依赖MySQL 8.0及以上版本的递归CTE支持，如果你的版本较低，可能需要用临时表+循环的方式实现，但现在大部分生产环境都已经升级到8.0+了。
• 组标识用组内最小的记录值，不需要整数，完全满足你的要求，同时也方便理解每个组的起始节点。

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