PostgreSQL迁移：将主键id从bigint改为uuid并保留关联关系

以下是不丢失数据、维持表间关联的迁移步骤，适用于主流关系型数据库（PostgreSQL/MySQL等）：

一、前置准备 #

• 全量备份数据：迁移前务必备份整个数据库，避免操作失误导致数据丢失。
• 确认数据库支持uuid：PostgreSQL原生支持uuid类型，需先安装uuid-ossp扩展；MySQL可使用CHAR(36)存储uuid，依赖UUID()函数生成值。

二、处理主表（以person表为例） #

假设原主表主键为bigint类型的id，步骤如下：

1. 添加uuid类型的新列，并为已有数据生成唯一uuid：

   -- PostgreSQL
   ALTER TABLE person ADD COLUMN uuid UUID;
   UPDATE person SET uuid = uuid_generate_v4();
   
   -- MySQL
   ALTER TABLE person ADD COLUMN uuid CHAR(36);
   UPDATE person SET uuid = UUID();
   

2. 为新列添加唯一约束，确保数据唯一性：

   ALTER TABLE person ADD CONSTRAINT unique_person_uuid UNIQUE(uuid);
   

3. 重命名原主键列，保留作为临时关联依据：

   ALTER TABLE person RENAME COLUMN id TO id_obsolete;
   

4. 将新uuid列重命名为id：

   ALTER TABLE person RENAME COLUMN uuid TO id;
   

   注意：此时暂时不要将新id设为主键，需等所有关联表处理完成后再操作。

三、处理关联表（以users表的person_id外键为例） #

1. 添加临时uuid列，用于存储关联的主表uuid值：

   -- PostgreSQL
   ALTER TABLE users ADD COLUMN person_uuid UUID;
   -- MySQL
   ALTER TABLE users ADD COLUMN person_uuid CHAR(36);
   

2. 通过原外键值关联主表，填充临时uuid列：

   -- PostgreSQL
   UPDATE users u SET person_uuid = p.id FROM person p WHERE u.person_id = p.id_obsolete;
   
   -- MySQL
   UPDATE users u JOIN person p ON u.person_id = p.id_obsolete SET u.person_uuid = p.id;
   

3. 验证数据正确性：检查填充后的person_uuid非空数是否与原person_id非空数一致，避免漏填：

   -- 检查PostgreSQL
   SELECT COUNT(*) FROM users WHERE person_uuid IS NULL;
   -- 检查MySQL
   SELECT COUNT(*) FROM users WHERE person_uuid IS NULL;
   

4. 删除原外键约束（原外键类型与新主键不匹配，需重新建立）：

   -- 先查看外键名称，再删除（示例外键名为fk_users_person）
   ALTER TABLE users DROP FOREIGN KEY fk_users_person;
   

5. 重命名原外键列，并将临时列改为正式外键列：

   ALTER TABLE users RENAME COLUMN person_id TO person_id_obsolete;
   ALTER TABLE users RENAME COLUMN person_uuid TO person_id;
   

6. 为新外键列添加外键约束，关联主表的uuid主键：

   -- PostgreSQL
   ALTER TABLE users ADD CONSTRAINT fk_users_person FOREIGN KEY (person_id) REFERENCES person(id);
   -- MySQL
   ALTER TABLE users ADD CONSTRAINT fk_users_person FOREIGN KEY (person_id) REFERENCES person(id);
   

7. 为新外键列添加索引，避免查询性能下降：

   CREATE INDEX idx_users_person_id ON users(person_id);
   

四、最终完成主表主键切换 #

所有关联表处理完成后，将主表的新id设为主键：

ALTER TABLE person ADD PRIMARY KEY(id);

五、后续清理（可选） #

• 确认迁移完全正常后，可删除所有*_obsolete列，清理冗余数据。
• 若数据库支持，可为主表的uuid主键设置默认值，确保新插入数据自动生成uuid：

  -- PostgreSQL
  ALTER TABLE person ALTER COLUMN id SET DEFAULT uuid_generate_v4();
  -- MySQL
  ALTER TABLE person ALTER COLUMN id SET DEFAULT UUID();
  

注意事项 #

• 分批处理大表：若数据量极大，避免一次性执行全表更新，可分批次通过LIMIT或范围条件更新，减少锁表时间。
• 事务包裹：将每个表的迁移步骤放在事务中执行，若中途出错可回滚，保证数据一致性。
• 测试环境验证：先在测试环境完成全流程验证，确认无问题后再在生产环境执行。

内容的提问来源于stack exchange，提问作者George Morris