使用PHP 5.0 轻松解析XML文档(6)

当有业务需求需要一次性循环n条数据,插入或更新数据库时,如果单纯的循环,插入/更新,会消耗太多的数据库资源

下面是通过createNode()函数,添加商品, 然后显示添加后的结果
<?xml version=”1.0″ encoding=”GB2312″ ?>
<shop>
<name>华联</name>
<address>北京长安街-9999号</address>
<desc>连锁超市</desc>
<telphone>123456789</telphone>
<cat id=”food”>
<goods id=”food11″>
<name>food11</name>
<price>12.90</price>
<leaveword author=”hahawen”
date=”2004-12-05″>这个商品不错</leaveword>
</goods>
<goods id=”food12″>
<name>new food12</name>
<price>22.10</price>
</goods>
<goods id=”food13″>
<name>food13</name>
<price>100</price>
</goods>
</cat>
<cat>
<goods id=”tel21″>
<name>tel21</name>
<price>1290</price>
</goods>
</cat>
<cat id=”coat”>
<goods id=”coat31″>
<name>coat31</name>
<price>112</price>
</goods>
<goods id=”coat32″>
<name>coat32</name>
<price>45</price>
</goods>
</cat>
<special id=”hot”>
<goods>
<name>hot41</name>
<price>99</price>
</goods>
</special>
</shop>
  下面是通过removeNode()函数,删除商品, 然后显示删除后的结果
<?xml version=”1.0″ encoding=”GB2312″ ?>
<shop>
<name>华联</name>
<address>北京长安街-9999号</address>
<desc>连锁超市</desc>
<telphone>123456789</telphone>
<cat id=”food”>
<goods id=”food11″>
<name>food11</name>
<price>12.90</price>
<leaveword author=”hahawen”
date=”2004-12-05″>这个商品不错</leaveword>
</goods>
<goods id=”food13″>
<name>food13</name>
88bf必发娱乐,<price>100</price>
</goods>
</cat>
<cat>
<goods id=”tel21″>
<name>tel21</name>
<price>1290</price>

一下是一种简单的解决方案

数据库的insert 是可以批量更新的,当有大量数据循环insert时,可以将数据先保留不执行插入命令,到最后一条时一次性插入,例如tp的addAll()方法;

数据库的update 如果使用case when 的话,也是可以批量更新的,本人在百度上找到了一个基于tp的saveAll()方法,用于更新数据

本文主要讲关于批量insert;

例:

生成一个订单

正常情况的语句为:

INSERTINTOorder(`goods_id`,`num`,`price`)VALUES(1,1, ```'10.00');//封装成函数functionadd_order($goods_id,$num,$price){$db->query(``` "INSERTINTOorder(`goods_id`,`num`,`price`)VALUES($goods_id,$num,$price)" ```);}`

假设有一个用户,一次性将购物车的1000个商品结算成订单,生成1000个订单时;

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

大概意思就是这样了,批量更新实现比较麻烦一点,就不发了,以下是批量更新的sql执行语句

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-0921:40:06'``WHEN``1058``THEN``'2017-12-0921:40:06'``WHEN``1055``THEN``'2017-12-0921:40:06'``END``WHERE``id``IN``(1041,1058,1055)

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 )

附带基于tp的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%uTHEN'%s'"``,``$data``[``$pk``],``$value``);````}````}````}````foreach``(``$lists``as``$key``=>``$value``){````$sql``.=sprintf(``` "%s=CASE%s%sEND," ```,``$key``,``$pk``,``$value``);````}````$sql``=sprintf(``'UPDATE__%s__SET%sWHERE%sIN'``,``strtoupper``(``$model``),rtrim(``$sql``,``','``),``$pk``,implode(``','``,``$ids``));````return``M()->execute(``$sql``);``}