Mysql实现和Oracle一样的merge into功能

     今天在项目实践中有个功能需要将两个相同表结构的数据合在一起，要求是数据能匹配到就进行更新匹配不到就进行插入动作，在oracle中可以用merge into语法实现该功能，mysql中也有类似的语法可以实现即：

insert into 表名(表字段,表字段,表字段,表字段) select a.字段名,a.字段名,a.字段名 from 表2 a on duplicate key update 表1.待更新字段=表2.字段...;

示例：

新建表test：

CREATE TABLE `test`  (
  `key1` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `key2` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `key3` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`key1`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

新建表test1:

CREATE TABLE `test1`  (
  `key1` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `key2` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `key3` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`key1`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

注意两张表至少要有一个相同的唯一索引

然后分别插入数据：test

test1：

执行sql语句：

insert into test(key1,key2,key3) 
select a.key1,a.key2,a.key3 from test1 a
on duplicate key update key2 = a.key2,key3 = a.key3;

执行结果：test表中的数据变化：

可以看出第一行红框中的数据已被更新，新增了第五行的数据。