Four ways for MySQL to avoid repeatedly inserting data

Time:2022-5-5

The most common way is to set the primary key or unique index for the field. When inserting duplicate data, an error will be thrown and the program will terminate, but this will bring trouble to the subsequent processing. Therefore, special processing needs to be done for the insertion statement to avoid or ignore exceptions as much as possible. Here is a brief introduction for interested friends to try:

In order to facilitate the demonstration, I have created a user test table, which mainly includes four fields: ID, username, sex and address. The primary key is ID (self increment), and a unique index is set for the username field:

01 insert ignore into

That is, when inserting data, if the data exists, the insertion will be ignored. The precondition is that the inserted data field is set with a primary key or unique index. The test SQL statement is as follows. When inserting this data, MySQL database will first retrieve the existing data (i.e. idx_username index). If it exists, the insertion will be ignored. If it does not exist, the data will be inserted normally:

02 on duplicate key update

That is, when inserting data, if the data exists, the update operation will be executed. The precondition is the same as above. The primary key or unique index is set for the inserted data field. The test SQL statement is as follows. When inserting this record, MySQL database will first retrieve the existing data (idx_username index). If it exists, the update operation will be executed. If it does not exist, it will be inserted directly:

03 replace into

That is, when inserting data, if the data exists, delete it and then insert it. The preconditions are the same as above. The inserted data field needs to set a primary key or unique index. The test SQL statement is as follows. When inserting this record, MySQL database will first retrieve the existing data (idx_username index). If it exists, delete the old data first and then insert it. If it does not exist, insert it directly:

04 insert if not exists

Insert into… Select… Where not exist, This method is suitable for inserting data fields without primary key or unique index. When inserting a piece of data, first judge whether the data exists in MySQL database. If it does not exist, insert it normally. If it does, ignore:

At present, let’s share these four methods of MySQL processing duplicate data. The first three methods are suitable for setting primary keys or unique indexes for fields, while the last method has no such restriction. As long as you are familiar with the use process, you can master it soon. There are also relevant materials and tutorials on the Internet. The introduction is very detailed. If you are interested, you can search it

This is the end of this article on four ways of avoiding repeated data insertion in MySQL. For more information about avoiding repeated data insertion in mysql, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you can support developeppaer in the future!