[MySQL日记]主键唯一键重复插入解决方法

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/SunnyYoona/article/details/51346593 我们插...
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/SunnyYoona/article/details/51346593
我们插入数据的时候,有可能碰到重复数据插入的问题,但是这些数据又是不被允许有重复值:

 
  
  1. CREATE TABLE stuInfo (
  2. id INT NOT NULL COMMENT '序号',
  3. name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',
  4. age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  5. PRIMARY KEY (id),
  6. UNIQUE KEY uniq_name(name)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';

 
  
  1. mysql> INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25),(2,'aa',24);
  2. ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

解决方案:

1. IGNORE

使用ignore当插入的值遇到主键(PRIMARY KEY)或者唯一键(UNIQUE KEY)重复时自动忽略重复的记录行,不影响后面的记录行的插入。

 
 
  1. INSERT IGNORE INTO stuInfo (name,birthday,is_deleted) VALUES ('yoona','1990-01-05',0),('aa','1990-01-16',0),('bb','1990-01-17',0);
运行结果:
 
  
  1. mysql> INSERT IGNORE INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25),(2,'aa',24);
  2. Query OK, 2 rows affected (0.02 sec)
  3. Records: 3 Duplicates: 1 Warnings: 0
  4. mysql> select * from stuInfo;
  5. +----+-------+-----+
  6. | id | name | age |
  7. +----+-------+-----+
  8. | 1 | yoona | 20 |
  9. | 2 | aa | 24 |
  10. +----+-------+-----+
  11. 2 rows in set (0.00 sec)

我们可以从运行结果中看出,只有两行受到影响,意思即(1,'yoona',20)数据插入,(1,'xiaosi',25)重复数据自动被忽略,(2,'aa',24)不重复数据继续插入,不会受到重复数据的影响;

2. REPLACE

使用replace当插入的记录遇到主键或者唯一键重复时先删除表中重复的记录行再插入。

 
 
  1. mysql> REPLACE INTO stuInfo (name,birthday,is_deleted) VALUES ('yoona','1990-01-15',0),('yoona','1990-02-16',0),('aa','1990-01-13',0);
  2. Query OK, 4 rows affected (0.02 sec)
  3. Records: 3  Duplicates: 1  Warnings: 0

运行结果:

 
 
  1. mysql> select * from stuInfo;                                                   +----+-------+------------+------------+
  2. | id | name  | birthday   | is_deleted |
  3. +----+-------+------------+------------+
  4. | 21 | yoona | 1990-02-16 |          0 |
  5. | 22 | aa    | 1990-01-13 |          0 |
  6. +----+-------+------------+------------+
  7. 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操作

 
 
  1. DROP TABLE  IF EXISTS stuInfo;
  2. CREATE TABLE stuInfo (
  3.  id INT NOT NULL COMMENT '序号',
  4.  name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',
  5.  age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  6.  PRIMARY KEY (id),
  7.  UNIQUE KEY uniq_name(name)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';


在ON DUPLICATE KEY UPDATE后VALUES解释:

VAULES(age)指的是待插入的记录的值

age指得是表的自身值,已插入值。

(1)第一种情形:

 
 
  1. #VALUES(age) 待插入值 25
  2. INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25) ON DUPLICATE KEY UPDATE age = VALUES(age) + 1;

相当于:

 
 
  1. INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20);
  2. UPDATE stuInfo
  3. SET age = VALUES(age) + 1
  4. WHERE id = 1;

运行结果:

 
  
  1. mysql> INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25) ON DUPLICATE KEY UPDATE age = VALUES(age) + 1;
  2. Query OK, 3 rows affected (0.01 sec)
  3. Records: 2 Duplicates: 1 Warnings: 0
  4. mysql> select * from stuInfo;
  5. +----+-------+-----+
  6. | id | name | age |
  7. +----+-------+-----+
  8. | 1 | yoona | 26 |
  9. +----+-------+-----+
  10. 1 row in set (0.00 sec)


(2)第二种情形:

 
 
  1. #age 已插入值 20
  2. INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25) ON DUPLICATE KEY UPDATE age = age + 1;

相当于:

 
 
  1. INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20);
  2. UPDATE stuInfo
  3. SET age = age + 1
  4. WHERE id = 1;

运行结果:

 
  
  1. mysql> INSERT INTO stuInfo (id,name,age) VALUES (1,'yoona',20),(1,'xiaosi',25) ON DUPLICATE KEY UPDATE age = age + 1;
  2. Query OK, 3 rows affected (0.02 sec)
  3. Records: 2 Duplicates: 1 Warnings: 0
  4. mysql> select * from stuInfo;
  5. +----+-------+-----+
  6. | id | name | age |
  7. +----+-------+-----+
  8. | 1 | yoona | 21 |
  9. +----+-------+-----+
  10. 1 row in set (0.00 sec)

如果遇到重复插入的数据的情形,ON DUPLICATE KEY UPDATE用来对已插入的数据进行修改,可以使用获取重复已插入数据(直接使用字段名称),也可以获取重复待插入数据(values(字段名称))。我们不会对重复待插入数据进行插入操作。

重复已插入数据:上例中的(1,'yoona',20)

重复待插入数据:上例中的(1,'yoona',25)



相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
8天前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(4-2)作者——LJS[含MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法]
24MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法(4-2) 学不会你来砍我!!!
|
8天前
|
NoSQL 安全 关系型数据库
2024Mysql And Redis基础与进阶操作系列(6)作者——LJS[含MySQL 多表之一对一/多;多对多;多表联合查询等详解步骤及常见报错问题所对应的解决方法]
MySQL 多表之一对一/多;多对多;多表联合之交叉连接;内连接;左、右、外、满、连接;子查询及关键字;自连接查询等详解步骤及常见报错问题所对应的解决方法
|
8天前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(5)作者——LJS[含MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
|
8天前
|
SQL NoSQL 关系型数据库
|
8天前
|
存储 SQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(1)作者——LJS[含MySQL的下载、安装、配置详解步骤及报错对应解决方法]
Mysql And Redis基础与进阶操作系列(1)之[MySQL的下载、安装、配置详解步骤及报错对应解决方法]
|
8天前
|
SQL 关系型数据库 MySQL
|
7天前
|
存储 SQL NoSQL
|
5月前
|
关系型数据库 MySQL
MYSQL:约束(主键约束)
MYSQL:约束(主键约束)
|
7天前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(8)作者——LJS[含MySQL 创建、修改、跟新、重命名、删除视图等具体详步骤;注意点及常见报错问题所对应的解决方法]
MySQL 创建、修改、跟新、重命名、删除视图等具体详步骤;举例说明注意点及常见报错问题所对应的解决方法
|
8天前
|
SQL NoSQL 关系型数据库