SQL replace into usage detailed description

SQL replace into usage detailed description

The run of replace is similar to insert. With one exception, if an old record in the table is used with a primary

Key or a new record of a unique index has the same value, 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 will be the same as the insert, because no indexes are used to determine whether the new row replicates other rows.

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

As with inserts. You cannot reference a value from the current row, nor can you use a value in a new row. If you use one such as "

Set col_name = Col_name + 1, a reference to the column name on the right side will be 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 of rows that are deleted and inserted, and the

。 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 value of a different old row in a different unique index, it is possible that a single row replaces more than one old row.

The number of rows affected can easily determine whether replace has added only one row, or if replace also replaces other rows

: Check whether the number is 1 (added) or larger (replace).

1. Try inserting the new row into the table

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

A. Delete conflicting rows that contain duplicate key values from the table

B. Try inserting the new row into the table again

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); With 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 records are deleted when replace into. If the table has a self added primary key.
Then there's going to be a problem.

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

Second, the frequent replace into will cause the value of the primary key of the new record to increase rapidly.
One of these days. When the maximum is reached, it overflows because the data is too large. There's no way to insert a new record. The data table is full, not because there is not enough space, but because the value of the primary key cannot be increased.