mysql - 当列应该变得唯一时合并相关数据

Question

给定一个包含如下表的 mysql 数据库:

author:
+----+----------+
| id | name     |
+----+----------+
| 1  | John     |
| 2  | Peter    |
| 3  | Peter    |
+----+----------+

article:
+----+-----------+------+
| id | author_id | text |
+----+-----------+------+
| 1  | 2         | ...  |
| 2  | 3         | ...  |
| 3  | 3         | ...  |
+----+-----------+------+

作者表的名称列并非偶然设置为唯一。现在我必须将相关文章“合并”到其中一位相关作者中，即将文章 2 和 3 的 author_id 设置为 2。之后我想使名称列唯一。

我无法手动重新分配文章，因为受影响的记录太多。但我认为这个问题可能有现成的解决方案/片段。

Answer 1

要更新您的 article 表，可以这样做:

update article art
   set art.author_id = (select min(aut.id)
                          from author aut
                         where aut.name = (select a.name
                                             from author a
                                            where a.id = art.author_id));

select * from article;    
+ ------- + -------------- + --------- +
| id      | author_id      | text      |
+ ------- + -------------- + --------- +
| 1       | 2              |           |
| 2       | 2              |           |
| 3       | 2              |           |
+ ------- + -------------- + --------- +
3 rows

如果你喜欢更紧凑的更新(和更优化)，那么你可以使用这个，它的工作方式相同:

update article art
   set art.author_id = (select min(aut.id)
                          from author aut
                         inner join author a on a.name = aut.name
                         where a.id = art.author_id);

最后，要删除多余的作者，您需要

delete a
  from author a
 inner join (
    select name, min(id) as min -- this subquery returns all repeated names and their smallest id
      from author
     group by name
    having count(*) > 1) repeated on repeated.name = a.name
 where a.id > repeated.min;     -- delete all repeateds except the first one

select * from author;    
+ ------- + --------- +
| id      | name      |
+ ------- + --------- +
| 1       | John      |
| 2       | Peter     |
+ ------- + --------- +
2 rows

这适用于任意数量的重复作者集。

希望对你有帮助