如何合并两个MySQL表?

How can I merge two MySQL tables that have the same structure?

如何合并具有相同结构的两个MySQL表?

The primary keys of the two tables will *, so I have take that into account.

这两个表的主键会发生冲突，所以我已经考虑到了这一点。

6 个解决方案

#1

111

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.

将使用表_2中的对应行更新表_1中已经存在的行，同时使用新的主键插入行。

#2

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意味着什么，您需要找到一种方法来确定哪个记录应该具有优先级。您可以创建一个select查询，以首先查找重复的内容(参见cpitis的answer)。然后删除那些您不想保存的，并使用上面的insert来添加剩下的记录。

#3

INSERT
INTO    first_table f
SELECT  *
FROM    second_table s
ON DUPLICATE KEY
UPDATE
        s.column1 = DO_WHAT_EVER_MUST_BE_DONE_ON_KEY_*(f.column1)

#4

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查询中删除括号，导致下面注释中的错误]

#5

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

#6

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- easy -merge-two-同一性-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;

#1

111