mysql - PHP bulk insert and update issues

Insert problem

When PHP operates large batches of data, the solution I thought of is as follows

Method 1: Construct the sql statement in the foreach loop traversal and then insert it into the database
insert into xxx values ​​(xxx, xxx, xxx)

Method 2: foreach loop traverses to construct the sql statement, and finally inserts
insert into xxx (field1, field2, field3) values ​​(xxx1, xxx2, xxx3), (xxx1, xxx2, xxx3)

The above is just for insertion. If you want to first determine whether the inserted data exists in the database when inserting, then a select operation must be performed before each insertion. Is this inefficient? How to optimize clam?

Updated question

In fact, it is similar to the insert above. Select before update. If it exists, update it. If it does not exist, insert it. There are still many SQL statements. How to optimize it?

Reply content:

Insert problem

When PHP operates large batches of data, the solution I thought of is as follows

Method 1: Construct the sql statement in the foreach loop traversal and then insert it into the database
insert into xxx values ​​(xxx, xxx, xxx)

Method 2: foreach loop traverses to construct the sql statement, and finally inserts
insert into xxx (field1, field2, field3) values ​​(xxx1, xxx2, xxx3), (xxx1, xxx2, xxx3)

The above is just for insertion. If you want to first determine whether the inserted data exists in the database when inserting, then a select operation must be performed before each insertion. Is this inefficient? How to optimize clam?

Updated question

In fact, it is similar to the above insertion. Select before updating. If it exists, update it. If it does not exist, insert it. There are still many SQL statements. How to optimize it?

1- Mysql also has a syntax that is REPLACE INTO. If it exists, update it. Otherwise, insert
2- Mysql has another syntax that is INSERT INTO... ON DUPLICATE KEY UPDATE. If there is a unique key conflict, update it.
3- Large batches of data insertion are very rare in actual development. At least 1,000 items or less are definitely not considered large batches, so if you want to save trouble, you will usually verify that the trouble caused by being lazy and saving 4 hours is enough for you. 8 hours of this prophecy.

Usually large batch data insertion occurs when data is imported from the old database, but this kind of import is usually only once, so it cannot be taken too seriously. Others, such as importing data from uploaded csv files, need to depend on the specific business. For logic, it is more common to use try/catch for insertion. The failed data is displayed, allowing the user to confirm the overwrite, and then update.

1) If it can be guaranteed that there will be no duplication of data in insert, then insert is definitely more appropriate
2) Index~, proper index really helps a lot to improve performance
3) Use mysql batch processing Importing can help improve performance, but the disadvantage is that data may be lost.
4) Turn off automatic commit=true, which means closing the transaction. After each submission of several records (for example, 1W records), commit once. The speed can be greatly improved. It is also very easy to configure 1W QPS on a single machine.
5) Redesign the mysql library, separate reading and writing, create a cluster, and install SSD...

REPLACE deletes the insert if it exists or updates if DUPLICATE exists

You can use replace to solve the trouble of update and insert

On duplicate key update Use this to create a unique index for each field that you don’t want to duplicate, so you don’t have to check whether to choose insert or update. It can automatically execute the statement following UPDATE when repeated

You don’t have to use REPLACE and DUPLICATE. You can try:
Start a transaction and insert in a loop. If the insertion fails, update instead.

<code><?php
$db = new mysqli('127.0.0.1','user','pass','dbname',3306);
$db->query('SET AUTOCOMMIT=0');
$db->query('START TRANSACTION');
//开始循环
if(!$db->query('INSERT INTO posts(id, post_title, post_content) VALUES(1,"title_1","content_1")')) {
    $db->query('UPDATE posts SET post_title = "title_1", post_content = "content_1" WHERE id = 1');
}
//插入失败,或者没有AUTO_INCREMENT字段,或者不是INSERT语句,insert_id为0.
echo $db->insert_id;
$db->query('COMMIT');
$db->query('SET AUTOCOMMIT=1');</code>