MySQL如何为user表无social_media关联的存量用户批量创建关联并更新外键

操作前务必先完成数据备份，避免操作失误导致数据丢失

操作步骤 #

• 执行备份命令，备份user和social_media两个表的数据：

  mysqldump -u[你的数据库用户名] -p [你的数据库名] user social_media > pre_migration_backup.sql
  

  执行后输入数据库密码即可完成备份，出现异常可以直接用备份文件回滚数据。
• 给social_media表临时添加关联用户ID的字段，用于后续映射对应关系：

  ALTER TABLE social_media ADD COLUMN temp_user_id INT;
  

• 批量为所有未关联社交媒体记录的用户插入空的social_media记录：

  INSERT INTO social_media (temp_user_id, instagram, facebook, twitter)
  SELECT id, NULL, NULL, NULL FROM user WHERE social_media_id IS NULL;
  

  这一步会自动生成自增主键id，并将对应用户的ID存入临时字段temp_user_id中。
• 回填user表的外键字段social_media_id：

  UPDATE user u
  INNER JOIN social_media sm ON u.id = sm.temp_user_id
  SET u.social_media_id = sm.id;
  

• 清理临时添加的字段：

  ALTER TABLE social_media DROP COLUMN temp_user_id;
  

校验与注意事项 #

操作完成后可以执行以下语句校验结果，返回值为0则说明所有用户都已关联社交媒体记录：

SELECT COUNT(*) FROM user WHERE social_media_id IS NULL;

如果你此前已经给user.social_media_id添加了非空外键约束，需要先临时取消约束，完成数据回填后再重新启用约束即可。

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