1.语法：

insert into 表名(字段名1,字段名2,字段名3,字段名4……)
values(字段1的值,字段2的值,字段3的值,字段4的值……)
on duplicate key update 某字段=某字段的值,某字段=某字段的值,某字段=某字段的值

例如：

-- id为主键,school_code为唯一约束
-- id使用的是uuid,一般不会重复
-- If the school code column does not existnanshan,则插入该数据 
-- 如果存在nanshanThis school code,Replace the school address with ‘东海’
insert into school(id,school_code,school_address)
values(UUID(),'nanshan','月亮湾')
on duplicate key update school_address='东海'

2.意思：

Determine whether the data exists in the table（存在规则：primary key 和 unique key）,如果不存在则插入一条,如果存在则update该条数据.

3.注意：

1.If inserting a piece of data 主键 和 Unique data exists in the table,However, the data determined by the primary key and the data determined by the uniqueness are not the same（实际上,This occurs because there is a problem with the inserted data,We just assume this is the case）,此时,updateThe data of the primary key is the one determined in the uniqueness？
After the experiment,The one for the primary key.

2.If the implementation isinsert语句,则返回的结果：Affected rows：1
If the implementation isupdate语句,则返回的结果为：Affected rows: 0 或者 Affected rows: 2
即使是update,That should affect it0line or line,为啥出来个2呢.Refer to the official explanation here
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

Red area translation：
如果是insert则为1;
如果是update,但是updateThe content is consistent with the original data0,It is inconsistent with the original data2;

So the number of rows affected here just represents what operation was performed.

3.也有人这样写

insert ignore into ed_auth(phone_open_id,diagnostician_uid,name,school_code,id)
values(?,?,?,?,?)
on duplicate key update phone_open_id=?,name=?,school_code=?

This compares to the syntax above,在insert后面多了一个ignore,Actually this is a separate syntax（update中也可以使用）,ignoreis intended to be ignored,Here it is to ignore errors,That is, when an insertion error occurs（是数据错误,不是SQL语法错误）,不报错,只是返回Affected rows: 0 .