The use of replace into in MySQL and the difference from inset into

When inserting data into a table, we often encounter situations such as: 1, first to determine whether the data exists, 2, if it does not exist, then insert; 3, if present, update.

This can be handled in SQL Server:

If not exists (select 1 from t where id = 1)?
INSERT into T (ID, Update_time) VALUES (1, GETDATE ())
Else
Update T Set update_time = GETDATE () Where id = 1

So how do you implement such logic in MySQL? There is an easier way to do this in MySQL: replace into

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), 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.

In order to be able to use replace, you must have both insert and delete permissions for the table.

The Replace statement returns a number that indicates the number of rows affected. The number is the number of rows that are deleted and inserted. If the number is 1 for a single-line replace, the row is inserted and no rows are deleted. If the number is greater than 1, one or more old rows are deleted before the new row is inserted. If the table contains more than one unique index, and the new row replicates the values of the different old rows in different unique indexes, it is possible that a single row has replaced multiple old rows.

The number of rows affected can easily determine if replace adds only one row, or if replace replaces other rows: Check whether the number is 1 (added) or larger (replace).

If you are using the C API, you can use the Mysql_affected_rows () function to get the number of rows affected.

Currently, you cannot change from one table to another in a subquery and select from the same table.

The following is a more detailed description of the algorithm used (the algorithm is also used for load DATA ...). REPLACE):

1. Try inserting a new row into the table

2. When an insert fails because of a duplicate keyword error for a primary key or a unique keyword:

A. Removing conflicting rows from a table that contain duplicate key values

B. Try again to insert a new row into the table

The use of replace into in MySQL and the difference from inset into