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!