Preface In the case of primary key conflict or unique key conflict in MySql, there are generally three insertion methods to avoid errors depending on the insertion method.
insert ignore insert ignore will ignore the data that already exists in the database. It will insert new data if there is no data in the database based on the primary key or unique index. If there is data, it will skip this data. Small case Table Structure 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) Insert conflicting data 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 View Results 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 will try to insert the data first, and delete it if a conflict is found. Otherwise, do nothing. Small 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) Insert conflicting data 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) You can see that the original record is gone and a new record has appeared. insert on duplicate key update If insert on duplicate key update is specified at the end of the insert into statement, if duplicate values appear, update will be performed after the duplicate values appear. 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) Insert a data that conflicts with the unique key (column c1) of record id=3 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) It can be seen that the record with id=3 has changed, c1=original c1+3, and other columns have not changed. The above are the details of four MySql methods to avoid duplicate data insertion. For more information about MySQL to avoid inserting duplicate data, please pay attention to other related articles on 123WORDPRESS.COM! You may also be interested in:
|
<<: Distinguishing between Linux hard links and soft links
>>: A brief discussion on the perfect adaptation solution for Vue mobile terminal
This article records the detailed tutorial of MyS...
MySQL-Group-Replication is a new feature develope...
1. Execute the select statement first to generate...
1. InnoDB locking mechanism The InnoDB storage en...
Table of contents Overview Form validation withou...
During the development process, I often encounter...
After the article "This Will Be a Revolution&...
Table of contents 1. Setup 1. The first parameter...
This article shares the specific code of jquery t...
Table of contents 1. Container lifecycle manageme...
Two-column layout is often used in projects. Ther...
Last time we talked about some SQL query optimiza...
Three ways to set borders in HTML border-width: 1...
Table of contents 1. mixin.scss 2. Single file us...
I have been studying how to get https. Recently I...