Laravel迁移：将旧数据表列转成行实现数据规范化

旧表的每一行对应新表的多条数据（每个非空的X_Y列对应新表一条记录），我们需要遍历旧表的每条记录，逐个处理这些列，提取出user_id、pick_id和gender_id：

步骤1：定义旧列与新字段的映射关系 #

先把旧表的列名对应到新表的pick_id和gender_id（按你给出的规则，_1对应雌性gender_id=1，_2对应雄性gender_id=2，列名前缀是pick_id），用数组存储映射：

$columnMappings = [
    '1_1' => ['pick_id' => 1, 'gender_id' => 1],
    '2_1' => ['pick_id' => 2, 'gender_id' => 1],
    '3_1' => ['pick_id' => 3, 'gender_id' => 1],
    '4_1' => ['pick_id' => 4, 'gender_id' => 1],
    '5_1' => ['pick_id' => 5, 'gender_id' => 1],
    '1_2' => ['pick_id' => 1, 'gender_id' => 2],
    '2_2' => ['pick_id' => 2, 'gender_id' => 2],
    '3_2' => ['pick_id' => 3, 'gender_id' => 2],
    '4_2' => ['pick_id' => 4, 'gender_id' => 2],
    '5_2' => ['pick_id' => 5, 'gender_id' => 2],
];

步骤2：修改迁移代码，遍历处理每条旧记录和列 #

替换原循环逻辑，改为遍历每个列映射，提取对应值写入新表：

$oldPicks = DB::connection('old')->table('picks')->get();

foreach ($oldPicks as $oldPick) {
    foreach ($columnMappings as $columnName => $mapping) {
        $userId = $oldPick->{$columnName};
        // 跳过未分配的user_id（null值）
        if (is_null($userId)) {
            continue;
        }

        Pick::updateOrCreate(
            // 唯一匹配条件：同一个窝、挑选位置、性别的组合唯一
            [
                'litter_id' => $oldPick->litter_id,
                'pick_spot' => $mapping['pick_id'],
                'gender_id' => $mapping['gender_id'],
            ],
            // 需要写入/更新的字段
            [
                'user_id' => $userId,
                'updated_at' => $oldPick->updated_at,
                // 若旧表有created_at字段，可添加：'created_at' => $oldPick->created_at,
            ]
        );
    }
}

关键说明 #

• 用$oldPick->{$columnName}动态获取旧表列的值，适配字符串形式的列名
• 跳过user_id为null的情况，避免生成无意义的空记录
• updateOrCreate的第一个参数是唯一匹配规则，确保同组合的记录不会重复创建；第二个参数是实际写入的数据

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