Mysql,replace into,存在则更新,不存在则插入

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

 

注意：

1. 根据主键或唯一索引判断记录是否已存在，所以插入数据的表必须要有主键或者唯一索引！否则的话，REPLACE INTO 会直接插入数据（相当于INSERT），会导致表中出现重复数据。

2. 如果不写某个字段的值则会使用默认值，如果该字段没有定义默认值则报错。

3. 要使用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 t_user change id id int(11) not null AUTO_INCREMENT; 
-- 修改主键为自增

-- 修改后的DDL
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. 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  男

 

2. 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  女

 

3. 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的记录