MySQL DML operation suggestions

(I) INSERT large data volume

(1) Replace a single row with multi-row insert: insert into t values (),(),(),...

(2) load data infile... into table t

(3) Write a temporary table and INSERT... SELECT

(4) cancel the index and constraints, and re-build the data after loading.

DELAYED attribute specified during INSERT

For InnoDB, partitions can be divided into multiple transactions and submitted in batches.

(Ii) large data volume UPDATE

Before that, we should disable indexes and related constraints.

① SELECT unique field, change field

② Write the result set to a temporary table

③ Create a composite index for a temporary table

④ Each record of the cyclic temporary table

⑤ Update the change field of the target table based on the unique field of the temporary table

(Iii) large data volume DELETE

① Partitioned table

② Obtain the unique identifier of the data to be deleted to the temporary table and batch delete of the cursor Loop

Recommended reading:

PL/SQL ORA-14551: cannot perform DML operations in queries solved

Common MySQL DDL, DML, and DCL languages (example)

Execute batch DML exercises for Oracle basic transactions and ForAll

Oracle DML Statement (insert, update, delete) rollback Estimation