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

This article shows you the comparative analysis of MYSQL Replace into and Insert into duplicate key update, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Some students wonder whether the two sentences that seem to have the same function in MYSQL are repetitive or have their own characteristics. We need to find out and make use of them in appropriate situations.

Let's learn more about how they are used and their similarities and differences through several operations.

In case 1, determine whether there is a record with ID = 1 in the current ID. If so, update the data. If not, insert the record.

If we simply use the UPDATE statement here, it will be troublesome. First of all, we need to determine whether there is a record of ID = 1, and proceed to the next step according to the result of the judgment.

The above requirements can be met by using the replace function

The data has been directly changed, in fact, to be more accurate, not update but two operations, careful students should have found that the above picture is 2 rows affected, originally a piece of data, how to pop up two. (note 2 rows affected)

Replace into statement is mainly through the primary key and unique index to determine the repeatability of data, and then 1 delete data, 2 in the insert data routine, is actually a statement, complete the judgment, delete, insert operation, this design can avoid some programs on the table data processing special requirements.

Students who may be familiar with MYSQL will immediately ask, isn't there a sentence of INSERT DUPLICATE KEY, and what's the difference between him and replace into?

Requirement 2, now you need to insert data into the test table. If you don't repeat, insert the data in batches. If you repeat, update one of the values.

Obviously, after the operation, the repeated data is updated, but no duplicate data is inserted, so what are the similarities and differences asked by his classmates?

Whether 1 replace into can insert data in batches or not, insert duplicate key can.

2 both repace into and insert duplicate key can update data

3 replace into can not be followed by select statements can not, insert into duplicate key can be connected to select statements, which in the two statements in the applicable environment has an obvious separation.

4 different operations on database tables, one is delete, insert, and the other is only insert update, which is also fundamentally different at the physical operation level of the database.

The most important difference is that when dealing with self-incrementing keys, if we do not specify the data of self-incrementing primary keys (assuming that we use a unique index to judge instead of self-incrementing primary keys), the result will be very different. Replace into will delete duplicate rows and insert new ones, while insert into duplicate key will not change the original self-incrementing primary key, but will directly UPDATE. This is fundamentally different when based on the use of self-increasing primary keys in applications. (relatives of ORACLE may not understand this, because ORACLE itself does not have self-increasing primary keys at the beginning of its design, and the underlying data storage design is different, which makes it difficult for ORACLE to understand the design and processing of some data tables in SQL SERVER MYSQL. Similarly, SQL SERVER MYSQL has difficulties in understanding ORACLE in some data table design.) Back to the point, MYSQL programmers are using these two statements. I summarized the relevant usage scenarios.

1 when a small amount of data is not updated in large quantities, and the whole row of data needs to be changed, REPLACE INTO can be used, and if the primary key is closely related to the application, the primary key value needs to be specified, otherwise the primary key of the original data row will disappear, which is useful in some applications, because instead of the original primary key, a new ID should contain the new value.

2 mass data update and insertion, two or more tables, merge and insert into one table, and remove duplicates, or update the VALUE of a field requires the use of INSERT INTO duplicate key update statements

In the case of the same large amount of data, the performance of insert into duplicate key update statements is better than that of replace into, and the impact of two operations and one operation on operation performance is also self-evident.

Note: during the test, some minor problems with self-increasing primary keys in insert into duplicate key update in 5.x seem to have disappeared on 8.0, and the details are still being tested. It seems that 8.0 is really worth looking forward to and having.

The above is the comparative analysis of MYSQL Replace into and Insert into duplicate key update. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.