Usage of replace into in MySQL

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

Insert two piece of data

 insert into test (TITLE,UID) values ( ' 123465" 1001  "insert into test (TITLE,UID) values ( ' 123465" 1002  
 executes a single insert of data to see that the result is as follows:              

[SQL]into  VALUES ('123465','1001'10.175s  

When inserting data using replace into:

VALUES ('1234657','1003');

Execution Result:
[SQL] REPLACE into Test (title,uid) VALUES (' 1234657 ', ' 1003 ');
Rows affected: 1
Time: 0.035s

The current Database test table has all the following data:

When the UID is present, use the Replace into statement

REPLACEinto Test (TITLE,UID)VALUES ('1234657 ','1001');  [SQL]REPLACE into test (title,uid) VALUES ('1234657','1001' ); rows affected: 2 time: 0.140s            

Replace into t (ID, Update_time) VALUES (1, now ());

Or

Replace into t (ID, update_time) Select 1, now ();

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), then the row data is first deleted and then the new data is inserted. 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.

Usage of replace into in MySQL