PHP学习笔记-mysql快速插入上万条数据-Sham@双目瞿

2020-04-10更新

这两天发现前面使用的上传大量数据的方法，但是发现很多时候还是直接导致数据库奔溃，而且无法使用查询数据库中是否已经有数据了，所以代码还需要优化，经过群里热心大佬指点和多次尝试，终于找到了解决办法，处理方式入下：

首先，通过一次性读取数据库数据，把用来判断的键值拼接，用MD5转码之后，作为key键值存入数组；

然后通过phpexcel读取excel数据，同样把用来判断的拼接转码，然后通过isset($array[$key])来判断，如有，则通过continue跳过当前循环进入下一轮循环，如果没有，则把当前行数据全部拼接到$sql中；

为了防止一次性提交导致系统崩溃，设定每5000行提交一次数据库，这样就实现了大量数据判断提交功能了；

Sham用的是阿里云最低配入门云主机，1核2G 1M 10%突发性，虽然cpu和内存都彪红，但是至少提交个10万条数据还是跑的起来的，不错

以下是优化后的全部代码

<?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.'"'."}"; 
	}
?>

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对应代码已省略）

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

}

$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还没搞懂这样做是为什么，慢慢研究吧

PHP学习笔记-mysql快速插入上万条数据

2020-04-10更新

相关推荐

评论抢沙发

最新评论

热门文章

热门标签

网站统计

如果你觉得文章好，请赏1杯速溶咖啡给Sham吧！

微信扫一扫打赏

2020-04-10更新

相关推荐

评论 抢沙发

最新评论

热门文章

热门标签

网站统计

如果你觉得文章好，请赏1杯速溶咖啡给Sham吧！

微信扫一扫打赏

评论抢沙发