如何使用mysql和php执行批处理操作 [英] How to perform batch operation using mysql and php

问题描述

我正在尝试使用单个MySQL查询插入多个记录，但是我不想一次插入大量记录.

I am trying to insert multiple records using single MySQL query, but i don't want to insert a huge number of records at once.

以下代码获取，构建和插入记录

The following code fetches, buildes and inserts the records

if(is_array($id_rec)){
        $values = "";
        foreach($id_rec as $key=>$value){
            $values .= "(".(int)$value.",".(int)$id_group."), ";
        }
        $values = rtrim($values,", ");
        $sql = "INSERT IGNORE INTO fu_rec_group_link (id_rec, id_group) VALUES ".$values;
        $GLOBALS['db']->execute($sql);

我在这里有两个问题.

I have two questions here.

1. 第一个问题: 我应该一次插入几条记录?什么是适量?
2. 第二个问题: 在达到记录的最大限制后如何暂停/中断循环并插入它，然后从我的左边继续呢?

1. Frist Question: How many records should i insert at once? What is right amount?
2. Second Question: How can i pause/break the loop after reaching the max limit of records and insert it and then continue from i left?

任何帮助将不胜感激.

推荐答案

您应该在单个INSERT中插入尽可能多的记录，而不是将其分解为多个INSERTS.

You should insert as many records as possible in a single INSERT as opposed to breaking it down into many INSERTS.

例如，做

INSERT INTO mytable (column1, column2, column3)
VALUES ('text', 'text', 'text'),
       ('text', 'text', 'text'),
       ('text', 'text', 'text'),
       ('text', 'text', 'text');

比执行

INSERT INTO mytable (column1, column2, column3)
VALUES ('text', 'text', 'text'),
       ('text', 'text', 'text');
INSERT INTO mytable (column1, column2, column3)
VALUES ('text', 'text', 'text'),
       ('text', 'text', 'text');

总的性能差异随着行数的增加而更加明显.

The aggregate difference in performance is more pronounced as the number of rows go up.

这篇关于如何使用mysql和php执行批处理操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！