Explain the usage of MySQL replace into in detail

Time:2022-1-2

In general, the replace statement is similar to the insert statement, but if there is a primary or unique index in the table, if the inserted data is the same as the original primary key or unique, the original data will be deleted and a new data will be added. Therefore, sometimes executing a replace statement is equivalent to executing a delete and insert statement. Go directly to the example:

Create a new test table with three fields: ID, title and uid. ID is the self incremented primary key and uid is the unique index;

CREATE TABLE `test` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `Title ` varchar (25) default null comment ',
 `uid` int(11) DEFAULT NULL COMMENT 'uid',
 PRIMARY KEY (`Id`),
 UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert into test (title, uid) values (‘Hello ‘,’ 1 ‘);
Insert into test (title, uid) values (‘National day ‘,’ 2 ‘);

The results are as follows:

When inserting data using replace into:

Replace into test (title, uid) values ('This is an 8-day Holiday ',' 3 ');

Use the replace into statement when the uid exists

Replace into test (title, uid) values ('This is the first data with uid = 1 ','1');

It is not expected that MySQL actually deletes old records and writes new records in case of data conflict (that is, when uid duplicate data occurs),. Through the above examples, I believe bloggers can see:

The function of replace into is similar to that of insert, except that replace into first attempts to insert data into the table,

1. If it is found that this row of data already exists in the table (judged according to the primary key or unique index), delete this row of data first, and then insert new data. 2. Otherwise, insert new data directly.

Note: the table to insert data must have a primary key or unique index! Otherwise, replace into will directly insert data, which will cause duplicate data in the table.

MySQL replace into has three forms:

1. replace into tbl_name(col_name, …) values(…)

2. replace into tbl_name(col_name, …) select …

3. replace into tbl_name set col_name=value, …

The first form is similar to the use of insert into,

The second use of replace select is also similar to insert select. This use does not necessarily require column names to match. In fact, MySQL doesn’t even care about the column names returned by select. It needs the position of the column. For example, replace into tb1 (name, title, mood) select rname, rtitle, rmood from TB2;? This example uses replace into from? TB2 imports all data into tb1.

The third replace set usage is similar to the update set usage. If you use an assignment such as “set col_name = col_name + 1”, the reference to the column name on the right will be treated as default (col_name). Therefore, this assignment is equivalent to set col_ name = DEFAULT(col_name) + 1。

The first two forms are used more. The keyword “into” can be omitted, but it is better to add “into”, which makes the meaning more intuitive. In addition, MySQL will automatically assign default values to those columns that are not given values.