2020-04-10更新
这两天发现前面使用的上传大量数据的方法,但是发现很多时候还是直接导致数据库奔溃,而且无法使用查询数据库中是否已经有数据了,所以代码还需要优化,经过群里热心大佬指点和多次尝试,终于找到了解决办法,处理方式入下:
首先,通过一次性读取数据库数据,把用来判断的键值拼接,用MD5转码之后,作为key键值存入数组;
然后通过phpexcel读取excel数据,同样把用来判断的拼接转码,然后通过isset($array[$key])来判断,如有,则通过continue跳过当前循环进入下一轮循环,如果没有,则把当前行数据全部拼接到$sql中;
为了防止一次性提交导致系统崩溃,设定每5000行提交一次数据库,这样就实现了大量数据判断提交功能了;
Sham用的是阿里云最低配入门云主机,1核2G 1M 10%突发性,虽然cpu和内存都彪红,但是至少提交个10万条数据还是跑的起来的,不错
以下是优化后的全部代码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
<?php header("Content-Type:text/html;charset=utf-8"); include_once("../public/dbcon.php"); $sql="select * from s_info"; $result = $con->query($sql); $begin = time(); $checkitems=array(); if($result){ while ($row = mysqli_fetch_array($result)) { $checkitem=md5($row["orderdate"].$row["carid"].$row["prdid"]); $checkitems[$checkitem]=1; } } /** //判断是否选择了要上传的表格 if ($_FILES['file']=="" ||$_FILES['file']==null ) { echo "{".'"code": 0,'. '"msg": "你没有选择表格"'."}"; exit(); } //获取表格的大小,限制上传表格的大小10M elseif ($_FILES['file']['size']>10*1024*1024) { echo "{".'"code": 0,'. '"msg": "上传失败,上传的表格不能超过10M的大小"'."}"; exit(); } //限制上传表格类型 //$file_type = $_FILES['file']['type']; //application/vnd.ms-excel 为xls文件类型 // if ($file_type!='application/vnd.ms-excel') { // echo "<script>alert('上传失败,只能上传excel2003的xls格式!');history.go(-1)</script>"; // exit(); // } **/ //判断表格是否上传成功 if (is_uploaded_file($_FILES['file']['tmp_name'])) { //以上三步加载phpExcel的类 require_once '../public/PHPExcel/PHPExcel.php'; require_once '../public/PHPExcel/PHPExcel/IOFactory.php'; require_once '../public/PHPExcel/PHPExcel/Reader/Excel2007.php'; //链接数据库 require_once '../public/dbcon.php'; set_time_limit(0); //设定超时时间 //设置缓存,防止溢出 $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp; $cacheSettings = array( 'memoryCacheSize' => '100MB'); PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings); $objReader = PHPExcel_IOFactory::createReader('Excel2007'); $objReader->setReadDataOnly(true); //只读取数据,不读取格式等 //接收存在缓存中的excel表格 $filename = $_FILES['file']['tmp_name']; $objPHPExcel = $objReader->load($filename); //$filename可以是上传的表格,或者是指定的表格 $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // 取得总行数 $highestColumn = $sheet->getHighestColumn(); // 取得总列数 $begin = time(); //开始时间 $donums =0; //开始计数 //循环读取excel表格,读取一条,插入一条 //j表示从哪一行开始读取 从第二行开始读取,因为第一行是标题不保存 //$a表示列号 for($j=2;$j<=$highestRow;$j++) { $id = ltrim($objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue(), "'");//获取B列的值 $orderdate = gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue())); //获取C列的值 $series = ltrim($objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue(), "'");//获取D列的值 $carid = ltrim($objPHPExcel->getActiveSheet()->getCell("D".$j)->getValue(), "'"); $prdtype = ltrim($objPHPExcel->getActiveSheet()->getCell("E".$j)->getValue(), "'");//获取D列的值 $prdid = ltrim($objPHPExcel->getActiveSheet()->getCell("F".$j)->getValue(), "'");//获取D列的值 $numbers = ltrim($objPHPExcel->getActiveSheet()->getCell("G".$j)->getValue(), "'");//获取D列的值 $shopname = ltrim($objPHPExcel->getActiveSheet()->getCell("H".$j)->getValue(), "'");//获取D列的值 $vendor = ltrim($objPHPExcel->getActiveSheet()->getCell("I".$j)->getValue(), "'");//获取D列的值 $remark = ltrim($objPHPExcel->getActiveSheet()->getCell("J".$j)->getValue(), "'");//获取D列的值 $checkitem = md5($orderdate.$carid.$prdid); if(isset($checkitems[$checkitem])){ continue; }else{ $sqls .= "('$orderdate','$series','$carid','$prdtype','$prdid','$numbers','$shopname','$vendor','$remark'),"; $donums +=1; if($j%5000==0){ //每5000条提交一次数据库 $sql = 'INSERT INTO `s_info` (`orderdate`, `series`, `carid`, `prdtype`, `prdid`, `numbers`, `shopname`, `vendor`, `remark`) value'.rtrim($sqls, ","); $result = $con->query($sql); $sqls =''; unset($sql); $sql=""; } } } //把剩余的一次性提交 $sql = 'INSERT INTO `s_info` (`orderdate`, `series`, `carid`, `prdtype`, `prdid`, `numbers`, `shopname`, `vendor`, `remark`) value'.rtrim($sqls, ","); $result = $con->query($sql); unset($sql); $end = time(); //结束时间 $usedtime=$end-$begin; //计算耗时 $msg="提交了".$donums." 条,耗时".$usedtime." 秒"; echo "{".'"code": 0,'. '"msg": "'.$msg.'"'."}"; } ?> |
今天Sham测试了一次性导入上万乃至十多万条数据到mysql,使用的还是phpexcel导入,使用原来的办法导入时,服务器CPU和内存直接飙到100%,而且花了很长时间最后还是导入失败,于是上网查大神的分享,貌似被我找到了,以下是代码(phpexcel对应代码已省略)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
$highestRow = $sheet->getHighestRow(); // 取得总行数 $sqls =''; for($j=2;$j<=$highestRow;$j++) { $id = $objPHPExcel->getActiveSheet()->getCell("A".$j)->getValue();//获取B列的值 $content = $objPHPExcel->getActiveSheet()->getCell("B".$j)->getValue();//获取C列的值 $mydesc = $objPHPExcel->getActiveSheet()->getCell("C".$j)->getValue();//获取D列的值 $sqls .= "(null,'$content','$mydesc'),"; if($j%$highestRow==0){ //当$j除以总行数的余数为0时,执行 $sql = 'INSERT INTO `test` (`id`,`content`,`mydesc`) value'.rtrim($sqls, ","); mysqli_query($con,$sql); $sqls =''; unset($sql); } } |
不过目前Sham还没搞懂这样做是为什么,慢慢研究吧
最新评论