mysql - 根据现有列数据设置外键值

Question

我正在将旧的(非标准化)数据库迁移到新版本。

现在我得到了这个中间结果:

    CREATE TABLE recipient(        id int NOT NULL AUTO_INCREMENT,        email VARCHAR(255),        PRIMARY KEY (id),        UNIQUE INDEX (`email`),    ) ENGINE=INNODB;    CREATE TABLE comment(        # Right now this is always NULL:        fk_recipient INT,        # Temporary solution. Note that this field is NOT UNIQUE:        tmp_email VARCHAR(255) NOT NULL,        # ...        FOREIGN KEY (`fk_recipient`) REFERENCES recipient(id);    ) ENGINE=INNODB;

两个表都填充了正确的数据:

• 表 comment 中具有正确 tmp_email 和 fk_recipient = null 的数百万条评论(注意:电子邮件不是唯一的)
• 表收件人中有数十万个唯一电子邮件地址。

我需要做什么:

我想删除 comment.tmp_email 列，而是将 comment.fk_recipient 指向表 recipient 中的相应行。

我当前的方法(使用 PHP):

1. 获取所有评论
2. 迭代所有评论:
   • 在收件人表中查找右行
   • 设置右外键
3. ...删除列 tmp_email

这需要很长时间，让我想知道是否没有本地 MySQL 方法可以做到这一点？

Answer 1

以下解决方法可以完成这项工作:

1. 在 comment.tmp_email 上创建临时外键:

   ALTER TABLE comment    ADD CONSTRAINT `tmpMailKey` FOREIGN KEY (`tmp_email`)    REFERENCES `recipient`(`email`);

2. 在临时键上连接两个表并使用该信息设置真正的外键:

   UPDATE comment cINNER JOIN recipient rON        c.tmp_email = r.email    AND c.tmp_email IS NOT NULLSET c.fk_recipient = r.id;

3. 删除临时外键(以及 tmp 列):

   ALTER TABLE `udw_v3`.`travelogue_guestbookentry`    DROP COLUMN `tmp_email`,    DROP INDEX `tmpMailKey`,    DROP FOREIGN KEY `tmpMailKey`;