如何在SQLite中避免第三张表实现账户与服务的多对多关系？

虽然第三张关联表是多对多关系的标准实现方式，能保证数据完整性和查询性能，但如果确实需要避免使用中间表，可通过以下两种方式实现：

方法1：使用JSON数组存储关联ID #

SQLite从3.31.0版本开始支持JSON函数，可以在account表中添加一个存储服务ID数组的字段（或反向在service表中存账户ID数组），这里以account表为例：

创建表语句 #

-- 创建account表
CREATE TABLE account (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    service_ids JSON NOT NULL DEFAULT '[]' -- 存储关联的service ID数组
);

-- 创建service表
CREATE TABLE service (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

插入关联数据 #

给ID为1的账户关联ID为2和3的服务：

UPDATE account 
SET service_ids = json_insert(service_ids, '$[#]', 2) 
WHERE id = 1;

UPDATE account 
SET service_ids = json_insert(service_ids, '$[#]', 3) 
WHERE id = 1;

查询单个账户的所有服务 #

用json_each展开数组后关联service表：

SELECT s.*
FROM account a
JOIN json_each(a.service_ids) j
JOIN service s ON j.value = s.id
WHERE a.id = 1;

优缺点 #

• 优点：结构简单，无需额外表，SQLite原生支持JSON操作
• 缺点：
  • 关联数据量大时查询性能显著下降
  • 无法自动校验service ID的合法性，数据完整性难保证
  • 修改关联关系需解析JSON数组，操作繁琐

方法2：使用分隔符存储关联ID #

在account表中用逗号等分隔符存储多个service ID，兼容性强但缺陷明显：

创建表语句 #

CREATE TABLE account (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    service_ids TEXT NOT NULL DEFAULT '' -- 逗号分隔的service ID字符串，如"2,3"
);

CREATE TABLE service (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

查询单个账户的所有服务 #

通过拼接分隔符避免误匹配：

SELECT s.*
FROM service s
JOIN account a ON ',' || a.service_ids || ',' LIKE '%,' || s.id || ',%'
WHERE a.id = 1;

优缺点 #

• 优点：无需依赖JSON支持，适用于低版本SQLite
• 缺点：
  • 极易出现数据错误（如ID含逗号、误匹配相似ID）
  • 查询性能极差，无法利用索引
  • 数据完整性完全无法保证，维护难度极大

注意：以上两种方式均为权宜之计。规范的多对多关系仍推荐使用第三张关联表，它能保证数据一致性、支持索引优化，且便于后续扩展复杂查询。

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