How to handle large amounts of data in PHP database

There is a business need to loop n pieces of data at once. When inserting or updating the database, if you simply loop, insert/update, it will consume too many database resources

The following is a simple solution

The insert of the database can be updated in batches. When a large amount of data is inserted cyclically, the data can be retained first. Execute the insertion command, and insert it all at once when reaching the last item, such as the addAll() method of tp; (Recommended learning: PHP video tutorial)

Update of the database if If you use case when, you can also update in batches.

This article mainly talks about batch insert;

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 into orders at one time and generates 1,000 orders;

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);

This is probably what it means. It is a bit more troublesome to implement batch updates, so I won’t post them. The following It 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 '红枣' WHEN 1058 THEN '莲藕' WHEN 1055 THEN '洋葱'  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 )

The above is the detailed content of How to handle large amounts of data in PHP database. For more information, please follow other related articles on the PHP Chinese website!