SQL数据库中如何在单行列存储外键集合？

首先明确：不建议直接在单列中存储外键列表，这种设计违反关系型数据库的第一范式（1NF），会带来一系列问题：

• 查询关联数据时需要拆分数组，无法利用索引，数据量较大时性能极差；
• 维护成本高，添加/删除单个外键时需修改整个列表，容易出现格式错误、重复ID等问题；
• 无法借助数据库外键约束保证数据合法性，可能存入不存在的Source ID，导致数据不一致。

推荐的关系型数据库设计方案 #

针对Room与Source的一对多关系，标准做法是通过关联表（或在子表添加外键）实现：

表结构设计 #

1. Rooms表（主表）：

| ID | Name       | More Props.. |
|----|------------|--------------|
| 1  | Some Room  | ...          |

2. Sources表（子表）：

| ID       | Name       | More Props.. |
|----------|------------|--------------|
| SourceID1| Source1    | ...          |
| SourceID7| Source7    | ...          |
| SourceID3| Source3    | ...          |

3. Room_Sources关联表（用于关联Room和Source，若Source仅属于一个Room，也可直接在Sources表添加RoomID字段）：

| RoomID | SourceID   |
|--------|------------|
| 1      | SourceID1  |
| 1      | SourceID7  |
| 1      | SourceID3  |

查询示例 #

要获取包含Sources列表的Room数据，可通过JOIN+聚合查询实现：

-- 获取逗号分隔的Source ID列表
SELECT r.ID, r.Name, GROUP_CONCAT(s.ID) AS SourceIds
FROM Rooms r
LEFT JOIN Room_Sources rs ON r.ID = rs.RoomID
LEFT JOIN Sources s ON rs.SourceID = s.ID
WHERE r.ID = 1
GROUP BY r.ID, r.Name;

-- 生成你需要的JSON格式结果（以MySQL 8.0+为例）
SELECT JSON_OBJECT(
    'Rooms', JSON_ARRAYAGG(
        JSON_OBJECT(
            'Name', r.Name,
            'Sources', JSON_ARRAYAGG(s.ID)
        )
    )
) AS result
FROM Rooms r
LEFT JOIN Room_Sources rs ON r.ID = rs.RoomID
LEFT JOIN Sources s ON rs.SourceID = s.ID
GROUP BY r.ID;

若必须在单列存储外键列表（不推荐） #

如果受限于业务场景或历史架构必须这么做，需注意以下几点：

1. 数据类型选择：优先使用数据库原生的JSON类型（如MySQL JSON、PostgreSQL JSONB），直接存储数组格式；若不支持JSON类型，可使用VARCHAR/TEXT存储逗号分隔的字符串（如"SourceID1,SourceID7,SourceID3"）。
2. 数据合法性校验：在应用层或数据库触发器中添加校验逻辑，确保每个存入的Source ID都存在于Sources表。
3. 查询优化：使用JSON类型时，可借助数据库的JSON函数查询，比如MySQL的JSON_CONTAINS：

SELECT * FROM Rooms WHERE JSON_CONTAINS(SourceIds, '"SourceID1"');

但这种方式的性能仍远不及关联表方案，仅适合数据量较小的场景。

对应你的表结构调整 #

若采用JSON类型存储，Rooms表结构可调整为：

| ID | Name       | More Props.. | SourceIds                          |
|----|------------|--------------|------------------------------------|
| 1  | Some Room  | ...          | ["SourceID1", "SourceID7", "SourceID3"] |

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