MySQL单张大表拆分迁移至三张关联小表的技术实现咨询

嗨，我来帮你解决这个表拆分迁移的问题～你遇到的核心问题是批量插入时82983没法对应每一行的关联ID，因为这个函数只会返回最后一次插入操作生成的第一个自增ID，批量插入场景下根本没法匹配原表每一行对应的关联关系。下面给你两种可行的解决方案，根据你的数据情况选择即可：

方案一：利用原表唯一ID直接关联（最简单，适合原表ID连续唯一的场景） #

你的原表alldata的ID_all是连续且唯一的主键，完全可以直接用它来对应新表的关联ID，这样不需要依赖自增ID的返回值，操作起来特别省心：

1. 先修正新表的结构（补充主键和自增设置） #

首先要给三张新表设置主键和自增规则，确保数据完整性：

-- 修正workspace表
ALTER TABLE `workspace`
ADD PRIMARY KEY (`workspaceID`),
MODIFY `workspaceID` int(10) NOT NULL AUTO_INCREMENT;

-- 修正devices表
ALTER TABLE `devices`
ADD PRIMARY KEY (`deviceID`),
MODIFY `deviceID` int(10) NOT NULL AUTO_INCREMENT;

-- 修正datasize表
ALTER TABLE `datasize`
ADD PRIMARY KEY (`datasizeID`),
MODIFY `datasizeID` int(10) NOT NULL AUTO_INCREMENT;

2. 分步插入数据 #

• 插入workspace表：直接用原表的ID_all作为workspaceID（和原表行一一对应）：

INSERT INTO workspace (workspaceID, date, number)
SELECT ID_all, date, number FROM alldata;

如果不想用原表ID作为新表ID，也可以不指定workspaceID，让MySQL自动生成，后续步骤用关联查询匹配即可，不过直接对应更直观。

• 插入devices表：关联原表的ID_all作为workspaceID，deviceID让MySQL自动生成：

INSERT INTO devices (workspaceID, owner, device)
SELECT ID_all, owner, device FROM alldata;

• 插入datasize表：通过原表ID关联workspace和devices表的对应ID：

INSERT INTO datasize (deviceID, workspaceID, datasize)
SELECT d.deviceID, w.workspaceID, a.datasize
FROM alldata a
JOIN workspace w ON a.ID_all = w.workspaceID
JOIN devices d ON a.ID_all = d.workspaceID;

方案二：用存储过程逐行处理（适合原表ID不连续或复杂关联场景） #

如果你的原表ID不是连续的，或者不想和原表ID绑定，就用存储过程逐行遍历原表数据，每插入一张表就获取对应的自增ID，再插入下一张关联表：

1. 创建并执行存储过程 #

DELIMITER //
CREATE PROCEDURE migrate_alldata_to_new_tables()
BEGIN
    -- 声明变量存储原表数据和新生成的关联ID
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id_all INT;
    DECLARE v_date VARCHAR(500);
    DECLARE v_owner VARCHAR(500);
    DECLARE v_device VARCHAR(500);
    DECLARE v_number VARCHAR(500);
    DECLARE v_datasize VARCHAR(500);
    DECLARE v_workspace_id INT;
    DECLARE v_device_id INT;
    
    -- 声明游标遍历原表所有数据
    DECLARE data_cursor CURSOR FOR 
        SELECT ID_all, date, owner, device, number, datasize FROM alldata;
    -- 游标结束时的处理逻辑
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 打开游标开始遍历
    OPEN data_cursor;
    data_loop: LOOP
        FETCH data_cursor INTO v_id_all, v_date, v_owner, v_device, v_number, v_datasize;
        IF done THEN
            LEAVE data_loop;
        END IF;
        
        -- 插入workspace表，获取生成的workspaceID
        INSERT INTO workspace (date, number) VALUES (v_date, v_number);
        SET v_workspace_id = 82983;
        
        -- 插入devices表，关联刚生成的workspaceID，获取deviceID
        INSERT INTO devices (workspaceID, owner, device) VALUES (v_workspace_id, v_owner, v_device);
        SET v_device_id = 82983;
        
        -- 插入datasize表，关联之前生成的两个ID
        INSERT INTO datasize (deviceID, workspaceID, datasize) VALUES (v_device_id, v_workspace_id, v_datasize);
    END LOOP;
    
    -- 关闭游标
    CLOSE data_cursor;
END //
DELIMITER ;

-- 调用存储过程执行迁移
CALL migrate_alldata_to_new_tables();

额外注意事项 #

1. 数据备份：操作前一定要备份原表数据，避免迁移过程中数据丢失；
2. 字段类型转换：你的新表workspace的date字段是datetime类型，而原表是varchar，如果原表日期格式是标准的YYYY-MM-DD，MySQL会自动转换，但如果格式不标准，需要用STR_TO_DATE(v_date, '%Y-%m-%d')手动转换；
3. 测试验证：先在测试环境执行迁移，验证数据关联是否正确，再到生产环境操作。

备注：内容来源于stack exchange，提问作者phpfreund