<p>php实现大批量入库实战，前几年写的，现在看看也是有参考价值的，这块也考虑到了数据重复的处理方式</p><pre style="background-color:#262e37;color:#ffffff;font-family:'Consolas',monospace;font-size:11.3pt;"> $file <span style="color:#f92672;">= </span><span style="color:#66d9ef;font-style:italic;">fopen</span>(<span style="color:#e6db74;">'.'</span><span style="color:#f92672;">.</span>$upload_result[<span style="color:#e6db74;">'data'</span>][<span style="color:#ae81ff;">0</span>][<span style="color:#e6db74;">'url'</span>], <span style="color:#e6db74;">"r"</span>);<br/> $aid<span style="color:#f92672;">=</span><span style="color:#a6e22e;">session</span>(<span style="color:#a6e22e;">C</span>(<span style="color:#e6db74;">'USER_AUTH_KEY'</span>));<br/><span style="color:#66837f;background-color:#191f26;">//</span><span style="color:#66837f;background-color:#191f26;font-family:'宋体',monospace;">输出文本中所有的行，直到文件结束为止。</span><span style="color:#66837f;font-family:'宋体',monospace;"><br/></span><span style="color:#66837f;font-family:'宋体',monospace;"> </span>$k<span style="color:#f92672;">=</span><span style="color:#ae81ff;">0</span>;<br/> $ins_str<span style="color:#f92672;">=</span><span style="color:#e6db74;">''</span>;<br/> <span style="color:#f92672;">while</span>(<span style="color:#f92672;">! </span><span style="color:#66d9ef;font-style:italic;">feof</span>($file))<br/> {<br/> $vo<span style="color:#f92672;">= </span><span style="color:#66d9ef;font-style:italic;">fgets</span>($file);<span style="color:#66837f;background-color:#191f26;">//fgets()</span><span style="color:#66837f;background-color:#191f26;font-family:'宋体',monospace;">函数从文件指针中读取一行</span><span style="color:#66837f;font-family:'宋体',monospace;"><br/></span><span style="color:#66837f;font-family:'宋体',monospace;"> </span>$v<span style="color:#f92672;">=</span><span style="color:#66d9ef;font-style:italic;">explode</span>(<span style="color:#e6db74;">'-'</span>,$vo);<br/> <span style="color:#f92672;">if</span>(<span style="color:#f92672;">!empty</span>($v[<span style="color:#ae81ff;">0</span>])){<br/> $prod[<span style="color:#e6db74;">'title'</span>] <span style="color:#f92672;">= </span><span style="color:#66d9ef;font-style:italic;">trim</span>($v[<span style="color:#ae81ff;">0</span>]);<br/> <span style="color:#66837f;background-color:#191f26;">//15</span><span style="color:#66837f;background-color:#191f26;font-family:'宋体',monospace;">是父栏目</span><span style="color:#66837f;font-family:'宋体',monospace;"><br/></span><span style="color:#66837f;font-family:'宋体',monospace;"> </span><span style="color:#f92672;">if</span>($cid<span style="color:#f92672;">==</span><span style="color:#ae81ff;">15</span>){<br/> $cid<span style="color:#f92672;">=</span><span style="color:#66d9ef;font-style:italic;">strlen</span>($prod[<span style="color:#e6db74;">'title'</span>])<span style="color:#f92672;">-</span><span style="color:#ae81ff;">4</span>;<br/> $cid<span style="color:#f92672;">=</span><span style="color:#66d9ef;font-style:italic;">min</span>($cid,<span style="color:#ae81ff;">6</span>);<br/> }<br/> <span style="color:#66837f;background-color:#191f26;">//2064897854-40</span><span style="color:#66837f;background-color:#191f26;font-family:'宋体',monospace;">级</span><span style="color:#66837f;background-color:#191f26;">-350</span><span style="color:#66837f;background-color:#191f26;font-family:'宋体',monospace;">元</span><span style="color:#66837f;font-family:'宋体',monospace;"><br/></span><span style="color:#66837f;font-family:'宋体',monospace;"> </span>$prod[<span style="color:#e6db74;">'price'</span>] <span style="color:#f92672;">= </span><span style="color:#66d9ef;font-style:italic;">intval</span>(<span style="color:#66d9ef;font-style:italic;">trim</span>($v[<span style="color:#ae81ff;">2</span>]));<br/> $prod[<span style="color:#e6db74;">'level'</span>] <span style="color:#f92672;">= </span><span style="color:#66d9ef;font-style:italic;">intval</span>(<span style="color:#66d9ef;font-style:italic;">trim</span>($v[<span style="color:#ae81ff;">1</span>]));<br/> $prod[<span style="color:#e6db74;">'mibao'</span>] <span style="color:#f92672;">= </span><span style="color:#66d9ef;font-style:italic;">intval</span>(<span style="color:#66d9ef;font-style:italic;">trim</span>($mibao));<br/> $prod[<span style="color:#e6db74;">'isvip'</span>] <span style="color:#f92672;">= </span><span style="color:#66d9ef;font-style:italic;">intval</span>(<span style="color:#66d9ef;font-style:italic;">trim</span>($isvip));<br/> $prod[<span style="color:#e6db74;">'cid'</span>] <span style="color:#f92672;">= </span><span style="color:#66d9ef;font-style:italic;">trim</span>($cid);<br/> $prod[<span style="color:#e6db74;">'status'</span>] <span style="color:#f92672;">= </span><span style="color:#ae81ff;">1</span>;<br/> $prod[<span style="color:#e6db74;">'publishtime'</span>] <span style="color:#f92672;">= </span><span style="color:#66d9ef;font-style:italic;">time</span>();<br/> $prod[<span style="color:#e6db74;">'updatetime'</span>] <span style="color:#f92672;">= </span><span style="color:#66d9ef;font-style:italic;">time</span>();<br/> $prod[<span style="color:#e6db74;">'addtime'</span>] <span style="color:#f92672;">= </span><span style="color:#66d9ef;font-style:italic;">date</span>(<span style="color:#e6db74;">"Y-m-d H:i:s"</span>,<span style="color:#66d9ef;font-style:italic;">time</span>());<br/> $prod[<span style="color:#e6db74;">'aid'</span>] <span style="color:#f92672;">= </span>$aid;<br/> $prod[<span style="color:#e6db74;">'filename'</span>] <span style="color:#f92672;">= </span>$filename;<br/> <span style="color:#66837f;background-color:#191f26;">//insert into __YMPRODUCT__ (title,price,level,mibao,isvip,cid,status,publishtime,updatetime,addtime,aid,filename) values ($prod['title'],$prod['price'],$prod['level'],$prod['mibao'],$prod['isvip'],$prod['cid'],$prod['status'],$prod['publishtime'],$prod['updatetime'],$prod['addtime'],$prod['aid'],$prod['filename']) on duplicate key update price=values(price);</span><span style="color:#66837f;"><br/></span><span style="color:#66837f;"> </span>$k<span style="color:#f92672;">++</span>;<br/> <span style="color:#f92672;">if</span>($ins_str <span style="color:#f92672;">!= </span><span style="color:#e6db74;">''</span>){<br/> $ins_str<span style="color:#f92672;">.=</span><span style="color:#e6db74;">','</span>;<br/> }<br/> $ins_str<span style="color:#f92672;">.=</span><span style="color:#e6db74;">"('</span>{$prod[<span style="color:#e6db74;">'title'</span>]}<span style="color:#e6db74;">','</span>{$prod[<span style="color:#e6db74;">'price'</span>]}<span style="color:#e6db74;">','</span>{$prod[<span style="color:#e6db74;">'level'</span>]}<span style="color:#e6db74;">','</span>{$prod[<span style="color:#e6db74;">'mibao'</span>]}<span style="color:#e6db74;">','</span>{$prod[<span style="color:#e6db74;">'isvip'</span>]}<span style="color:#e6db74;">','</span>{$prod[<span style="color:#e6db74;">'cid'</span>]}<span style="color:#e6db74;">','</span>{$prod[<span style="color:#e6db74;">'status'</span>]}<span style="color:#e6db74;">','</span>{$prod[<span style="color:#e6db74;">'publishtime'</span>]}<span style="color:#e6db74;">','</span>{$prod[<span style="color:#e6db74;">'updatetime'</span>]}<span style="color:#e6db74;">','</span>{$prod[<span style="color:#e6db74;">'addtime'</span>]}<span style="color:#e6db74;">','</span>{$prod[<span style="color:#e6db74;">'aid'</span>]}<span style="color:#e6db74;">','</span>{$prod[<span style="color:#e6db74;">'filename'</span>]}<span style="color:#e6db74;">')"</span>;<br/> <span style="color:#f92672;">if</span>($k<span style="color:#f92672;">></span><span style="color:#ae81ff;">1000</span>){<br/><span style="color:#66837f;background-color:#191f26;">// sql</span><span style="color:#66837f;background-color:#191f26;font-family:'宋体',monospace;">拼接长度受</span><span style="color:#66837f;background-color:#191f26;">max_allowed_packet</span><span style="color:#66837f;background-color:#191f26;font-family:'宋体',monospace;">的值影响，值越大可拼接长度可越长，当然一次性可插入的数据就越大（个人未验证，但</span><span style="color:#66837f;background-color:#191f26;">sql</span><span style="color:#66837f;background-color:#191f26;font-family:'宋体',monospace;">语句的最大长度肯定说</span><span style="color:#66837f;background-color:#191f26;">sql</span><span style="color:#66837f;background-color:#191f26;font-family:'宋体',monospace;">数据库配置的影响）</span><span style="color:#66837f;font-family:'宋体',monospace;"><br/></span><span style="color:#66837f;font-family:'宋体',monospace;"> </span>$sql<span style="color:#f92672;">=</span><span style="color:#e6db74;">'insert into __PRODUCT__ (title,price,level,mibao,isvip,cid,status,publishtime,updatetime,addtime,aid,filename) values '</span><span style="color:#f92672;">.</span>$ins_str<span style="color:#f92672;">.</span><span style="color:#e6db74;">' on duplicate key update price=</span><span style="color:#66d9ef;font-style:italic;">IF</span><span style="color:#e6db74;">(price < </span><span style="color:#66d9ef;font-style:italic;">VALUES</span><span style="color:#e6db74;">(price), </span><span style="color:#66d9ef;font-style:italic;">VALUES</span><span style="color:#e6db74;">(price), price)'</span>;<br/> $Mprod<span style="color:#f92672;">-></span><span style="color:#a6e22e;">execute</span>($sql);<br/> $ins_str<span style="color:#f92672;">=</span><span style="color:#e6db74;">''</span>;<br/> $k<span style="color:#f92672;">=</span><span style="color:#ae81ff;">0</span>;<br/> }<br/><br/><br/> }<br/><br/> }<br/> <span style="color:#66d9ef;font-style:italic;">fclose</span>($file);<br/> <span style="color:#f92672;">if</span>($ins_str <span style="color:#f92672;">!= </span><span style="color:#e6db74;">''</span>){<br/> $sql<span style="color:#f92672;">=</span><span style="color:#e6db74;">'insert into __PRODUCT__ (title,price,level,mibao,isvip,cid,status,publishtime,updatetime,addtime,aid,filename) values '</span><span style="color:#f92672;">.</span>$ins_str<span style="color:#f92672;">.</span><span style="color:#e6db74;">' on duplicate key update price=</span><span style="color:#66d9ef;font-style:italic;">IF</span><span style="color:#e6db74;">(price < </span><span style="color:#66d9ef;font-style:italic;">VALUES</span><span style="color:#e6db74;">(price), </span><span style="color:#66d9ef;font-style:italic;">VALUES</span><span style="color:#e6db74;">(price), price)'</span>;<br/> $Mprod<span style="color:#f92672;">-></span><span style="color:#a6e22e;">execute</span>($sql);<br/> }<br/> <span style="color:#f92672;">exit</span>(<span style="color:#e6db74;">'{"error":0,"info":"</span><span style="color:#e6db74;font-family:'宋体',monospace;">入库成功</span><span style="color:#e6db74;">"}'</span>);</pre><p><br/></p>
相关文章
