MySQL一条语句当某记录不存在的时候(UniqueKey)才执行插入操作的多种实现

2021-07-19 42点热度 0人点赞 0条评论

需求

MySQL插入数据的时候,如果遇到UniqueKey(唯一键)已经存在的情况下,通常情况下按报错处理,所以想要的结果是,只有当UK不存在的情况下才执行插入操作。

实现

假设这样一张表t_student学生表,stu_no是学生编号,是唯一值:

mysql> ALTER TABLE t_student ADD UNIQUE INDEX stu_no_uk1(stu_no);

mysql> desc t_student;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | NO   | PRI | NULL    |       |
| create_date | datetime     | YES  |     | NULL    |       |
| update_date | datetime     | YES  |     | NULL    |       |
| stu_no      | varchar(36)  | NO   | UNI | NULL    |       |
| stu_name    | varchar(255) | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

mysql> select * from t_student;
+----------+---------------------+---------------------+--------+--------------+------+
| id       | create_date         | update_date         | stu_no      | stu_name | no  |
+----------+---------------------+---------------------+--------+--------------+------+
| 1        | 2020-10-27 08:41:29 | 2020-10-27 09:07:08 | A001        | Tony     |   7 |
+----------+---------------------+---------------------+--------+--------------+------+
  • 最简单能想到的方式,就是分2条SQL语句(如果是程序,就分两次判断),先执行select查询到是否存在这个UK,只有不存在的时候,才执行insert,这种方式就省略不说了。
  • 使用INSERT IGNORE,就是插入的时候,如果遇到唯一值约束问题,也不报错
-- 原来是insert into,插入stu_no是A001的记录会报错 1062 - Duplicate entry 'stu_no' for key 't_student.stu_no_uk1', Time: 0.003000s
INSERT INTO t_student(`id`, `create_date`, `update_date`, `stu_no`, `stu_name`) VALUES (2, '2021-10-27 08:41:29', NULL, 'A001', 'Tony');

-- 修改为insert ignore into 就可以了
INSER IGNORE INTO t_student(`id`, `create_date`, `update_date`, `stu_no`, `stu_name`) VALUES (2, '2021-10-27 08:41:29', NULL, 'A001', 'Tony');
  • 使用REPLACE,replace顾名思义,就是替换,当记录已经存在的时候,删掉并替换掉旧的,如果不存在,则新增一条记录,总之都会新增记录
REPLACE INTO t_student(`id`, `create_date`, `update_date`, `stu_no`, `stu_name`) VALUES (2, '2021-10-27 08:41:29', NULL, 'A001', 'Tony');
-- Affected rows: 2, Time: 0.009000s
-- 如果已存在UK的记录,会看到影响到的记录是2条,就是一条记录删除,再新增
  • 使用INSERT ... ON DUPLICATE KEY UPDATE,意思是Key(这里的key亲测,可以是PrimaryKey,也可以是UniqueKey)不存在的时候,执行insert,如果存在就更新某个或者某些字段
-- 使用INSERT INTO ... ON DUPLICATE KEY UPDATE
INSERT INTO t_student(`id`, `create_date`, `update_date`, `stu_no`, `stu_name`) VALUES (2, '2021-07-19 08:41:29', NULL, 'A001', 'Tony') ON DUPLICATE KEY UPDATE update_date='2021-07-19 08:41:29', name='Tony';

这样就实现了只有key存在才insert,否则update的效果,这是一般场景下,比较推荐的做法。

admin

这个人很懒,什么都没留下

文章评论

*

code