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')