如何在MariaDB 10.3中向pivot table插入所有关联标签的父级ID

现有三张数据表结构及数据如下：

公告表（Announcements） #

Announcements:
id | title | body
-----------------------
1  | test1 | test body1
2  | test2 | test body2
3  | test3 | test body3

标签表（Tags） #

标签为层级结构，parent_id 指向父级标签ID：

Tags:
id | title | parent_id
-------------------------
1  | tag1  | (NULL)
2  | tag2  | 1
3  | tag3  | 2

公告标签关联中间表（Announcement_tag） #

存储公告和标签的多对多关联：

Announcement_tag:
announcement_id | tag_id
1               | 1
2               | 2
3               | 3

• 新系统已经实现自动插入公告关联标签的所有父级标签的逻辑，但历史数据中部分公告缺失父级标签关联
• 不允许删除已有的关联数据，仅需要补充缺失的父级标签关联
• 最终期望的中间表数据如下：

Announcement_tag:
announcement_id | tag_id
1               | 1
2               | 1
2               | 2
3               | 1
3               | 2
3               | 3

你可以直接用递归CTE的SQL语句完成批量补全，不会修改已有数据，仅插入缺失的关联：

WITH RECURSIVE tag_hierarchy AS (
    -- 基础节点：获取每个标签本身作为起点
    SELECT id AS origin_tag_id, id AS related_tag_id FROM Tags
    UNION ALL
    -- 递归向上遍历所有父级标签
    SELECT th.origin_tag_id, t.parent_id AS related_tag_id
    FROM tag_hierarchy th
    JOIN Tags t ON th.related_tag_id = t.id
    WHERE t.parent_id IS NOT NULL
)
-- 插入不存在的关联关系
INSERT IGNORE INTO Announcement_tag (announcement_id, tag_id)
SELECT DISTINCT at.announcement_id, th.related_tag_id
FROM Announcement_tag at
JOIN tag_hierarchy th ON at.tag_id = th.origin_tag_id
WHERE NOT EXISTS (
    SELECT 1 FROM Announcement_tag exist_at
    WHERE exist_at.announcement_id = at.announcement_id
    AND exist_at.tag_id = th.related_tag_id
);

方案说明 #

• 递归CTE会生成每个标签对应的所有自身+父级标签的映射关系，比如tag3会映射到3、2、1三个标签ID
• INSERT IGNORE和WHERE NOT EXISTS双重校验避免重复插入已有数据，完全符合你不能删除原有数据的要求
• 支持MySQL 8.0+、PostgreSQL、SQL Server等所有支持递归CTE的主流数据库，执行一次即可完成所有历史数据的父级关联补全

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