The insert of the database can be updated in batches, when there are a large number of data loops insert, the data can be retained without executing the INSERT command, to the last one-time insertion, such as the AddAll () method of TP;
Database update If you use case when, you can also batch update, I found on Baidu on a TP-based SaveAll () method for updating data
This article mainly talk about bulk insert;
Cases:
Generate an Order
The normal statement is:
INSERT into order (' goods_id ', ' num ', ' price ') VALUES (1, 1, ' 10.00 ');//encapsulated into function Add_order ($goods _id, $num, $price) {$ Db->query ("INSERT into order (' goods_id ', ' num ', ' price ') VALUES ($goods _id, $num, $price)");}
Suppose that there is a user, a one-time shopping cart 1000 items settled into an order, generate 1000 orders;
for ($i =0; $i <1000; $i + +) {$db->query ("INSERT into order (' goods_id ', ' num ', ' price ') VALUES ($goods _id, $num, $price )");} This will result in server resource consumption too large, the site is stuck//so we can $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 what this is about, the bulk update implementation is a bit cumbersome, not sent, the following is a batch update SQL execution statement
UPDATE tiyan.dm_user_cupboard SET ' res_id ' = case ' id ' when 1041 then ' 1 ' if 1058 then ' 1 ' when 1055 then ' 1 ' END, ' food_ Code ' = Case ' id ' when 1041 then ' the ' 1058 ' when the 1055 Then ' "" "END, ' food_name ' = case ' id ' when 1041 then ' Red Date ' 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 (104 1,1058,1055)
With TP-based SaveAll ()
//Bulk Update public Function SaveAll ($datas, $model) {($model | | $model = $this TableName); $model =empty ($model)? $this->name: $model; $sql = "; SQL $lists = []; Recordset $lists $PK = $this->getpk ();//Get Primary key 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, $valu e); } $sql = sprintf (' UPDATE __%s__ SET%s WHERE%s in (%s) ', Strtoupper ($model), RTrim ($sql, ', '), $PK, implode (', ', $ids)); Return M ()->execute ($sql);}