mysql快速合并大表_如何将两个大型mysql表合并为一个具有相似...

由于您手边有大量数据,我建议您先合并日期和时间列.然后,您可以有效地使用索引.如果你不这样做,你将不得不做类似的事情

...WHERE CONCAT(date, ' ', time) = SELECT MAX(CONCAT(date, ' ', time)) ...

所以,首先对两个表都这样做.

ALTER TABLE tableA ADD COLUMN creation_date datetime; /*or whatever name, just make it meaningful and don't use keywords*/

UPDATE tableA SET creation_date = CONCAT(date, ' ', time);

ALTER TABLE tableA DROP COLUMN date, DROP COLUMN time;

CREATE INDEX idx_dt_tableA_creation ON tableA(creation_date);

然后,您可以将两个表插入combine_table

(注意,保留完整性,第二个选项要好得多).

INSERT INTO combined_table

SELECT col1, col2, creation_date

FROM (

SELECT col1, col2, creation_date

FROM tableA

UNION ALL

SELECT col1, col2, creation_date

FROM tableB

) sq /*subquery_alias*/

WHERE creation_date = (SELECT MAX(creation_date) FROM (

SELECT col1, col2, creation_date

FROM tableA

UNION ALL

SELECT col1, col2, creation_date

FROM tableB

) another_sq

WHERE sq.col1 = another_sq.col1

)

;

尽管如此,如果您真的拥有那么多数据,这将是一项繁重的操作.

现在我想到了,有一个更好的方法.

首先插入表A.

INSERT INTO combined_table

SELECT * FROM tableA;

INSERT INTO combined_table c

SELECT * FROM tableB b

ON DUPLICATE KEY UPDATE

/*you can skip col1, since it's the identifying primary key here*/

col2 = IF(b.creation_date > c.creation_date, b.col2, c.col2),

creation_date = IF(b.creation_date > c.creation_date, b.creation_date, c.creation_date)

;