What is the functional difference between the update operation and the replacement operation when MySQL primary key conflicts (as shown in the figure)

Question

Answer 1

There is a difference between

INSERT ... ON DUPLICATE KEY UPDATE and REPLACE. The former executes UPDATE after INSERT fails, while the latter is equivalent to DELETE and then INSERT. It turns out that the entire line disappears!

Specifically, if your table has 3 columns:

id name alias
1  王五 王二麻子

The effects of these two sentences are different:

INSERT INTO pri (id, name) VALUES (1, '李四') ON DUPLICATE KEY UPDATE name = '李四'

REPLACE INTO pri (id, name) VALUES (1, '李四')

The former alias remains unchanged, but the latter will become null.

Answer 2

insert into on duplicate update is to operate on the original record
replace determines if there is a duplicate, delete it first, and then insert it