MySQL 5.5:复制具有不同ID的相同用户信息的有效方法 [英] MySQL 5.5: Efficient way to copy the same user information with different IDs

问题描述

在MySQL 5.5中，假设我们具有以下MAIN表

In MySQL 5.5, assume we have the following MAIN table

Id  Name    Score
3   a   100
3   b   99
4   c   98
4   d   97

还有一个SUB表:

Id  New_Id
3   1
3   1
4   2
4   2

理想的输出是OUTPUT表:

The ideal output is OUTPUT table:

Id  Name    Score
3   a   100
3   b   99
4   c   98
4   d   97
1   a   100
1   b   99
2   c   98
2   d   97

此处提供MySQL小提琴

The MySQL fiddle is available here

http://sqlfiddle.com/#!9/91c1cf/6

在OUTPUT表中，

我们可以看到Id = 1与Id = 3共享相同的信息.同样，

we can see that the Id=1 shares the same information as Id =3. Similarly,

我们可以看到Id = 2与Id = 4共享相同的信息.

we can see that the Id=2 shares the same information as Id =4.

有没有简单的方法，例如"INSERT INTO ... SELECT"，"COPY"和"UPDATE"等，我们可以将数据从Id = 3复制到Id = 1，并且

Is there any simple way such as "INSERT INTO...SELECT", "COPY", and "UPDATE" etc. that we can just copy the data from Id=3 to Id=1, and

将数据从Id = 4复制到Id = 2吗?

copy the data from Id=4 to Id=2?

以下查询确实生成了理想的输出，但是在我们的几亿行中，具有正确索引的JOIN仍然很慢.在数十亿行的情况下，由于RAM和/tmp文件夹空间不足，作业甚至失败.我们正在将系统从MySQL升级到更易于扩展的地方.但是，我们需要确保MySQL系统在过渡期的几个月内能够正常运行.任何2美分将不胜感激！

The following query does generate our ideal output, but the JOIN with the proper indexes is still painfully slow in our few hundreds millions rows. The job even fails due to not sufficient RAM and /tmp folder space in the case of few billion rows. We are upgrading our system from MySQL to somewhere more scab-able. However, we need to make sure the MySQL system will be functional during the few months of the transit period. Any 2 cents will be highly appreciated!

SELECT Id, Name, Score FROM MAIN

UNION

SELECT d.New_Id AS Id, c.Name, c.Score FROM MAIN c
RIGHT JOIN SUB d

ON c.Id = d.Id;

推荐答案

使用INNER JOIN而不是RIGHT JOIN，因为您不需要来自不匹配行的空行.您可以使用INSERT INTO ... SELECT将这些新行添加到表中.而不是使用UNION，您只需在新表中进行两次插入即可:

Use INNER JOIN rather than RIGHT JOIN, since you don't need the null rows that result from non-matching rows. You can use INSERT INTO ... SELECT to add these new rows to the table. And rather than using UNION, you can simply do two inserts into the new table:

INSERT INTO OUTPUT (id, name, score)
SELECT id, name, score
FROM MAIN;

INSERT INTO OUTPUT (id, name, score)
SELECT d.new_id, c.name, c.score
FROM MAIN AS c
JOIN SUB AS d ON c.id = d.id;

只要在两个输入表的id列上都有索引，这应该尽可能有效.

As long as you have indexes on the id columns in both input tables this should be as efficient as possible.

最后，当执行大型UNION查询时，如果您知道没有任何重复项需要合并，请使用UNION ALL. UNION本身默认为UNION DISTINCT，因此它需要创建一个临时表来保存所有结果以扫描重复项.

Finally, when doing large UNION queries, use UNION ALL if you know there are no duplicates that need to be merged. UNION by itself defaults to UNION DISTINCT, so it needs to create a temporary table to hold all the results to scan for duplicates.

这篇关于MySQL 5.5:复制具有不同ID的相同用户信息的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！