详解MySQL主键唯一键重复插入解决方法
作者:SunnyYoona 时间:2024-01-20 16:41:22
目录
解决方案:
1. IGNORE
2. REPLACE
3. ON DUPLICATE KEY UPDATE
我们插入数据的时候,有可能碰到重复数据插入的问题,但是这些数据又是不被允许有重复值:
CREATE TABLE stuInfo (
id INT NOT NULL COMMENT '序号',
name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
PRIMARY KEY (id),
UNIQUE KEY uniq_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';
mysql> INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25),(2,'aa',24);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
解决方案:
1. IGNORE
使用ignore当插入的值遇到主键(PRIMARY KEY)或者唯一键(UNIQUE KEY)重复时自动忽略重复的记录行,不影响后面的记录行的插入。
INSERT IGNORE INTO stuInfo (name,birthday,is_deleted) VALUES ('yoona','1990-01-05',0),('aa','1990-01-16',0),('bb','1990-01-17',0);
运行结果:
mysql> INSERT IGNORE INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25),(2,'aa',24);
Query OK, 2 rows affected (0.02 sec)
Records: 3 Duplicates: 1 Warnings: 0
mysql> select * from stuInfo;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | yoona | 20 |
| 2 | aa | 24 |
+----+-------+-----+
2 rows in set (0.00 sec)
我们可以从运行结果中看出,只有两行受到影响,意思即(1,'yoona',20)数据插入,(1,'xiaosi',25)重复数据自动被忽略,(2,'aa',24)不重复数据继续插入,不会受到重复数据的影响;
2. REPLACE
使用replace当插入的记录遇到主键或者唯一键重复时先删除表中重复的记录行再插入。
mysql> REPLACE INTO stuInfo (name,birthday,is_deleted) VALUES ('yoona','1990-01-15',0),('yoona','1990-02-16',0),('aa','1990-01-13',0);
Query OK, 4 rows affected (0.02 sec)
Records: 3 Duplicates: 1 Warnings: 0
运行结果:
mysql> select * from stuInfo; +----+-------+------------+------------+
| id | name | birthday | is_deleted |
+----+-------+------------+------------+
| 21 | yoona | 1990-02-16 | 0 |
| 22 | aa | 1990-01-13 | 0 |
+----+-------+------------+------------+
2 rows in set (0.00 sec)
从输出的信息可以看到是4行受影响,说明它是先插入了(‘yoona','1990-01-15',0)然后又删除了(‘yoona','1990-01-15',0)。
3. ON DUPLICATE KEY UPDATE
当插入的记录遇到主键或者唯一键重复时,会执行后面定义的UPDATE操作。相当于先执行Insert 操作,再根据主键或者唯一键执行update操作。
DROP TABLE IF EXISTS stuInfo;
CREATE TABLE stuInfo (
id INT NOT NULL COMMENT '序号',
name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
PRIMARY KEY (id),
UNIQUE KEY uniq_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';
在ON DUPLICATE KEY UPDATE后VALUES解释:
VAULES(age)指的是待插入的记录的值
age指得是表的自身值,已插入值。
(1)第一种情形:
#VALUES(age) 待插入值 25
INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25) ON DUPLICATE KEY UPDATE age = VALUES(age) + 1;
相当于:
INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20);
UPDATE stuInfo
SET age = VALUES(age) + 1
WHERE id = 1;
运行结果:
mysql> INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25) ON DUPLICATE KEY UPDATE age = VALUES(age) + 1;
Query OK, 3 rows affected (0.01 sec)
Records: 2 Duplicates: 1 Warnings: 0
mysql> select * from stuInfo;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | yoona | 26 |
+----+-------+-----+
1 row in set (0.00 sec)
(2)第二种情形:
#age 已插入值 20
INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25) ON DUPLICATE KEY UPDATE age = age + 1;
相当于:
INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20);
UPDATE stuInfo
SET age = age + 1
WHERE id = 1;
运行结果:
mysql> INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25) ON DUPLICATE KEY UPDATE age = age + 1;
Query OK, 3 rows affected (0.02 sec)
Records: 2 Duplicates: 1 Warnings: 0
mysql> select * from stuInfo;
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 1 | yoona | 21 |
+----+-------+-----+
1 row in set (0.00 sec)
如果遇到重复插入的数据的情形,ON DUPLICATE KEY UPDATE用来对已插入的数据进行修改,可以使用获取重复已插入数据(直接使用字段名称),也可以获取重复待插入数据(values(字段名称))。我们不会对重复待插入数据进行插入操作。
重复已插入数据:上例中的(1,'yoona',20)
重复待插入数据:上例中的(1,'yoona',25)
来源:https://blog.csdn.net/SunnyYoona/article/details/51346593
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Python制作微信好友背景墙教程(附完整代码)
![](https://img.aspxhome.com/file/2023/3/114993_0s.jpg)
Python 阶乘详解
![](https://img.aspxhome.com/file/2023/1/66871_0s.png)
一小时快速入门Python教程
Python3实现的判断回文链表算法示例
javascript 跳转代码集合
Golang详细讲解常用Http库及Gin框架的应用
Python的进程间通信详解
![](https://img.aspxhome.com/file/2023/8/127688_0s.jpg)
MySQL 5.0.16乱码问题的解决方法
Python爬虫实现抓取京东店铺信息及下载图片功能示例
windows系统下让mysql支持federated的storage engine
Python实现多行注释的另类方法
解决mysql ERROR 1045 (28000)-- Access denied for user问题
![](https://img.aspxhome.com/file/2023/1/121271_0s.png)
Django实现在线无水印抖音视频下载(附源码及地址)
![](https://img.aspxhome.com/file/2023/7/90597_0s.jpg)
如何利用Python连接MySQL数据库实现数据储存
![](https://img.aspxhome.com/file/2023/9/80789_0s.jpg)
JS中的public和private对象,即static修饰符
python实现雪花飘落效果实例讲解
![](https://img.aspxhome.com/file/2023/7/110397_0s.png)
Vue.js鼠标悬浮更换图片功能
![](https://img.aspxhome.com/file/2023/4/130084_0s.jpg)
SQL Data Services将成为云中完整的数据库
SQL Server数据类型转换方法
![](https://img.aspxhome.com/file/2023/6/124846_0s.jpg)