MySQL:将特定外键的所有实例替换为新值 - MySQL: Replace all instances of specific foreign key with new value

我有一个MySQL数据库，里面有1000条人事记录，通常都是重复的。

对于每个至少有一个重复项的情况，我希望能够删除除一个之外的所有重复项，然后用我没有删除的外键更新对那些已删除外键的任何引用。

例如，我们在下面看到Star Lord的两个实例：

+-----------------------+
|        `users`        |
+------+----------------+
| id   | name           |
+------+----------------+
| 1    | Star Lord      |
+------+----------------+
| 2    | Star Lord      |
+------+----------------+
| 3    | Iron Man       |
+------+-----+----------+
+-----------------------+
|       `messages`      |
+------+-----+----------+
| from | to  | text     |
+------+-----+----------+
| 1    | 5   | hi       |
+------+-----+----------+
| 2    | 5   | how r u  |
+------+-----+----------+
| 5    | 2   | Good, u? |
+------+-----+----------+

这两张表应该变成：

+-----------------------+
|        `users`        |
+------+----------------+
| id   | name           |
+------+----------------+
| 1    | Star Lord      |
+------+----------------+
| 3    | Iron Man       |
+------+-----+----------+
+-----------------------+
|       `messages`      |
+------+-----+----------+
| from | to  | text     |
+------+-----+----------+
| 1    | 5   | hi       |
+------+-----+----------+
| 1    | 5   | how r u  |
+------+-----+----------+
| 5    | 1   | Good, u? |
+------+-----+----------+

这能做到吗？我很乐意根据需要使用PHP。

我发现了以下内容，但它仅用于查找外键用法，而不是替换特定键值的实例：MySQL：如何查找所有具有引用特定表的外键的表。列AND具有这些外键的值？

奖励积分

可能有额外的数据需要合并到users表中。例如，ID为#1的Star Lord可能填充了phone字段，但ID为#2的Star Lord具有email字段。

最坏的情况是：它们和都有一个字段，数据冲突。

我建议：

创建一个包含正确数据的表。一个好的起点可能是：
```
CREATE TABLE users_new LIKE users;
ALTER  TABLE users_new ADD UNIQUE (name);
INSERT INTO users_new
  (id, name, phone, email)
SELECT   MIN(id), name, GROUP_CONCAT(phone), GROUP_CONCAT(email)
FROM     users
GROUP BY name;
```
请注意，由于您在"积分"下观察到的"最坏情况"，您可能需要在存档基础users数据之前手动验证此表的内容（我建议不要永久删除，以防万一）。

更新现有的对外关系：

UPDATE messages
  JOIN (users uf JOIN users_new unf USING (name)) ON uf.id = messages.from
  JOIN (users ut JOIN users_new unt USING (name)) ON ut.id = messages.to
SET    messages.from = unf.id,
       messages.to   = unt.id

如果有很多表要更新，可以缓存users和users_new之间的联接结果——或者：

在旧users表的new_id列中：

ALTER TABLE users ADD new_id BIGINT UNSIGNED;
UPDATE users JOIN users_new USING (name)
SET    users.new_id = users_new.id;
UPDATE messages
  JOIN users uf ON uf.id = messages.from
  JOIN users ut ON ut.id = messages.to
SET    messages.from = uf.new_id,
       messages.to   = ut.new_id;

或者在新的（临时）表中：

CREATE TEMPORARY TABLE newid_cache (
  PRIMARY KEY(old_id),
  KEY(old_id, new_id)
) ENGINE=MEMORY
SELECT users.id AS old_id, users_new.id AS new_id
FROM   users JOIN users_new USING (name);
UPDATE messages
  JOIN newid_cache nf ON nf.old_id = messages.from
  JOIN newid_cache nt ON nt.old_id = messages.to
SET    messages.from = nf.new_id,
       messages.to   = nt.new_id;

用users_new替换users，或者修改应用程序以使用新表代替旧表。

ALTER TABLE users     RENAME TO users_old;
ALTER TABLE users_new RENAME TO users;

根据需要更新任何外键约束。

我喜欢非常有条理地处理这件事，虽然你可以在一个复杂的查询中编写所有内容，但这是一种优化，除非很明显，否则是一种不必要的优化。

首先备份数据库：）Create a table来保存要保留的用户的ID。

用填充

Insert into Keepers Select keep_id From (Select Min(id) as keep_id,`name` From `users`)

在那之后，它只是加入的一些更新。

例如

UPDATE 
   `messages` m JOIN
   keepers k 
      ON k.keeper_id = m.from 
SET m.from = k.keeper_id
UPDATE 
   `messages` m JOIN
   keepers k 
      ON k.keeper_id = m.to 
SET m.to = k.keeper_id

然后摆脱你不想要的用户

Delete `users`
from `users` u
outer join keepers on k.keeper_id = u.id
where i.id is null

When一切都很好，例如，你有和刚开始一样多的消息，没有人在自言自语等等。

Delete the keepers table.

语法未检查，但应该是关闭的。