SQL replace into usage

SQL replace into usage

Replace runs like insert. There is only one exception. Assume that an old record in the table and a record used for primary

If the new record of a key or unique index has the same value, the old record is deleted before the new record is inserted.
Note: unless the table has a primary key or unique index, using a replace statement is meaningless. The

The statement is the same as the insert statement, because no index is used to determine whether other rows are copied in the new row.

The values of all columns are equal to the value specified in the replace statement. All missing columns are set as their default values.

Same as insert. You cannot reference a value from the current row or use a value in a new row. If you use

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

(Col_name) processing. Therefore, the value is equivalent to set col_name = default (col_name) + 1.

To use replace, you must have both the insert and delete permissions for the table.

The replace statement returns a number to indicate the number of affected rows. This is the sum of the number of deleted and inserted rows.

. If this number is 1 for a single row, one row is inserted and no row is deleted. If the number is greater than 1

Before a new row is inserted, one or more old rows are deleted. If the table contains multiple unique indexes and a new row is copied

The value 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 only one row is added to replace, or whether replace also replaces other rows.

: Check whether the number is 1 (ADD) or greater (replace ).

1. Try 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:

A. Delete conflicting rows with duplicate keyword values from the table

B. Try to Insert a 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); and insert into 'table' ('unique _ column ', 'num') values ('$ unique_value', $ num) on duplicate update num = $ num; there are some differences.
The difference is that old records are deleted when replace into is used. If the table has an auto-increment primary key.
There is a problem.

First, because the new record and the old record have different primary key values, all the associations established with the old data primary key ID in other tables will be damaged.

Second, frequent replace into causes the primary key value of the new record to increase rapidly.
One day. When the maximum value is reached, the data overflows because of the large amount of data. There is no way to insert new records. The data table is full, not because the space is insufficient, but because the value of the primary key cannot be increased.