乐趣区

关于mysql:mysql数据重复新增的几种解决方案

在 MySQL 数据库中,如果在 insert 语句前面带上 ON DUPLICATE KEY UPDATE 子句,而要插入的行与表中现有记录的惟一索引或主键中产生反复值,那么就会产生旧行的更新;如果插入的行数据与现有表中记录的惟一索引或者主键不反复,则执行新纪录插入操作。另外,ON DUPLICATE KEY UPDATE 不能写 where 条件。

create table kid_score(
id tinyint unsigned not null,
birth_day date not null,
score int unsigned not null,
primary key(id, birth_day)  -- 惟一索引是由 id + birth_day 两个字段组成
) engine = InnoDB;

-- 初始化数据
insert into kid_score(id, birth_day, score) values (1,'2019-01-15',10),(2,'2019-01-16',20);

insert into kid_score(id, birth_day, score) values (1,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;

分惟一索引反复、惟一索引不反复的状况。
惟一索引反复,那就是更新操作。
惟一索引不反复,就是插入操作。

须要留神的是:如果行作为新记录被插入,则受影响行的值为 1;如果原有的记录被更新,则受影响行的值为 2,如果更新的数据和已有的数据截然不同,则受影响的行数是 0。

mysql> select * from kid_score;

+----+------------+-------+
| id | birth_day  | score |
+----+------------+-------+
|  1 | 2019-01-15 |    10 |
|  2 | 2019-01-16 |    20 |
+----+------------+-------+
2 rows in set

-- 惟一索引反复,执行更新
mysql> insert into kid_score(id, birth_day, score) values (1,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;
Query OK, 2 rows affected

mysql> select * from kid_score;
+----+------------+-------+
| id | birth_day  | score |
+----+------------+-------+
|  1 | 2019-01-15 |    60 |
|  2 | 2019-01-16 |    20 |
+----+------------+-------+
2 rows in set

-- 惟一索引不反复,执行插入
mysql> insert into kid_score(id, birth_day, score) values (2,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;

Query OK, 1 row affected

mysql> select * from kid_score;
+----+------------+-------+
| id | birth_day  | score |
+----+------------+-------+
|  1 | 2019-01-15 |    60 |
|  2 | 2019-01-15 |    30 |
|  2 | 2019-01-16 |    20 |
+----+------------+-------+
3 rows in set

-- 惟一索引反复,应该执行更新,但更新值与原值雷同
mysql> insert into kid_score(id, birth_day, score) values (2,'2019-01-16',20) ON DUPLICATE KEY UPDATE score = 20;

Query OK, 0 rows affected

mysql> select * from kid_score;
+----+------------+-------+
| id | birth_day  | score |
+----+------------+-------+
|  1 | 2019-01-15 |    60 |
|  2 | 2019-01-15 |    30 |
|  2 | 2019-01-16 |    20 |
+----+------------+-------+
3 rows in set

最常见的形式就是为字段设置主键或惟一索引,当插入反复数据时,抛出谬误,程序终止,但这会给后续解决带来麻烦,因而须要对插入语句做非凡解决,尽量避开或疏忽异样,上面我简略介绍一下,感兴趣的敌人能够尝试一下:

这里为了不便演示,我新建了一个 user 测试表,次要有 id,username,sex,address 这 4 个字段,其中主键为 id(自增),同时对 username 字段设置了惟一索引:

01. insert ignore into

即插入数据时,如果数据存在,则疏忽此次插入,前提条件是插入的数据字段设置了主键或惟一索引,测试 SQL 语句如下,当插入本条数据时,MySQL 数据库会首先检索已有数据(也就是 idx_username 索引),如果存在,则疏忽本次插入,如果不存在,则失常插入数据:
在这里插入图片形容

02. on duplicate key update

即插入数据时,如果数据存在,则执行更新操作,前提条件同上,也是插入的数据字段设置了主键或惟一索引,测试 SQL 语句如下,当插入本条记录时,MySQL 数据库会首先检索已有数据(idx_username 索引),如果存在,则执行 update 更新操作,如果不存在,则直接插入:
在这里插入图片形容

03. replace into

即插入数据时,如果数据存在,则删除再插入,前提条件同上,插入的数据字段须要设置主键或惟一索引,测试 SQL 语句如下,当插入本条记录时,MySQL 数据库会首先检索已有数据(idx_username 索引),如果存在,则先删除旧数据,而后再插入,如果不存在,则直接插入:
在这里插入图片形容

04. insert if not exists

即 insert into … select … where not exist …,这种形式适宜于插入的数据字段没有设置主键或惟一索引,当插入一条数据时,首先判断 MySQL 数据库中是否存在这条数据,如果不存在,则失常插入,如果存在,则疏忽:
在这里插入图片形容

目前,就分享这 4 种 MySQL 解决反复数据的形式吧,前 3 种形式适宜字段设置了主键或惟一索引,最初一种形式则没有此限度,只有你相熟一下应用过程,很快就能把握的,网上也有相干材料和教程,介绍的十分具体,感兴趣的话,能够搜一下。

退出移动版