Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "what are the methods to avoid repeated data insertion in MySQL database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The most common way is to set a primary key or unique index for the field. When you insert duplicate data, an error is thrown and the program terminates, but this will bring trouble to the subsequent processing, so you need to do special handling for the insert statement to avoid or ignore exceptions as far as possible. Let me briefly introduce below, interested friends can try:

In order to facilitate the demonstration here, I have created a new user test table, which mainly has four fields of id,username,sex,address, in which 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 insert is ignored. The prerequisite is that the inserted data field has a primary key or unique index. The test SQL statement is as follows. When inserting this data, the MySQL database will first retrieve the existing data (that is, the idx_username index). If it exists, ignore the insert. 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 is performed. The prerequisite is the same as above, and the inserted data field has 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, perform the update update operation. If it does not exist, insert it directly:

03 replace into

That is, when inserting data, delete and insert again if the data exists. The prerequisite is 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, 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

Namely insert into... Select... Where not exist., this method is suitable for inserting a data field without setting 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 normally. If so, ignore:

At present, share these four MySQL ways to deal with duplicate data, the first three ways are suitable for the field to set a primary key or a unique index, the last way does not have this restriction, as long as you are familiar with the use process, you will soon be able to master, there are also relevant materials and tutorials on the Internet, the introduction is very detailed, if you are interested, you can search.

This is the end of the content of "what are the ways to avoid repeatedly inserting data in the MySQL database?" Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.