mysql - 如何在 MYSQL 中合并记录

Question

我能够在 Oracle 和 MSSQL 中使用 MERGE 语句。现在我必须使用MYSQL。 MYSQL有类似的合并数据语句吗？

假设我有两个表:

create table source
(
 col1 bigint not null primary key auto_increment,
 col2 varchar(100),
 col3 varchar(50)
 created datetime
)

create table destination
(
 col1 bigint not null primary key auto_increment,
 col2 varchar(100),
 col3 varchar(50)
 created datetime 
)

现在我想将所有数据从“源”移动到“目的地”。如果记录已存在于“目的地”中，则我需要更新，否则我需要插入。

在MSSQL中我使用以下MERGE语句，类似的可以在ORACLE中使用:

MERGE destination AS TARGET
USING(SELECT * FROM source WHERE col2 like '%GDA%') AS SOURCE
ON
(TARGET.col1 = SOURCE.col1)
WHEN MATCHED THEN
UPDATE SET TARGET.col2 = SOURCE.col2,
           TARGET.col3 = SOURCE.col3
WHEN NOT MATCHED THEN
INSERT INTO
(col2,col3,created)
VALUES
(
SOURCE.col2,
SOURCE.col3,
GETDATE()
)OUTPUT $action INTO $tableAction;

WITH mergeCounts AS
(
SELECT COUNT(*) cnt,
     MergeAction
  FROM @tableAction
 GROUP BY MergeAction
) 
SELECT @Inserted = (SELECT ISNULL(cnt,0) FROM mergeCounts WHERE MergeAction = 'INSERT'),
     @Updated = (SELECT ISNULL(cnt,0) FROM mergeCounts WHERE MergeAction = 'UPDATE'),
     @Deleted = (SELECT ISNULL(cnt,0) FROM mergeCounts WHERE MergeAction = 'DELETE')          

所以在这里我更新记录(如果存在)并插入(如果有新记录)。在 MERGE 语句之后，我还能够计算插入、更新了多少条记录......

在MYSQL中是否可以有这样的实现？

Answer 1

Mysql 有 insert ... on Dund key update ... 语法。像这样使用它:

insert into destination(col1, col2, col3, created) 
  select * 
    from source
  on duplicate key update
    col2 = values(col2),
    col3 = values(col3),
    created = values(created);

demo here

要获取受影响的行数，请随后运行select row_count()