The use of MySQL Replace INTO

  • 2020-05-06 11:48:30
  • OfStack

REPLACE works much like INSERT. With one exception, if an old record in the table has the same value as a new record for PRIMARY   KEY or an UNIQUE index, the old record is dropped before the new record is inserted. See section 13.2.4, "INSERT syntax."

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 default values, just like INSERT. 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, the assignment is equivalent to SET   col_name   =   DEFAULT(col_name)   +   1.


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

The REPLACE statement returns a number indicating the number of affected rows. The number is the sum of the number of rows deleted and inserted. If the number is 1 for a single row REPLACE, a 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 unique indexes, and the new row copies the values of different old rows in different unique indexes, it is possible that a single row replaces multiple old rows.

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

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

Currently, you cannot replace to a table and select from the same table in a subquery.

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

1.   attempts to insert a new row into the
table
2.   when insertion fails due to a duplicate keyword error for the primary key or unique keyword:

Es93en.  removes from the table conflicting rows
containing duplicate keyword values
Es98en.  again tries to insert a new row into the table

My throat is killing me today. Go to bed
Use format:

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

Related articles: