Three ways to prevent MySQL from inserting duplicate data

Create a new table

CREATE TABLE `person` (
 `id` int NOT NULL COMMENT 'Primary key',
 `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT 'Name',
 `age` int NULL DEFAULT NULL COMMENT 'Age',
 `address` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT 'Address',
 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

Add three pieces of data as follows:

We can avoid it according to the insertion method:

1. insert ignore

insert ignore will automatically ignore the data that already exists in the database (based on the primary key or unique index). If there is no data, insert the data; if there is data, skip inserting this data.

--Insert SQL as follows:
insert ignore into person (id,name,age,address) values(3,'那谁',23,'赣苏省'),(4,'我天',25,'浙江省');

If you check the database again, you will find that only data with id 4 is inserted. Since data with id 3 already exists in the database, it is ignored.

2. replace into

replace into first tries to insert data into the table. 1. If it is found that this row of data already exists in the table (determined by the primary key or unique index), delete this row of data first, and then insert the new data. 2. Otherwise, insert the new data directly.

--Insert SQL as follows:
replace into person (id,name,age,address) values(3,'那谁',23,'赣苏省'),(4,'我天',25,'浙江省');

First, we restore the data in the table, and then after the insert operation, we find that the data with id 3 has changed and the data with id 4 has been added.

3. insert on duplicate key update

insert on duplicate key update If on duplicate key update + field update is specified at the end of the insert into statement, the information will be updated according to the description of the subsequent field update when duplicate data appears (determined by the primary key or unique index).

--Insert SQL as follows:
insert into person (id,name,age,address) values(3,'那谁',23,'赣苏省') on duplicate key update name='那谁', age=23, address='赣苏省';

First, we restored the data in the table, and then when performing the insert operation, we found that the data with id 3 had changed, so we performed an update operation.

We can choose the method according to our business needs.

The above are the details of three methods to prevent MySQL from inserting data repeatedly. For more information on preventing MySQL from inserting data repeatedly, please pay attention to other related articles on 123WORDPRESS.COM!