4 ways to avoid duplicate insertion of data in Mysql

The most common way is to set a primary key or unique index for the field. When inserting duplicate data, an error is thrown and the program terminates, but this will cause trouble for subsequent processing. Therefore, special processing is required for the insert statement to avoid or ignore exceptions as much as possible. I will briefly introduce it below. Interested friends can try it:

For the convenience of demonstration, I created a user test table with four fields: id, username, sex, and address. The primary key is id (auto-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 is ignored. The prerequisite is that the inserted data field has a primary key or unique index set. The test SQL statement is as follows. When inserting this data, the MySQL database will first search for existing data (that is, the idx_username index). If it exists, the insertion is ignored. If it does not exist, the data is inserted normally:

02 on duplicate key update

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

03 replace into

That is, when inserting data, if the data exists, delete it and then insert it. The prerequisite is the same as above. The inserted data field needs to set the primary key or unique index. The test SQL statement is as follows. When inserting this record, the 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

That is, insert into ... select ... where not exist .... This method is suitable for inserting data fields that do not have a primary key or unique index. When inserting a piece of data, first determine whether the data exists in the MySQL database. If it does not exist, insert it normally. If it exists, ignore it:

At present, I will share these 4 ways of handling duplicate data in MySQL. The first three methods are suitable for fields with primary keys or unique indexes set, and the last method has no such restrictions. As long as you are familiar with the usage process, you will be able to master it quickly. There are also relevant materials and tutorials on the Internet, which are introduced in great detail. If you are interested, you can search

This concludes this article on 4 ways to avoid duplicate data insertion in MySQL. For more information on how to avoid duplicate data insertion in MySQL, please search previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!