在循环中插入sql查询一个好的做法还是坏的？

I have a list of users which needs to be iterated using a foreach loop and inserted in to a table for every new row in db table.

我有一个用户列表,需要使用foreach循环进行迭代,并插入到db表中每个新行的表中。

$data['entity_classid'] = $classid;
    $data['notification_context_id'] = $context_id;
    $data['entity_id'] = $entity_id;
    $data['notification_by'] = $userid;
    $data['actionid'] = $actionid;
    $data['is_read'] = 0;
    $data['createdtime'] = time();
    foreach($classassocusers as $users){
            $data['notification_to'] = $users->userid;
            $DB->insert_record('homework.comments',$data,false);
        }

so using the insert query as given above is

所以使用上面给出的插入查询是

A good practice or bad practice,

一个好的做法或不好的做法,

Shall i place any delay after every insert query execution?

我应该在每次插入查询执行后放置任何延迟吗?

what are the pros and cons of doing so?

这样做的利弊是什么?

Thanks

2 个解决方案

#1

Using the query like that is a good practice in your case. You will have to insert a list of users anyway, so you will have to process many queries. No way around this!

在您的情况下使用这样的查询是一个很好的做法。您无论如何都必须插入用户列表,因此您必须处理许多查询。没办法解决这个问题!

I have no idea why you would want to place a delay after each insert. These methods are synchronous calls, so your code will be "paused" anyway during the execution of your query. So delaying it will just delay your code while nothing is progressing.

我不知道你为什么要在每次插入后放置延迟。这些方法是同步调用,因此在执行查询期间,代码将“暂停”。所以推迟它只会延迟你的代码而没有任何进展。

So your loop will not continue while executing a query. So don't delay your code even more on purpose.

因此,在执行查询时,您的循环将不会继续。所以不要故意拖延你的代码。

Another way to do this is by executing one query though.

另一种方法是执行一个查询。

$user_data = "";
foreach($classassocusers as $users) {
   $user_data .= "('" . $users->userid . "', '" . $users->name . "'), ";
}

$user_data = substr($user_data, 0, strlen($user_data) - 2);

$query = "INSERT INTO `homework.comments` ( `id`, `name` )
          VALUES " . $user_data;

That's supposed to make a query like:

这应该是一个查询,如:

INSERT INTO `homework.comments` ( `id`, `name` )
VALUES ('1', 'John'),
       ('2', 'Jeffrey'),
       ('3', 'Kate');

(By the way, I made some assumptions regarding your $users object and your table structure. But I'm sure you catch the idea)

(顺便说一句,我对你的$ users对象和你的表结构做了一些假设。但我相信你能理解这个想法)

#2

It all depends on your requirements.

这一切都取决于您的要求。

If you run 500.000 of these updates in 5 minutes - every 15 minutes, your database will have a hard time. If you do this for 1.000 users every 15 minutes - this is a great approach.

如果您在5分钟内运行500,000个这些更新 - 每15分钟一次,您的数据库将很难。如果每15分钟为1.000个用户执行此操作 - 这是一个很好的方法。

When performance is demanded, concider the following:

当需要性能时,请遵循以下内容:

Combine INSERT using the VALUES syntax, process every 500/1000.

使用VALUES语法组合INSERT,每500/1000处理一次。

Add a small timeout after the query.

在查询后添加一个小超时。

Otherwise, this is an excellent approach!

否则,这是一个很好的方法!

#1