SQL resolves primary key conflicts

Insert fails when data is inserted, assuming that the value corresponding to the primary key already exists! This is the primary key violation.
When the primary key is in conflict (duplicate key), it can be selectively processed, that is, ignored, updated, or replaced.

1. Ignore

Insert ignore into table

Keep the original record, ignoring the newly inserted record

2. Replace

Replace into table

Replace the original record, delete the original record, and then insert the new record

3. Update insert into table value（"xx","xx"） ON DUPLICATE KEY UPDATE

In fact, this is the original need to execute 3 SQL statements (select,insert,update), reduced to 1 statements to complete.
That

IF (SELECT * FROM where 存在) { UPDATE SET WHERE ;} else { INSERT INTO;}

Such as:

insert into Tble values (1,‘xxx‘,‘xxx‘) ON DUPLICATE KEY UPDATE status =‘drain‘;Query OK, 2 rows affected (0.00 sec)

The above statement pseudo-code indicates that the

if (select * from table where id=1) { update device set status =‘drain‘ where id=1 } else { insert into table value (1,‘xxx‘,‘xxx‘)}

Obviously, the id=1 is there, so the update operation is performed

mysql> select * from table;+-------+--------+-----------+| id| status | spec_char |+-------+--------+-----------+| 1 | drain | yangting | | 2 | dead | zhong | +-------+--------+-----------+2 rows in set (0.00 sec)

SQL resolves primary key conflicts