mysql技巧:如果记录存在则更新/如果不存在则插入的三种处理方法

先建一个表,便于后面讨论:

CREATE TABLE `t_emp` (
  `f_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `f_emp_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '员工号',
  `f_emp_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '员工姓名',
  `f_city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '所属城市',
  `f_salary` int(11) DEFAULT '1200' COMMENT '工资',
  `f_last_update_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '最后修改时间',
  PRIMARY KEY (`f_id`),
  UNIQUE KEY `idx_emp_code` (`f_emp_code`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='员工表'

插入几条数据:

要求:

新增一个员工时,如果该员工已存在(以员工号f_emp_code作为判断依据),则更新,否则插入。而且工资f_salary,更新时,不得低于原工资(即:工资只能涨,不能降)。

 

方法一:传统方法

插入

INSERT INTO t_emp(
	f_emp_code ,
	f_emp_name ,
	f_city ,
	f_salary
) SELECT '10007' ,
	 '新人' ,
	 '西安' ,
	 1000 
	FROM DUAL WHERE NOT EXISTS(
	SELECT * FROM t_emp WHERE f_emp_code = '10007'
);

 

更新: 

UPDATE t_emp SET f_emp_name = '新人2' ,
 f_city = '西安' ,
 f_salary = IF(1000 > f_salary , 1000 , f_salary) WHERE f_emp_code = '10007'

缺点就是得写2条语句,分别处理插入和更新的场景。

 

方法二:replace into

REPLACE INTO t_emp(
	f_emp_code ,
	f_emp_name ,
	f_city ,
	f_salary
) VALUES(
	'10007' ,
	'新人' ,
	'西安' ,
	IF(1000 > f_salary , 1000 , f_salary));

replace into相当于,先检测该记录是否存在(根据表上的唯一键),如果存在,先delete,然后再insert。 这个方法有一个很大的问题,如果记录存在,每次执行完,主键自增id就变了(相当于重新insert了一条),对于有复杂关联的业务场景,如果主表的id变了,其它子表没做好同步,会死得很难看。-- 不建议使用该方法!

 

方法三:on duplicate key

INSERT INTO t_emp(
	f_emp_code ,
	f_emp_name ,
	f_city ,
	f_salary)
VALUES(
	'10007' ,
	'新人' ,
	'西安' ,
	1000) 
ON DUPLICATE KEY UPDATE 
	f_emp_code = values(f_emp_code) ,
	f_emp_name = values(f_emp_name),
	f_city = values(f_city),
	f_salary = if(values(f_salary)>f_salary,values(f_salary),f_salary);

注意上面的on duplicate key,遇到重复键(即:违反了唯一约束),这时会做update,否则做insert。该方法,没有replace into的副作用,不会导致已存在记录的自增id变化。但是有另外一个问题,如果这个表上有不止一个唯一约束,在特定版本的mysql中容易产生dead lock(死锁),见网友文章https://blog.csdn.net/pml18710973036/article/details/78452688

posted @ 2018-12-02 14:56 菩提树下的杨过 阅读(...) 评论(...) 编辑 收藏