How to solve the problem of logging a large amount of data into the database at one time in PHP

The insert of the database can be updated in batches. When there is a large amount of data inserted in a loop, the data can be retained without executing the insert command, and the last one can be inserted at once, such as the addAll() method of tp;

Database update can also be updated in batches if case when is used. I found a tp-based saveAll() method on Baidu for updating data

This article mainly talks about batch insert;

Example:

Generate an order

The normal statement is:

INSERT INTO order (`goods_id`,`num`,`price`) VALUES (1,1,'10.00');
//封装成函数
function add_order($goods_id,$num,$price){
$db->query("INSERT INTO order (`goods_id`,`num`,`price`) VALUES ($goods_id,$num,$price)");
}

Suppose there is a user who settles 1,000 items in the shopping cart at one time into an order, when 1,000 orders are generated;

for ($i=0;$i<1000;$i++){
$db->query("INSERT INTO order (`goods_id`,`num`,`price`) VALUES ($goods_id,$num,$price)");
}
//这样的话会导致服务器资源占用过大,网站卡死
//所以,我们可以
$sql = "INSERT INTO order (`goods_id`,`num`,`price`) VALUES ";
for ($i=0;$i<1000;$i++){
    if($i==0){
    $sql.="($goods_id,$num,$price)";
    }else{
    $sql.=",($goods_id,$num,$price)";
    }
}
$db->query($sql);

That’s probably what it means. It’s a bit more troublesome to implement batch update, so I won’t send it. The following is the sql execution statement for batch update

UPDATE tiyan .dm_user_cupboard SET `res_id` = CASE `id` WHEN 1041 THEN '1' WHEN 1058 THEN '1' WHEN 1055 THEN '1' END,`food_code` = CASE `id` WHEN 1041 THEN '68' WHEN 1058 THEN '47 ' WHEN 1055 THEN '49' END,`food_name` = CASE `id` WHEN 1041 THEN 'Red dates' WHEN 1058 THEN 'Lotus root' WHEN 1055 THEN 'onion' END,`num` = CASE `id` WHEN 1041 THEN '2 ' WHEN 1058 THEN '3' WHEN 1055 THEN '2' END,`level` = CASE `id` WHEN 1041 THEN '2' WHEN 1058 THEN '2' WHEN 1055 THEN '2' END,`update_time` = CASE `id `WHEN 1041 THEN '2017-12-09 21:40:06' WHEN 1058 THEN '2017-12-09 21:40:06' WHEN 1055 THEN '2017-12-09 21:40:06' END WHERE id IN (1041,1058,1055)

Comes with tp-based saveAll()

//批量更新
public function saveAll($datas,$model){
    ( $model || $model=$this->tableName);
    $model=empty($model)?$this->name:$model;
    $sql   = ''; //Sql
    $lists = []; //记录集$lists
    $pk    = $this->getPk();//获取主键
    foreach ($datas as $data) {
        foreach ($data as $key=>$value) {
            if($pk===$key){
                $ids[]=$value;
            }else{
                $lists[$key].= sprintf("WHEN %u THEN '%s' ",$data[$pk],$value);
            }
        }
    }
    foreach ($lists as $key => $value) {
        $sql.= sprintf("`%s` = CASE `%s` %s END,",$key,$pk,$value);
    }
    $sql = sprintf('UPDATE __%s__ SET %s WHERE %s IN ( %s )',strtoupper($model),rtrim($sql,','),$pk,implode(',',$ids));
    return M()->execute($sql);
}

Related recommendations:

Clean up php data before entering it into the database. Pay attention to php intval and mysql The int value range is different_PHP tutorial

Detailed example of simple data collection and storage program based on PHP

Simple data collection based on PHP Storage program, php collection and storage_PHP tutorial

The above is the detailed content of How to solve the problem of logging a large amount of data into the database at one time in PHP. For more information, please follow other related articles on the PHP Chinese website!