如何将表中列数据迁移至新表并在原表中将对应ID设为外键

以下为通用关系型数据库（兼容MySQL、PostgreSQL等）的实操流程，操作前请先备份全量数据。

1. 预配置表结构 #

首先确保application_command表已提前创建，基础结构要求：

• 主键字段id：自增主键，类型建议为INT或BIGINT
• 命令存储字段command：类型与原表allowed_command完全一致

然后给原表新增外键关联字段：

ALTER TABLE application ADD COLUMN application_command_id INT;

注意：application_command_id的字段类型必须和application_command表的主键id类型完全匹配

2. 迁移命令值到新表 #

去重插入原表的allowed_command取值到新表，避免冗余存储：

INSERT INTO application_command (command)
SELECT DISTINCT allowed_command FROM application WHERE allowed_command IS NOT NULL;

如果业务要求相同命令值也生成多条独立记录，可删除语句中的DISTINCT关键字

3. 回填外键ID到原表 #

关联两张表，将新生成的主键ID回写到application表的外键列：

MySQL 语法 #

UPDATE application a
INNER JOIN application_command ac 
ON a.allowed_command = ac.command
SET a.application_command_id = ac.id;

PostgreSQL 语法 #

UPDATE application a
SET application_command_id = ac.id
FROM application_command ac
WHERE a.allowed_command = ac.command;

4. 校验与收尾 #

• 数据一致性校验：执行以下语句，返回结果为0则代表所有非空命令都完成关联

  SELECT COUNT(*) FROM application 
  WHERE allowed_command IS NOT NULL AND application_command_id IS NULL;
  

• 可选操作：确认数据无误后可删除原allowed_command字段

  ALTER TABLE application DROP COLUMN allowed_command;
  

• 可选操作：添加外键约束保障数据一致性

  -- 先给外键列加非空约束（仅当所有记录都有对应command时执行）
  ALTER TABLE application MODIFY COLUMN application_command_id INT NOT NULL;
  -- 添加外键约束
  ALTER TABLE application ADD CONSTRAINT fk_app_cmd FOREIGN KEY (application_command_id) REFERENCES application_command(id);
  

重要提醒：所有操作请先在测试环境验证执行逻辑，确认无问题后再操作生产环境，避免数据丢失。

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