The use of the SQL statement replace into

SQL replace into usage detailed description

The replace operation is similar to insert. There is only one exception, if an old record in the table is used with a primary

A new record of key or a unique index has the same value, and the old record is deleted before the new record is inserted.
Note that it is meaningless to use a Replace statement unless the table has a primary key or a unique index. The

The statement is the same as insert because no index is used to determine whether the new row has replicated other rows.

The values of all columns are taken from the values that are specified in the Replace statement. All missing columns are set to their default values, which

The same as insert. You cannot reference a value from the current row, nor can you use a value in a new row. If you use an example such as "

Set col_name = col_name + 1 ", the reference to the column name on the right is used as the default

(col_name) processing. Therefore, the assignment is equivalent to set col_name = DEFAULT (col_name) + 1.

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, and

。 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 is copied

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

: Checks whether the number is 1 (added) or larger (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

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

REPLACE into ' table ' (' unique_column ', ' num ') VALUES (' $unique _value ', $num), and insert INTO ' table ' (' unique_column ', ' num ') VALUES (' $unique _value ', $num) on DUPLICATE UPDATE num= $num; there are some differences.
The difference is that the old record is deleted when replace into. If there is a self-growing primary key in the table.
Then there's going to be a problem.

First, because the record is different from the primary key value of the old record, all the associations in the other tables that are associated with the old data primary key ID of this table are all corrupted.

Second, the frequent replace into causes the value of the primary key of the new record to increase rapidly.
Someday. When the maximum value is reached, it overflows because the data is too large. You won't be able to insert a new record. The data sheet is full, not because of the lack of space, but because the value of the primary key cannot be increased any more.

The use of the SQL statement replace into