Shulou(Shulou.com)05/31 Report--

MySQL database how to avoid writing duplicate data, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.

When we design tables in MySQL database, in order to prevent duplicate data in the table, we usually set the specified field as PRIMARY KEY index or UNIQUE KEY index to ensure the uniqueness of the data.

If we set a unique index, SQL statements will fail to execute successfully and throw errors when writing duplicate data.

Therefore, when we write data to MySQL database, we usually consider how to avoid duplicate writing of data or throwing exceptions due to duplicate data writing.

There are four common ways to prevent duplicate writes in MySQL databases: In this article, we will take the user_basic_infor table as an example (the table SQL statement is as follows) to share how to use these methods to avoid duplicate data writing.

insert ignore into as above table creation statement, use PRIMARY KEY and UNIQUE KEY in user_basic_infor table to ensure the uniqueness of data. To avoid writing data repeatedly, insert ignore into syntax can be used, as follows:

When writing data using insert ignore, if an error occurs, such as duplicate data, no error is returned, only a warning is returned. That is, insert ignore ignores existing data in the database. If writing new data does not cause duplication, write new data. If writing new data will cause duplication, skip this new line of data.

Note that when using insert ignore, make sure that the SQL statement itself is not problematic, otherwise it will be ignored.

on duplicate key update Similarly, use PRIMARY KEY and UNIQUE KEY in user_basic_infor table to ensure the uniqueness of data. To avoid writing data repeatedly, you can also use on duplicate key update syntax, as follows:

If on duplicate key update... is specified at the end of the insert statement, and writing the row will cause duplicate values in the PRIMARY KEY or UNIQUE KEY, update the row where the duplicate values are located, and write the new row if it will not cause the unique value column to duplicate.

replace into Similarly, use PRIMARY KEY and UNIQUE KEY in the user_basic_infor table to ensure the uniqueness of the data. To avoid writing data repeatedly, replace into syntax can also be used, as follows:

replace tries to write a new row to the table. If the primary key index (PRIMARY KEY) and the unique index (UNIQUE KEY) of the data to be written are duplicated, resulting in an error caused by a write failure, replace will delete the original row involved in duplication from the table first, and then try to write the new row to the table again. This method is to delete the original data before writing.

insert... select... where not exist... In addition, in MySQL database, write a piece of data, we can first check whether this piece of data already exists, when the data does not exist and then perform the write operation, so that you can not only judge by the primary key index (PRIMARY KEY) or the unique index (UNIQUE KEY), but also by other conditions, as follows:

Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

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.