mysql唯一性约束冲突_MySQL避免插入重复记录：唯一性约束

最新推荐文章于 2024-07-30 03:50:28 发布

胡格

最新推荐文章于 2024-07-30 03:50:28 发布

阅读量914

点赞数

文章标签： mysql唯一性约束冲突

本文链接：https://blog.csdn.net/weixin_32364911/article/details/113141820

版权

mysql在存在主键冲突或者唯一键冲突的情况下，根据插入策略不同，一般有以下三种避免方法。

1、insert ignore

2、replace into

3、insert on duplicate key update

注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用以上三个语句没有意义，与使用单纯的INSERT INTO相同。

一、insert ignore

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

Case：

表结构如下：

root:test> show create table t3\G

*************************** 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

如下，可以看到只插入了(6,'dd',5)这条，同时有一条warning提示有重复的值。

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)

重新查询表结构，发现虽然只增加了一条记录，但是AUTO_INCREMENT还是增加了2个(18变成20)

root:test> show create table t3\G

*************************** 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=20 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

二、replace into

replace into 首先尝试插入数据到表中。如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据，然后插入新的数据，否则，直接插入新数据。

使用replace into，你必须具有delete和insert权限

Case:

root:test> show create table t3\G

*************************** 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)

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

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)

可以看到原有id=3，c1=3的记录不见了，新增了一条id=4，c1=3的记录.

replace into语句执行完会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和,上面的例子中2 rows affected .

三、insert on duplicate key update

如果在insert into 语句末尾指定了on duplicate key update，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致重复的问题，则插入新行，跟普通的insert into一样。

使用insert into，你必须具有insert和update权限

如果有新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2；如果记录被更新前后值是一样的，则受影响行数的值显示0

Case:

root:test> show create table t3\G

*************************** 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,其他列没有改变。

INSERT...ON DUPLICATE KEY UPDATE产生deathlock

insert ... on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，

如果存在，在对该现有的行加上S(共享锁)锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，

然后对该记录加上X(排他锁)，最后进行update写入。

如果有两个事务并发的执行同样的语句，

那么就会产生death lock，如：

解决办法：

1、尽量对存在多个唯一键的table使用该语句

2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

结论：

这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。

insert ignore能忽略重复数据，只插入不重复的数据。

replace into和insert ... on duplicate key update，都是替换原有的重复数据，区别在于replace into是删除原有的行后，在插入新行，如有自增id，这个会造成自增id的改变；insert ... on duplicate key update在遇到重复行时，会直接更新原有的行，具体更新哪些字段怎么更新，取决于update后的语句。

参考来源:https://www.cnblogs.com/prayer21/p/6018864.html

参考来源：https://www.2cto.com/database/201711/695662.html