Resolve the differences between Replace INTO and INSERT INTO in SQL statements

REPLACE works very similar to INSERT. With the exception of 1, if an old record in the table has the same value as a new record for an PRIMARY KEY or an UNIQUE index, the old record is deleted before the new record is inserted.
Note that it makes no sense to use an REPLACE statement unless the table has an PRIMARY KEY or UNIQUE index. This statement will be the same as INSERT, because no index is used to determine whether the new row copies other rows.

The values for all columns are taken from the values specified in the REPLACE statement. All missing columns are set to their respective default values, as is the case with INSERT1. You cannot reference a value from the current line, nor can you use a value in a new line. If you use an assignment such as "SET col_name = col_name + 1", the reference to the column name on the right is handled as DEFAULT(col_name). Therefore, this assignment is equivalent to SET col_name = DEFAULT(col_name) + 1.

To be able to use REPLACE, you must have both the INSERT and DELETE permissions for the table.

The REPLACE statement returns a number indicating the number of affected rows. This number is the sum of the number of rows deleted and inserted. If the number REPLACE is 1 for a single row, 1 row is inserted and no rows are deleted. If the number is greater than 1, one or more of the old rows are deleted before the new row is inserted. If the table contains multiple 1-only indexes, and the new row copies the values of different old rows in different 1-only indexes, it is possible that a single 1 row replaces multiple old rows.

The number of affected rows can be easily determined if REPLACE has only added 1 row, or if REPLACE has also replaced other rows: check to see if the number is 1 (add) or larger (replace).

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

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

(this algorithm is also used for LOAD DATA... REPLACE) :
1. Try to insert a new row into the table
2. When the insertion fails due to repeated keyword errors for the primary key or only one keyword:
a. Removes conflicting rows from the table that contain duplicate keyword values
b. Try again to insert a new row into the table

The format is as follows:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...). , (...). ,...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT},...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT...