如何合并两个 MySQL 表？

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...

Solve the duplicates.

Rename the table.

[edited - removed brackets in the UNION query, which was causing the error in the comment below]

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)

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.

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.

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.

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/

They have a clever script to use the information_schema tables to get the "id" columns:

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;

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