MySQL Magic: Bulk INSERT records, repeat records are automatically updated

You may encounter two problems when you update large amounts of data:

1. If each update executes a SQL performance is low, it is also prone to blocking;

2. There may be problems with primary key duplication when you batch update
   

Use on DUPLICATE key to update a SQL to resolve bulk updates and primary key duplication issues (ID primary key)

INSERT into MyTable (Id,pid,ele,anim) VALUES (?,?,?,?), (?,?,?,?), (?,?,?,?) On DUPLICATE KEY UPDATE pid=values (PID), Ele=values (ele)//pid=values (PID), Ele=values (ele) Indicates that when the ID field value of a column in values duplicates the value of an existing ID field in the table, the two fields of the corresponding record are updated///can also specify other values or perform operations: pid=pid+1,ele=ele-1//because the column anim is not specified here, the column with duplicate ID is encountered. The Anim field of the column in the table is not updated//If a column is inserted as a new record, the value of the affected row is 1, and if the records in the table are updated, the value of the affected row is 2

It is not only valid for the unique primary key, but also for the composite primary key, and the composite primary key is set:

ALTER TABLE mytable ADD (CONSTRAINT PRIMARY KEY (id,pid));

But on DUPLICATE KEY update is only MySQL's unique syntax, not SQL standard syntax, don't mess with OH

MySQL Magic: Bulk INSERT records, repeat records are automatically updated