如何用现有数据填充新外键表？数据库结构重构实操

完全可以用SQL实现这个需求，以下是分步操作方案（以MySQL为例，其他数据库仅需调整自增ID语法）：

1. 创建新的date表 #

首先创建存储日期组合的表，包含自增主键和三个日期列：

CREATE TABLE `date` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date1 DATE,
    date2 DATE,
    date3 DATE
);

2. 向date表插入所有原表的日期行 #

使用UNION ALL保留所有重复的日期组合（因为原表每一行都需要对应date表的独立行）：

INSERT INTO `date` (date1, date2, date3)
-- 插入表A的所有日期行
SELECT date1, date2, date3 FROM A
UNION ALL
-- 插入表B的所有日期行
SELECT date1, date2, date3 FROM B
UNION ALL
-- 插入表C的所有日期行
SELECT date1, date2, date3 FROM C;

执行后date表的行顺序会是：先表A的所有行（按原表id排序），再表B，最后表C，与预期输出一致。

3. 给原表添加date_id外键列 #

分别给A、B、C表添加用于关联date表的整数列：

-- 给表A添加date_id列
ALTER TABLE A ADD COLUMN date_id INT;
-- 给表B添加date_id列
ALTER TABLE B ADD COLUMN date_id INT;
-- 给表C添加date_id列
ALTER TABLE C ADD COLUMN date_id INT;

4. 更新原表的date_id，关联到date表对应的行 #

因为date表的行是按A→B→C的顺序插入的，我们可以通过行号匹配来关联原表行和date表行：

更新表A的date_id #

UPDATE A
JOIN (
    -- 给表A的每一行标记行号
    SELECT id AS original_id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM A
) AS a_rows ON A.id = a_rows.original_id
JOIN (
    -- 给date表中属于A的行标记行号（前COUNT(A)行）
    SELECT id AS date_id, ROW_NUMBER() OVER (ORDER BY id) AS rn 
    FROM `date` 
    WHERE id <= (SELECT COUNT(*) FROM A)
) AS date_rows ON a_rows.rn = date_rows.rn
SET A.date_id = date_rows.date_id;

更新表B的date_id #

UPDATE B
JOIN (
    SELECT id AS original_id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM B
) AS b_rows ON B.id = b_rows.original_id
JOIN (
    SELECT id AS date_id, ROW_NUMBER() OVER (ORDER BY id) AS rn 
    FROM `date` 
    WHERE id > (SELECT COUNT(*) FROM A) 
      AND id <= (SELECT COUNT(*) FROM A) + (SELECT COUNT(*) FROM B)
) AS date_rows ON b_rows.rn = date_rows.rn
SET B.date_id = date_rows.date_id;

更新表C的date_id #

UPDATE C
JOIN (
    SELECT id AS original_id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM C
) AS c_rows ON C.id = c_rows.original_id
JOIN (
    SELECT id AS date_id, ROW_NUMBER() OVER (ORDER BY id) AS rn 
    FROM `date` 
    WHERE id > (SELECT COUNT(*) FROM A) + (SELECT COUNT(*) FROM B)
) AS date_rows ON c_rows.rn = date_rows.rn
SET C.date_id = date_rows.date_id;

5. 清理原表并建立外键约束 #

删除原表中的日期列，并添加外键约束确保关联完整性：

-- 清理表A的日期列并添加外键
ALTER TABLE A 
DROP COLUMN date1,
DROP COLUMN date2,
DROP COLUMN date3,
ADD CONSTRAINT fk_a_date FOREIGN KEY (date_id) REFERENCES `date`(id);

-- 清理表B的日期列并添加外键
ALTER TABLE B 
DROP COLUMN date1,
DROP COLUMN date2,
DROP COLUMN date3,
ADD CONSTRAINT fk_b_date FOREIGN KEY (date_id) REFERENCES `date`(id);

-- 清理表C的日期列并添加外键
ALTER TABLE C 
DROP COLUMN date1,
DROP COLUMN date2,
DROP COLUMN date3,
ADD CONSTRAINT fk_c_date FOREIGN KEY (date_id) REFERENCES `date`(id);

验证结果 #

执行完上述步骤后，各表结构和数据会与你给出的预期输出完全一致：

• date表包含所有原表的日期行（重复组合保留）
• A、B、C表通过date_id关联到date表的对应行，且已移除原日期列

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