如何将SQLite单表中分隔列数据转换为关联表结构？

前提说明 #

假设原CSV导入的单表名为RawContent，结构对应示例行如下：
id INTEGER PRIMARY KEY, title TEXT, categories TEXT, tags TEXT, content TEXT

1. 创建目标关系表结构 #

先搭建最终需要的5张核心表：

-- 内容主表（保留原核心数据，可按需调整字段）
CREATE TABLE Content (
    id INTEGER PRIMARY KEY,
    title TEXT,
    content TEXT
);

-- 分类表（存储唯一分类值）
CREATE TABLE Categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL
);

-- 标签表（存储唯一标签值）
CREATE TABLE Tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL
);

-- 内容-分类关联表
CREATE TABLE ContentCategories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    content_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    FOREIGN KEY (content_id) REFERENCES Content(id),
    FOREIGN KEY (category_id) REFERENCES Categories(id),
    UNIQUE(content_id, category_id) -- 避免重复关联
);

-- 内容-标签关联表
CREATE TABLE ContentTags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    content_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    FOREIGN KEY (content_id) REFERENCES Content(id),
    FOREIGN KEY (tag_id) REFERENCES Tags(id),
    UNIQUE(content_id, tag_id) -- 避免重复关联
);

2. 迁移主内容数据 #

把原表的核心内容迁移到Content表：

INSERT INTO Content (id, title, content)
SELECT id, title, content FROM RawContent;

3. 提取并插入唯一分类 #

利用SQLite 3.36+支持的递归CTE高效拆分分号分隔字段，自动去重插入分类表：

WITH RECURSIVE SplitCategories(content_id, cat, rest) AS (
    -- 初始行：拆分第一个分类
    SELECT id, 
           SUBSTR(categories, 1, INSTR(categories || ';', ';') - 1),
           SUBSTR(categories, INSTR(categories || ';', ';') + 1)
    FROM RawContent
    WHERE categories IS NOT NULL AND categories != ''
    UNION ALL
    -- 递归拆分剩余分类
    SELECT content_id,
           SUBSTR(rest, 1, INSTR(rest || ';', ';') - 1),
           SUBSTR(rest, INSTR(rest || ';', ';') + 1)
    FROM SplitCategories
    WHERE rest IS NOT NULL AND rest != ''
)
-- 依赖UNIQUE约束自动忽略重复值
INSERT OR IGNORE INTO Categories(name)
SELECT TRIM(cat) FROM SplitCategories;

4. 建立内容与分类的关联 #

再次用递归CTE拆分每个内容的分类，匹配分类ID后插入关联表：

WITH RECURSIVE SplitContentCategories(content_id, cat, rest) AS (
    SELECT id, 
           SUBSTR(categories, 1, INSTR(categories || ';', ';') - 1),
           SUBSTR(categories, INSTR(categories || ';', ';') + 1)
    FROM RawContent
    WHERE categories IS NOT NULL AND categories != ''
    UNION ALL
    SELECT content_id,
           SUBSTR(rest, 1, INSTR(rest || ';', ';') - 1),
           SUBSTR(rest, INSTR(rest || ';', ';') + 1)
    FROM SplitContentCategories
    WHERE rest IS NOT NULL AND rest != ''
)
INSERT OR IGNORE INTO ContentCategories(content_id, category_id)
SELECT s.content_id, c.id
FROM SplitContentCategories s
JOIN Categories c ON TRIM(s.cat) = c.name;

5. 提取唯一标签并建立关联 #

分类的处理逻辑完全适用于标签，替换对应字段即可：

插入唯一标签 #

WITH RECURSIVE SplitTags(content_id, tag, rest) AS (
    SELECT id, 
           SUBSTR(tags, 1, INSTR(tags || ';', ';') - 1),
           SUBSTR(tags, INSTR(tags || ';', ';') + 1)
    FROM RawContent
    WHERE tags IS NOT NULL AND tags != ''
    UNION ALL
    SELECT content_id,
           SUBSTR(rest, 1, INSTR(rest || ';', ';') - 1),
           SUBSTR(rest, INSTR(rest || ';', ';') + 1)
    FROM SplitTags
    WHERE rest IS NOT NULL AND rest != ''
)
INSERT OR IGNORE INTO Tags(name)
SELECT TRIM(tag) FROM SplitTags;

建立内容-标签关联 #

WITH RECURSIVE SplitContentTags(content_id, tag, rest) AS (
    SELECT id, 
           SUBSTR(tags, 1, INSTR(tags || ';', ';') - 1),
           SUBSTR(tags, INSTR(tags || ';', ';') + 1)
    FROM RawContent
    WHERE tags IS NOT NULL AND tags != ''
    UNION ALL
    SELECT content_id,
           SUBSTR(rest, 1, INSTR(rest || ';', ';') - 1),
           SUBSTR(rest, INSTR(rest || ';', ';') + 1)
    FROM SplitContentTags
    WHERE rest IS NOT NULL AND rest != ''
)
INSERT OR IGNORE INTO ContentTags(content_id, tag_id)
SELECT s.content_id, t.id
FROM SplitContentTags s
JOIN Tags t ON TRIM(s.tag) = t.name;

方案优势 #

• 全程使用SQLite原生语法，无需外部工具，高效处理大批量数据
• 递归CTE拆分字段比逐行循环处理速度提升显著，完美适配3.36+版本
• INSERT OR IGNORE结合UNIQUE约束自动去重，避免冗余数据
• 关联表的UNIQUE约束防止同一内容重复关联同一分类/标签

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