Detailed description of mysql replace into usage

The replace statement is generally similar to insert. However, 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 one will be added. Therefore, sometimes executing a replace statement is equivalent to executing a delete and insert statement. Let’s go straight to the example:

Create a test table with three fields: id, title, and uid. id is the auto-increment primary key and uid is the unique index.

CREATE TABLE `test` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(25) DEFAULT NULL COMMENT 'Title',
 `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 using replace into to insert data:

REPLACE INTO test(title,uid) VALUES ('This time is 8 days off','3');

When uid exists, use replace into statement

REPLACE INTO test(title,uid) VALUES ('This is the first data of Uid=1','1');

I didn't expect that when MySQL encounters data conflicts (that is, when uid duplicates occur), it actually deletes the old record and then writes the new record. Through the above examples, I believe that bloggers can see:

replace into is similar to insert, except that replace into first tries to insert data into the table.

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

Please note that the table into which data is inserted must have a primary key or a unique index! Otherwise, replace into will directly insert the data, which will result in 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 usage of insert into.

The second usage of replace select is also similar to insert select. This usage does not necessarily require the column names to match. In fact, MYSQL does not even care about the column names returned by the select. What it needs is the position of the column. For example, replace into tb1( name, title, mood) select rname, rtitle, rmood from tb2; This example uses replace into to import all data from tb2 into tb1.

The third replace set usage is similar to the update set usage. Using 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 frequently. The keyword "into" can be omitted, but it is better to add "into" so that the meaning is more intuitive. In addition, for columns that have no given values, MySQL will automatically assign default values ​​to these columns.