MySQL replace into 和insert into on duplicate key update用法

replace into 用法

在向表中插入数据的时候，经常遇到这样的情况：
1. 首先判断数据是否存在；
2. 如果不存在，则插入；
3. 如果存在，则更新。

在 SQL Server 中可以这样处理：

if not exists (select 1 from t where id = 1)
      insert into t(id, update_time) values(1, getdate())
   else
      update t set update_time = getdate() where id = 1

那么 MySQL 中如何实现这样的逻辑呢？别着急！MySQL 中有更简单的方法： replace into

replace into t(id, update_time) values(1, now());

或

replace into t(id, update_time) select 1, now();

replace into 跟 insert 功能类似，不同点在于：
replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。

注意：插入数据的表必须有主键或者是唯一索引否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

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, ...

前两种形式用的多些。其中 “into” 关键字可以省略，不过最好加上 “into”，这样意思更加直观。另外，对于那些没有给予值的列，MySQL 将自动为这些列赋上默认值。

insert on duplcate key update 用法

replace into和on duplcate key update都是只有在primary key或者unique key冲突的时候才会执行。
如果数据存在，replace into则会将原有数据删除，再进行插入操作，这样就会有一种情况，如果某些字段有默认值，但是replace into语句的字段不完整，则会设置成默认值。
而on duplicate key update则是执行update后面的语句。
下面我们来看一个例子来说明。

CREATE TABLE mytest(
	id INT NOT NULL AUTO_INCREMENT,
	username VARCHAR(32) NOT NULL,
	password VARCHAR(32) DEFAULT NULL,
	PRIMARY KEY(id),
	UNIQUE KEY(col1)
);

插入一条测试数据：

INSERT INTO mytest VALUES (NULL, 'admin', '123456');

执行replace into：

replace into test(username) values ('unique');

重点： 可以看到id的值增加了 1，并且 password 的值变为了NUll，说明在出现冲突的情况下，使用replace into只是先delete掉原来的数据，再执行insert操作，如果新的insert语句不完成，会将其余字段设置为默认值。

现在我们将数据还原：

UPDATE mytest SET PASSWORD = '123456' WHERE username = 'admin';

执行on duplicate key update：

insert into mytest values(null, 'admin', '654321') on duplicate key update username = 'update_admin';

可以看到 id 没有变化，password 的值也没有变化，只有username 的值发生了变化，说明在出现冲突的情况下，on duplicate key update只是执行了update后面的语句。