mysql replace into与on duplicate key update区别，oracle merge into

1、replace into

REPLACE INTO 首先判断数据是否存在；如果不存在，则插入；如果已存在则更新（先删除再插入

• 根据主键或唯一索引判断记录是否已存在，所以插入数据的表必须要有主键或者唯一索引！否则的话，REPLACE INTO 会直接插入数据（相当于INSERT），会导致表中出现重复数据。
• 如果不写某个字段的值则会使用默认值，如果该字段没有定义默认值则报错。
• 要使用REPLACE INTO，必须同时拥有表的INSERT和 DELETE权限。

MySQL replace into 有三种形式
1. replace into tbl_name(col_name, ...) values(...)
2. replace into tbl_name(col_name, ...) select ...
3. replace into tbl_name set col_name=value, ...

// 唯一索引
ALTER TABLE 表名 ADD  UNIQUE KEY `uk_唯一索引名` (`字段名`,`字段名`...);

ALTER TABLE user ADD  UNIQUE KEY `uk_user_id_type` (`user_id`,`type`);

1.1、主键自增 

alter table t_user change id id int(11) not null AUTO_INCREMENT; 

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(40) NOT NULL,
  `password` varchar(40) NOT NULL,
  `sex` varchar(4) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `save_unique_index` (`username`,`password`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

1.2、replace into tbl_name(col_name, ...) values(...)

1. replace into tbl_name(col_name, ...) values(...)
-- 修改前的数据
1  Tom  123  男
2  Tom  1234  男
3  Jerry  234  女
4  Tom  12345  男
 
replace into t_user(username,password) values('Tom','123');
-- 报错，Field 'sex' doesn't have a default value
 
replace into t_user(username,password,sex) values('Tom','123','男');
-- 受影响的行: 2 ，先删除，再插入
-- 修改后的数据
2  Tom  1234  男
3  Jerry  234  女
4  Tom  12345  男
5  Tom  123  男
 
replace into t_user(username,password,sex) values('Tom','123','女');  
-- 受影响的行: 2
-- 修改后的数据
2  Tom  1234  男
3  Jerry  234  女
4  Tom  12345  男
6  Tom  123  女
 
replace into t_user(username,password,sex) values('Tom','123456','男'); 
-- 受影响的行: 1
-- 修改后的数据
2  Tom  1234  男
3  Jerry  234  女
4  Tom  12345  男
6  Tom  123  女
7  Tom  123456  男

1.3、replace into tbl_name(col_name, ...) select ...

CREATE TABLE `t_user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `pwd` varchar(40) NOT NULL,
  `sex` varchar(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
t_user:
2  Tom  1234  男
3  Jerry  234  女
4  Tom  12345  男
6  Tom  123  女
7  Tom  123456  男
 
t_user2:
1  Tom  123  男
2  Tonny  123  女
3  Jerry  234  女
replace into t_user(username,password,sex) select  name,pwd,sex from t_user2 ; 
-- 受影响的行: 5
-- 修改后，(t_user)6删除，(t_user)8添加，(t_user)9添加，(t_user)3删除，(t_user)10添加 
-- （唯一索引，所以‘Tom  123  女’会删除，再插入‘Tom  123  男’）
t_user:
2  Tom  1234  男
4  Tom  12345  男
7  Tom  123456  男
8  Tom  123  男
9  Tonny  123  女
10  Jerry  234  女

1.4、replace into tbl_name set col_name=value, ...

replace into  t_user set username = 'Tommy' ; 
-- Field 'password' doesn't have a default value

replace into  t_user set username = 'Tommy'  ,password = '1234567';  
-- Field 'sex' doesn't have a default value

replace into  t_user set username = 'Tommy'  ,password = '1234567',sex = '男';  
-- 受影响的行: 1
-- 修改后
2  Tom  1234  男
4  Tom  12345  男
7  Tom  123456  男
8  Tom  123  男
9  Tonny  123  女
10  Jerry  234  女
11  Tommy  1234567  男  -- 这一行是新加的
 
replace into  t_user set username = 'Tommy'  ,password = '1234567',sex = '女';  
-- 受影响的行: 2
-- 修改后
2  Tom  1234  男
4  Tom  12345  男
7  Tom  123456  男
8  Tom  123  男
9  Tonny  123  女
10  Jerry  234  女
12  Tommy  1234567  女 
-- 把id=11的记录delete后，再insert id=12的记录

2、on duplicate key update

on duplicate key update 首先判断数据是否存在；如果不存在，则插入；如果已存在则更新

• on duplicate key update 语句根据主键id或唯一键来判断当前插入是否已存在。
• 记录已存在时，只会更新on duplicate key update之后指定的字段。
• 如果同时传递了主键和唯一键，以主键为判断存在依据，唯一键字段内容可以被修改。

2.1、创建表

alter table test change id id int(11) not null AUTO_INCREMENT; 

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `organ_no` varchar(40) NOT NULL,
  `branch_no` varchar(40) NOT NULL,
  `branch_name` varchar(40) NOT NULL,
  `created_by` varchar(40) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `branch_no_index` (`branch_no`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

主键索引:id

唯一索引: branch_no

1、organ_no, branch_no, branch_name, created_by字段都不相同

2、branch_name, created_by不同

3、唯一索引branch_no不同

4、修改的值包括了唯一索引值branch_no

2.2、执行sql  ：organ_no, branch_no, branch_name, created_by字段都不相同

INSERT INTO t_bank_organ_copy1 ( organ_no, branch_no, branch_name, created_by )
VALUES
	( 1, '3', '浙江分行', '3' ) 
	ON DUPLICATE KEY UPDATE
	branch_name = '江苏分行';

因为没有值相同，所以一定添加

2.3、branch_name, created_by与表中数据不同 

INSERT INTO t_bank_organ_copy1 ( organ_no, branch_no, branch_name, created_by )
VALUES
	( 1, '3', '海南分行', '4' ) 
	ON DUPLICATE KEY UPDATE
	branch_name = '江苏分行';

第三条数据从浙江分行改变成了江苏分行

 ON DUPLICATE KEY UPDATE 后的值是要修改的值

而这个会根据唯一索引进行查询，其他普通列不做匹配（主键也是做匹配的）

2.4、唯一索引branch_no不同

INSERT INTO t_bank_organ_copy1 ( organ_no, branch_no, branch_name, created_by )
VALUES
	( 1, '4', '江苏分行', '3' ) 
	ON DUPLICATE KEY UPDATE
	branch_name = '海南分行';

除了唯一索引branch_no不同，其他列都相同

执行后数据库增加一条  

2.5、修改的值包括了唯一索引值branch_no

INSERT INTO t_bank_organ_copy1 ( organ_no, branch_no, branch_name, created_by )
VALUES
	( 1, '4', '江苏分行', '3' ) 
	ON DUPLICATE KEY UPDATE
	branch_name = '海南分行',
	branch_no = '8';

 修改branch_no 4为8，并修改branch_name为海南分行

3、oracle merge into

merge into 目标表 a
 
using 源表 b
 
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)  
 
when matched then update set a.字段=b.字段 --目标表别称a和源表别称b都不要省略
 
when  not matched then insert (a.字段1,a.字段2……)values(b.字段1,b.字段2……) --目标表别称a可省略,源表别称b不可省略

源表b可能是一张表结构不同于a的表,有可能是一张构建相同表结构的临时表,也有可能是我们自己组起来的数据

merge into student a
using (select '7' as id from dual) s
on (a.id = s.id)
when matched then
  update set a.student_name = '小明二号'
when not matched then
  insert (id, student_name, fk_class) values ('7', '小明', '2')