如何在MySQL中从一张表生成两张互相关联的新表？

针对从现有plan表创建关联的plana和planb表的需求，以下是分步实现方案：

1. 先创建目标表结构 #

首先按照需求创建plana和planb表，其中plana存储计划基础信息，planb存储计划对应的功能项，通过plana_id建立一对多关联：

-- 创建plana表
CREATE TABLE `plana` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_id INT NOT NULL,
    `name` VARCHAR (25) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建planb表
CREATE TABLE `planb` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plana_id INT NOT NULL,
    feature_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 同步基础数据到plana表 #

从原plan表提取计划的plan_id和name字段，插入到plana中：

INSERT INTO plana (plan_id, `name`)
SELECT plan_id, `name` FROM `plan`;

注：plana的created_at字段会自动使用默认的当前时间戳，无需手动指定。

3. 拆分功能字段并插入planb表 #

原表的features是逗号分隔的字符串，需要拆分为单个功能项后，关联plana的主键插入到planb，这里提供两种适配不同MySQL版本的方法：

方法一：递归CTE（MySQL 8.0及以上版本适用） #

利用递归CTE逐步拆分逗号分隔的字符串，再关联plana插入数据：

WITH RECURSIVE split_features AS (
    SELECT 
        p.id AS original_plan_id,
        p.name AS plan_name,
        SUBSTRING_INDEX(p.features, ',', 1) AS feature_id,
        SUBSTRING(p.features, LOCATE(',', p.features) + 1) AS remaining_features
    FROM `plan` p
    WHERE p.features IS NOT NULL AND p.features != ''
    
    UNION ALL
    
    SELECT 
        original_plan_id,
        plan_name,
        SUBSTRING_INDEX(remaining_features, ',', 1) AS feature_id,
        SUBSTRING(remaining_features, LOCATE(',', remaining_features) + 1) AS remaining_features
    FROM split_features
    WHERE remaining_features IS NOT NULL AND remaining_features != ''
)
INSERT INTO planb (plana_id, feature_id)
SELECT 
    pl.id AS plana_id,
    sf.feature_id
FROM split_features sf
JOIN plana pl ON sf.plan_name = pl.`name` AND sf.original_plan_id = pl.plan_id;

方法二：数字辅助表（兼容MySQL 5.x版本） #

如果你的MySQL版本不支持递归CTE，可以先创建一个数字辅助表，再拆分插入：

-- 创建数字辅助表（这里创建1-10的数字，可根据features的最大条目数调整）
CREATE TABLE numbers (n INT PRIMARY KEY);
INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

-- 拆分并插入数据
INSERT INTO planb (plana_id, feature_id)
SELECT 
    pl.id AS plana_id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(p.features, ',', n.n), ',', -1) AS feature_id
FROM `plan` p
JOIN numbers n ON n.n <= LENGTH(p.features) - LENGTH(REPLACE(p.features, ',', '')) + 1
JOIN plana pl ON p.`name` = pl.`name` AND p.plan_id = pl.plan_id
WHERE p.features IS NOT NULL AND p.features != '';

4. 添加外键约束（可选但推荐） #

为了保证两张表的数据一致性，建议给planb的plana_id添加外键约束，关联plana的主键：

ALTER TABLE planb
ADD CONSTRAINT fk_planb_plana FOREIGN KEY (plana_id) REFERENCES plana(id)
ON DELETE CASCADE ON UPDATE CASCADE;

注：ON DELETE CASCADE表示删除plana中的记录时，planb中对应的关联记录会自动删除；ON UPDATE CASCADE表示更新plana的主键时，planb的plana_id会同步更新。

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