Mysql ON DUPLICATE KEY UPDATE 用法与说明

先声明一点,ON DUPLICATE KEY UPDATE为Mysql特有语法,语句的作用是当insert已经存在的记录时,执行Update。

用法

什么意思?举个例子:
user_admin_t表中有一条数据如下

表中的主键为id,现要插入一条数据,id为1,password为第一次插入的密码,正常写法为:

 	INSERT INTO user_admin_t (_id,password) 
 	VALUES ('1','第一次插入的密码')

执行后刷新表数据,我们来看表中内容

此时表中数据增加了一条主键_id为1,password为第一次插入的密码的记录,当我们再次执行插入语句时,会发生什么呢?

 	-- 执行
 	INSERT INTO user_admin_t (_id,password) 
 	VALUES ('1','第一次插入的密码')
 	[SQL]INSERT INTO user_admin_t (_id,password) 
 	VALUES ('1','第一次插入的密码') 
 	
 	[Err] 1062 - Duplicate entry '1' for key 'PRIMARY'

Mysql告诉我们,我们的主键冲突了,看到这里我们是不是可以改变一下思路,当插入已存在主键的记录时,将插入操作变为修改:

 	-- 在原sql后面增加 ON DUPLICATE KEY UPDATE 
 	INSERT INTO user_admin_t (_id,password) 
 	VALUES ('1','第一次插入的密码') 
 	ON DUPLICATE KEY UPDATE 
 	_id = 'UpId',
 	password = 'upPassword';

我们再一次执行:

 	[SQL]INSERT INTO user_admin_t (_id,password) 
 	VALUES ('1','第一次插入的密码') 
 	ON DUPLICATE KEY UPDATE 
 	_id = 'UpId',
 	password = 'upPassword';
 	受影响的行: 2
 	时间: 0.131s

可以看到 受影响的行为2,这是因为将原有的记录修改了,而不是执行插入,看一下表中数据:

原本id为1的记录,改为了UpId,password也变为了upPassword,很好的解决了重复插入问题

扩展

当插入多条数据,其中不只有表中已存在的,还有需要新插入的数据,Mysql会如何执行呢?会不会报错呢?

其实Mysql远比我们想象的强大,他会智能的选择更新还是插入,我们尝试一下:

 	INSERT INTO user_admin_t (_id,password) 
 	VALUES 
 	('1','第一次插入的密码') ,
 	('2','第二条记录')
 	ON DUPLICATE KEY UPDATE 
 	_id = 'UpId',
 	password = 'upPassword';
 	[SQL]INSERT INTO user_admin_t (_id,password) 
 	VALUES 
 	('1','第一次插入的密码') ,
 	('2','第二条记录')
 	ON DUPLICATE KEY UPDATE 
 	_id = 'UpId',
 	password = 'upPassword';
 	受影响的行: 3
 	时间: 0.045s

Mysql执行了一次修改,一次插入,表中数据为:

VALUES修改

那么问题又来了,有人会说我ON DUPLICATE KEY UPDATE 后面跟的是固定的值,如果我想要分别给不同的记录插入不同的值怎么办呢?

 	INSERT INTO user_admin_t (_id,password) 
 	VALUES 
 	('1','多条插入1') ,
 	('UpId','多条插入2')
 	ON DUPLICATE KEY UPDATE 
 	password =  VALUES(password);

方法之一可以将后面的修改条件改为VALUES(password),动态的传入要修改的值,执行以下:

 	[SQL]INSERT INTO user_admin_t (_id,password) 
 	VALUES 
 	('1','多条插入1') ,
 	('UpId','多条插入2')
 	ON DUPLICATE KEY UPDATE 
 	password =  VALUES(password);
 	受影响的行: 4
 	时间: 0.187s

成功的修改了两条记录,刷新一下表

我们成功的为不同id的password修改成了不同的值

总结

其实修改的方法有很多种,包括SET或用REPLACE,连事务都省的做,ON DUPLICATE KEY UPDATE能够让我们便捷的完成重复插入的开发需求,但它是Mysql的特有语法,使用时应多注意主键和插入值是否是我们想要插入或修改的key、Value。