mysql技巧：如果记录存在则更新/如果不存在则插入的三种处理方法
【摘要】 先建一个表，便于后面讨论：12345678910CREATE 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 '' COMME...
先建一个表，便于后面讨论：
1 2 3 4 5 6 7 8 9 10 | 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，更新时，不得低于原工资（即：工资只能涨，不能降）。
方法一：传统方法
插入
1 2 3 4 5 6 7 8 9 10 11 12 | 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'); |
更新：
1 2 3 | 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
1 2 3 4 5 6 7 8 9 10 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）