如何使用SQL将SET类型属性规范化为MariaDB多对多关联交集表

解决方案 #

方法1：使用递归CTE拆分（适用于MariaDB 10.2.2及以上版本） #

直接执行以下查询即可得到你需要的拆分行结果：

WITH RECURSIVE split_values AS (
    -- 初始层：取出每条记录的第一个次大陆ID，保留剩余待拆分的字符串
    SELECT 
        Id AS RegionId,
        Super AS remaining,
        SUBSTRING_INDEX(Super, ',', 1) AS SubcontinentId
    FROM Regions
    WHERE Super IS NOT NULL AND Super <> ''

    UNION ALL

    -- 递归层：持续拆分剩余字符串，直到没有逗号为止
    SELECT 
        RegionId,
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2),
        SUBSTRING_INDEX(SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2), ',', 1)
    FROM split_values
    WHERE remaining LIKE '%,%'
)
-- 输出最终结果，转为数值类型避免字符串格式问题
SELECT RegionId, CAST(SubcontinentId AS UNSIGNED) AS SubcontinentId
FROM split_values
ORDER BY RegionId, SubcontinentId;

如果已经创建好交集表（假设表名为region_subcontinent，字段为RegionId INT、SubcontinentId INT），直接把查询结果插入即可：

INSERT INTO region_subcontinent (RegionId, SubcontinentId)
WITH RECURSIVE split_values AS (
    SELECT 
        Id AS RegionId,
        Super AS remaining,
        SUBSTRING_INDEX(Super, ',', 1) AS SubcontinentId
    FROM Regions
    WHERE Super IS NOT NULL AND Super <> ''
    UNION ALL
    SELECT 
        RegionId,
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2),
        SUBSTRING_INDEX(SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2), ',', 1)
    FROM split_values
    WHERE remaining LIKE '%,%'
)
SELECT DISTINCT RegionId, CAST(SubcontinentId AS UNSIGNED)
FROM split_values;

注意加DISTINCT是为了避免原始SET字段存在重复值导致插入重复关联

方法2：使用数字序列表拆分（适用于低版本不支持CTE的MariaDB） #

如果你的数据库版本不支持递归CTE，可以先手动创建一个存储连续数字的序列表nums，值覆盖你单条Super字段最多的关联数量即可，比如最多关联10个次大陆就插入1-10的数字：

-- 创建序列表
CREATE TABLE nums (n INT PRIMARY KEY);
INSERT INTO nums (n) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

然后执行以下查询拆分：

SELECT 
    r.Id AS RegionId,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(r.Super, ',', n.n), ',', -1) AS UNSIGNED) AS SubcontinentId
FROM Regions r
INNER JOIN nums n 
    ON n.n <= LENGTH(r.Super) - LENGTH(REPLACE(r.Super, ',', '')) + 1
WHERE r.Super IS NOT NULL AND r.Super <> ''
ORDER BY r.Id, SubcontinentId;

插入交集表的逻辑和方法1一致，套入INSERT SELECT语句即可。

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