MySql三种避免重复插入数据的方法

前言 MySql 在存在主键冲突或唯一键冲突的情况下，根据插入方式，一般有以下三种插入方式避免错误。 insert ignore。 replace into insert on duplicate key update insert ignore insert ignore 会忽视数据库

前言

MySql 在存在主键冲突或唯一键冲突的情况下，根据插入方式，一般有以下三种插入方式避免错误。

insert ignore。
replace into
insert on duplicate key update

insert ignore

insert ignore 会忽视数据库中已经存在的数据，根据主键或者唯一索引判断，如果数据库没有数据，就会插入新的数据，如果有数据的话就跳过这条数据

小case

表结构

root:test> show create table t3G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root:test> select * from t3;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | a | 1 |
| 2 | 2 | a | 1 |
| 8 | NULL | NULL | 1 |
| 14 | 4 | bb | NULL |
| 17 | 5 | cc | 4 |
+----+------+------+------+
5 rows in set (0.00 sec)

插入冲突数据

root:test> insert ignore into t3 (c1,c2,c3) values(5,'cc',4),(6,'dd',5); Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 2 Duplicates: 1 Warnings: 1

查看结果

root:test> show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '5' for key 'uidx_c1' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

root:test> select * from t3;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | a | 1 |
| 2 | 2 | a | 1 |
| 8 | NULL | NULL | 1 |
| 14 | 4 | bb | NULL |
| 17 | 5 | cc | 4 |
| 18 | 6 | dd | 5 |
+----+------+------+------+
6 rows in set (0.00 sec)

replace into

replace into 会尝试先插入数据，如果发现冲突进行删除。否则不做任何操作。

小case

root:test> show create table t3G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root:test> select * from t3;
+----+------+--------+------+
| id | c1 | c2 | c3 |
+----+------+--------+------+
| 1 | 1 | cc | 4 |
| 2 | 2 | dd | 5 |
| 3 | 3 | qwewqe | 3 |
+----+------+--------+------+
3 rows in set (0.00 sec)

插入冲突数据

root:test> replace into t3 (c1,c2,c3) values(3,'new',8);
Query OK, 2 rows affected (0.02 sec)

root:test> select * from t3;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | cc | 4 |
| 2 | 2 | dd | 5 |
| 4 | 3 | new | 8 |
+----+------+------+------+
3 rows in set (0.00 sec)

可以看到原有的记录已经没有了，新的记录又有了。

insert on duplicate key update

如果在insert into 语句末尾指定了 insert on duplicate key update 如果出现了重复值，则会在出现重复值以后进行update。

case

root:test> show create table t3G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uidx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

root:test> select * from t3;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | fds | 4 |
| 2 | 2 | ytu | 3 |
| 3 | 3 | czx | 5 |
+----+------+------+------+
3 rows in set (0.00 sec)

插入一条与记录id=3存在唯一键（列c1）冲突的数据

root:test> insert into t3(c1,c2,c3) values (3,'new',5) on duplicate key update c1=c1+3;
Query OK, 2 rows affected (0.01 sec)

root:test> select * from t3;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | 1 | fds | 4 |
| 2 | 2 | ytu | 3 |
| 3 | 6 | czx | 5 |
+----+------+------+------+
3 rows in set (0.00 sec)

可以看到，id=3的记录发生了改变，c1=原有的c1+3,其他列没有改变。

以上就是MySql四种避免重复插入数据的方法的详细内容，更多关于MySQL 避免插入重复数据的资料请关注每日运维其它相关文章！