mysql实现merge into_详解Oracle、mysql、pg、mssql数据库merge into语法实现（二）

概述

多表关联查询的时候会用到临时表插入数据，然后再用select查行查询，在往临时表里插入数据的时候，我们经常会用到判断如果临时表里有了这部分数据我们就要更新数据，如果临时表里没有这部分数据我们就要插入，这个时候可以怎么去实现呢？

下面介绍mysql的replace into语法和mysql数据库on duplicate key update实现方式。

一、mysql的replace into语法

1、语法

Mysql replace与replace into都是经常会用到的功能；replace其实是做了一次update操作，而不是先delete再insert；而replace into其实与insert into很像，但对于replace into，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。--前两种形式用的多些,对于那些没有给予值的列，MySQL将自动为这些列赋上默认值replace into table(col, ...) values(...)replace into table(col, ...) select ...replace into table set col=value, ...

2、实验

1)环境准备--建表create table t1(id bigint(10),name varchar(16),sale bigint(10),operatime datetime,UNIQUE KEY `idx_id` (`id`) USING BTREE);create table t2(id bigint(10),name varchar(16),sale bigint(20),UNIQUE KEY `idx_id` (`id`) USING BTREE);-- 插入数据INSERT into t1 values(1,"xiaohong",1000,now());INSERT into t1 values(2,"xiaoming",500,now());INSERT into t2 values(1,"xiaohong",300);INSERT into t2 values(2,"xiaoming",400);INSERT into t2 values(3,"xiaoxiao",900);

2)mysql replace into实现replace into t1 select id,name,sale,now() from t2;

结果：实现了将表t2更新到表t1中去,存在就更新，不存在就插入。

二、mysql的on duplicate key update语法

mysql并没有oracle、mssql的merge into语法，但是有个on duplicate key update语法(不是标准的sql语法)可以实现merge into语法。ON DUPLICATE KEY UPDATE为Mysql特有语法，使用时应多注意主键和插入值是否是我们想要插入或修改的key、Value。

创建表，注意要有一个唯一索引 new_code_index， 插入或者更新时，以此为标准。另外在高并发环境下，禁用insert into …on duplicate key update…,因为会造成mysql主从不一致。

1、相关语法--单条记录下使用--假如t1表的主键或者UNIQUE 索引是a，如果数据库里面已经存在a=1的记录则更新这条记录的c字段的值为原来值+1，然后返回值为2。--如果不存在则插入a=1,b=2,c=3到数据库，然后返回值为1。INSERT INTO t1(a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;--多记录下使用INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(c);

2、实验

1)环境准备--建表create table t1(id bigint(10),name varchar(16),sale bigint(10),operatime datetime,UNIQUE KEY `idx_id` (`id`) USING BTREE);create table t2(id bigint(10),name varchar(16),sale bigint(20),UNIQUE KEY `idx_id` (`id`) USING BTREE);-- 插入数据INSERT into t1 values(1,"xiaohong",1000,now());INSERT into t1 values(2,"xiaoming",500,now());INSERT into t2 values(1,"xiaohong",300);INSERT into t2 values(2,"xiaoming",400);INSERT into t2 values(3,"xiaoxiao",900);

2)on duplicate key update实现insert into t1 select id,name,sale,now() from t2 on duplicate key update sale=values(sale);

结果：实现了将表t2更新到表t1中去,存在就更新，不存在就插入,

注意：id字段是主键或UNIQUE索引，不然只会插入t2表所有行数据