MySQL inserts a record when the record does not exist. When the record exists, update the REPLACE statement. This situation may occur frequently when we use the database. If a table creates a unique index on a field and inserts a record into the table using an existing key value, a primary key conflict error is thrown. Of course, we may want to use a new record.
MySQL inserts a record when the record does not exist. When the record exists, update the REPLACE statement. This situation may occur frequently when we use the database. If a table creates a unique index on a field and inserts a record into the table using an existing key value, a primary key conflict error is thrown. Of course, we may want to use a new record.
MySQL inserts a record when the record does not exist and updates it when the record exists
REPLACE statement
We may often encounter this situation when using databases. If a table creates a unique index on a field and inserts a record into the table using an existing key value, a primary key conflict error is thrown. Of course, we may want to overwrite the original record value with the new record value. If you use the traditional method, you must first use the DELETE statement to DELETE the original record, and then use INSERT to INSERT a new record. MySQL provides us with a new solution, which is the REPLACE statement. When you use REPLACE to INSERT a record, if there is no duplicate record, REPLACE is the same as the INSERT function. If there is a duplicate record, REPLACE replaces the original record value with the value of the new record.
The biggest advantage of using REPLACE is that you can combine DELETE and INSERT into one to form an atomic operation. In this way, you do not have to consider complicated operations such as adding transactions when using both DELETE and INSERT.
When using REPLACE, the table must have a unique index, and the field where the index is located cannot allow null values. Otherwise, REPLACE is exactly the same as INSERT.
After REPLACE is executed, the system returns the affected number of rows. If 1 is returned, no duplicate records exist in the table. If 2 is returned, a duplicate record exists, the system automatically calls DELETE to DELETE this record, and then inserts this record with INSERT. If the returned value is greater than 2, multiple unique indexes exist, and multiple records are deleted and inserted.
The REPLACE syntax is very similar to INSERT. The following REPLACE statement inserts or updates a record.
Replace into users (id, name, age) VALUES (123, 'zhao Benshan ', 50 );
Insert multiple records:
Replace into users (id, name, age)
VALUES (123, 'zhao Benshan ', 50), (134, 'Mary', 15 );
REPLACE can also use the SET statement.
Replace into users SET id = 123, name = 'zhao Benshan ', age = 50;
As mentioned above, REPLACE may affect more than three records because there is more than one unique index in the table. In this case, REPLACE considers each unique index, deletes the duplicate record corresponding to each index, and inserts this new record. Assume that there is a table 1 with three fields a, B, and c. They all have a unique index.
Create table table1 (a int not null unique, B INT NOT NULL UNIQUE, c INT NOT NULL UNIQUE );
Assume that three records exist in table 1.
A B c
1 1 1
2 2 2
3 3 3
Next, we use the REPLACE statement to insert a record to table 1.
Replace into table1 (a, B, c) VALUES (1, 2, 3 );
The returned result is as follows:
Query OK, 4 rows affected (0.00 sec)
The record in table 1 is as follows:
A B c
1 2 3