如何合并两个 MySQL 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/725556/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I merge two MySQL tables?
提问by Steve McLeod
How can I merge two MySQL tables that have the same structure?
如何合并两个具有相同结构的 MySQL 表？
The primary keys of the two tables will clash, so I have take that into account.
两个表的主键会发生冲突，所以我已经考虑到了这一点。
回答by fcw
You can also try:
你也可以试试：
INSERT IGNORE
INTO table_1
SELECT *
FROM table_2
;
which allows those rows in table_1 to supersede those in table_2 that have a matching primary key, while still inserting rows with new primary keys.
这允许 table_1 中的那些行取代 table_2 中具有匹配主键的那些行，同时仍然插入具有新主键的行。
Alternatively,
或者，
REPLACE
INTO table_1
SELECT *
FROM table_2
;
will update those rows already in table_1 with the corresponding row from table_2, while inserting rows with new primary keys.
将使用 table_2 中的相应行更新 table_1 中已有的那些行，同时插入具有新主键的行。
回答by Milan Babu?kov
It depends on the semantic of the primary key. If it's just autoincrement, then use something like:
这取决于主键的语义。如果它只是自动增量，那么使用类似的东西：
insert into table1 (all columns except pk)
select all_columns_except_pk
from table2;
If PK means something, you need to find a way to determine which record should have priority. You could create a select query to find duplicates first (see answer by cpitis). Then eliminate the ones you don't want to keep and use the above insert to add records that remain.
如果PK意味着什么，您需要找到一种方法来确定哪个记录应该具有优先权。您可以创建一个选择查询来首先查找重复项（请参阅cpitis 的回答）。然后消除那些您不想保留的并使用上面的插入来添加保留的记录。
回答by Quassnoi
INSERT
INTO first_table f
SELECT *
FROM second_table s
ON DUPLICATE KEY
UPDATE
s.column1 = DO_WHAT_EVER_MUST_BE_DONE_ON_KEY_CLASH(f.column1)
回答by C?t?lin Piti?
If you need to do it manually, one time:
如果您需要手动执行此操作，请执行一次：
First, merge in a temporary table, with something like:
首先，合并一个临时表，类似于：
create table MERGED as select * from table 1 UNION select * from table 2
Then, identify the primary key constraints with something like
然后，用类似的东西识别主键约束
SELECT COUNT(*), PK from MERGED GROUP BY PK HAVING COUNT(*) > 1
Where PK is the primary key field...
其中 PK 是主键字段...
Solve the duplicates.
解决重复项。
Rename the table.
重命名表。
[edited - removed brackets in the UNION query, which was causing the error in the comment below]
[编辑 - 删除了 UNION 查询中的括号，这导致了下面评论中的错误]
回答by Bill Warren
Not as complicated as it sounds.... Just leave the duplicate primary key out of your query.... this works for me !
不像听起来那么复杂......只需将重复的主键从你的查询中删除......这对我有用！
INSERT INTO
Content(
`status`,
content_category,
content_type,
content_id,
user_id,
title,
description,
content_file,
content_url,
tags,
create_date,
edit_date,
runs
)
SELECT `status`,
content_category,
content_type,
content_id,
user_id,
title,
description,
content_file,
content_url,
tags,
create_date,
edit_date,
runs
FROM
Content_Images
回答by TimoSolo
You could write a script to update the FK's for you.. check out this blog: http://multunus.com/2011/03/how-to-easily-merge-two-identical-mysql-databases/
您可以编写一个脚本来为您更新 FK .. 查看此博客：http: //multunus.com/2011/03/how-to-easily-merge-two-identical-mysql-databases/
They have a clever script to use the information_schema tables to get the "id" columns:
他们有一个巧妙的脚本来使用 information_schema 表来获取“id”列：
SET @db:='id_new';
select @max_id:=max(AUTO_INCREMENT) from information_schema.tables;
select concat('update ',table_name,' set ', column_name,' = ',column_name,'+',@max_id,' ; ') from information_schema.columns where table_schema=@db and column_name like '%id' into outfile 'update_ids.sql';
use id_new
source update_ids.sql;
