Detail MySQL replace into

The Replace statement is generally similar to insert, but if there is a primary or a unique index in the table, if the inserted data is the same as the original primary key or unique, the original data is deleted, and then a new piece of data is added , so sometimes executing a replace statement is equivalent to executing a DELETE and insert statement. Go directly to the example:

Create a new test table, three fields, Id,title,uid, ID is the self-increment primary key, UID is the unique index;

CREATE TABLE' Test ' (' Id ')int( One) not NULLauto_increment, ' title 'varchar( -)DEFAULT NULLCOMMENT'title', ' uid 'int( One)DEFAULT NULLCOMMENT'UID',  PRIMARY KEY(' Id '),UNIQUE KEY' uid ' (' uid ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8MB4;

Insert  into  VALUES (' hello ','1'); Insert  into  VALUES (' National Day ','2');

The results are as follows:

When inserting data using replace into:

REPLACE  into VALUES (' This is a 8-day leave Oh ','3');

When the UID is present, use the Replace into statement

REPLACE  into VALUES (' This is Uid=1 's first piece of data Oh ','1');

It was not anticipated that MySQL would actually delete the old record and write a new record when the data conflict (that is, when the UID occurred). Through the above examples, I believe Bo friends can see:

Replace into is similar to insert, except that replace into first attempts to insert data into the table,

1. If you find that this row of data is already in the table (judging by a primary key or a unique index), delete the row data first, and then insert the new data. 2. Otherwise, insert the new data directly.

Note that the table in which the data is inserted must have a primary key or a unique index! Otherwise, replace into will insert the data directly, which will result in duplicate data appearing in the table.

MySQL replace into has three different 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 similar to insert Select, which does not necessarily require column names to match, and in fact, MySQL does not even care about the column names returned by SELECT, it requires the location of the columns. For example, replace into TB1 (name, title, mood) Select Rname, Rtitle, rmood from TB2;? This example uses replace into to import all data into the tb1 from the? TB2.

The third replace set usage is similar to the update set usage, with an assignment such as "Set col_name = col_name + 1", and the reference to the column name on the right is treated as default (col_name). Therefore, the assignment is equivalent to set col_name = DEFAULT (col_name) + 1.

The first two forms are used more. The "into" keyword can be omitted, but it is better to add "into" so that the meaning is more intuitive. In addition, for columns that are not given a value, MySQL automatically assigns default values to those columns.

Detail MySQL replace into