php 结合缓冲区读取excel数据并批量导入mysql_php批量读取txt文本,写入mysql-优快云博客

本文链接：https://blog.youkuaiyun.com/yun__yang/article/details/51201720

在工作中，很多时候需要把excel中的数据读取出来，导入到mysql中，以前的代码，都是通过 Spreadsheet_Excel_Reader插件直接取出来，进行for循环，组合成一个长的sql语句，这样进行插入，但是这样的导入有不足之处就是如果导入的数据量庞大，那么会造成长语句超出sql最大限制。并且执行时间超长，会超出php脚本执行时间下面贴一段原先的代码。

require_once 'Excel/reader.php';//调用Reader,引用地址可以任意，正确即可
$data = new Spreadsheet_Excel_Reader(); //创建 Reader
$data->setOutputEncoding('utf-8');//设置文本输出编码
$data->read($uploadfile);//读取Excel文件

$insert = '';

for ($i = 0; $i < $data->sheets[0]['numRows']; $i++) {

$insert .= "('".$data->sheets[0]['cells'][$i][1]."','".$data->sheets[0]['cells'][$i][3]."-".$data->sheets[0]['cells'][$i][4]."','".$data->sheets[0]['cells'][$i][7]."--".$data->sheets[0]['cells'][$i][6]."',1,'".$data->sheets[0]['cells'][$i][2]."','".$datetime."','".$data->sheets[0]['cells'][$i][8]."','".$data->sheets[0]['cells'][$i][5]."','".$data->sheets[0]['cells'][$i][8]."'),";

用过这款插件的同学应该知道，$data->sheets[0]['numRows']即php读取excel中的记录数，而$data->sheets[0]['cells']为php读取出来的数据,这样调取其实是可以的，但是前提是数据量不大。

数据量稍微大一点就提示以下错误，这是因为发送的SQL语句太长，以致超过了max_allowed_packet的大小，这种情况，你只要修改my.cnf，加大max_allowed_packet的值即可。但是这种虽然可以解决大sql插入问题，但是程序运行的时间也响应的增加了，比如：读取一个5000记录数的excel并插入，居然使用了超过20秒时间。这意味着如果采用此种方法，还需要设置set_time_limit(0);

但是大多数的情况下，不可能频繁的使用小excel文件进行导入，也太繁琐，通过php的前期处理，将大型的sql语句拆分为n多符合条件的sql语句，配合缓冲区，这样做的好处就是：不用考虑因为sql语句太长而造成执行时间超出php限定时间，以及语句太长造成mysql报错。

$len = $data->sheets[0]['numRows'];
		$datacells = $data->sheets[0]['cells'];
		unset($data);
		//以2000条为分界
		$lun = $len / 2000;
		// echo $len.'<br />';
		// echo $lun.'<br />';
		$num = intval($len % 2000);
		if($num == 0){
			$lun = $lun;
		}else{
			if($lun>0){
				$lun = floor($lun);
			}else{
				$lun = 0;
			}
		}
		// echo $lun.'<br />';
		// echo $num;exit;
		set_time_limit(0);
		//插入一条excel记录
		mysql_query("INSERT INTO `excel` (`id`, `oldname`, `newname`, `size`, `time`) VALUES (NULL, '$file', '$uploadfile', '$len','$exceltime');");  
		$insert_id=mysql_insert_id();
		if($lun >= 1){ 
			for($j = 0;$j < $lun;$j++){
				ob_end_clean();
				$insert = '';
				for($i = 0;$i <= 2000;$i++){
					$key=$i+$j*2000;
					if (strlen($datacells[$key][5])>0){
						$datetime=$datacells[$key][5];
					}else{
						$datetime=date('Y-m-d H:i:s');
					}
					// if($datacells[$key][3]){
						$insert .= "('".$datacells[$key][1]."','".$datacells[$key][3]."-".$datacells[$key][4]."','".$datacells[$key][7]."--".$datacells[$key][6]."',1,'".$datacells[$key][2]."','".$datetime."','".$datacells[$key][8]."','".$datacells[$key][5]."','".$datacells[$key][8]."','".$insert_id."'),"; 
					// }
				}
				$insert=trim($insert,',');
				$sql = "INSERT INTO message_bak (tel, addr, y_title, bs, wz, timeadd,ip,uid,telzt,excel_id) VALUES".$insert;
                               //echo $sql;
                                $res != mysql_query($sql);
				if (!$res){
					$msg="SQL语句执行错误".$sql;
				}	
				flush();
			}
		}
                if($lun > 0 && $num > 0){
                    unset($sql);
                    insert = '';
                    for($i = 0;$i <= $num;$i++){
                       $key=($lun-1)*2000+$i;
                       if (strlen($datacells[$key][5])>0){
                          $datetime=$datacells[$key][5];
                       }else{
                          $datetime=date('Y-m-d H:i:s');
                       }
                       $insert .= "('".$datacells[$key][1]."','".$datacells[$key][3]."-".$datacells[$key][4]."','".$datacells[$key][7]."--".$datacells[$key][6]."',1,'".$datacells[$key][2]."','".$datetime."','".$datacells[$key][8]."','".$datacells[$key][5]."','".$datacells[$key][8]."','".$insert_id."'),"; 
                    }
                    $insert=trim($insert,',');
                    $sql = "INSERT INTO message_bak (tel, addr, y_title, bs, wz, timeadd,ip,uid,telzt,excel_id) VALUES".$insert;
                    $res = mysql_query($sql);

                }elseif($num > 0){
                    unset($sql);
                    insert = '';
                    for($i = 0;$i <= $num;$i++){
                       $key=$i;
                       if (strlen($datacells[$key][5])>0){
                          $datetime=$datacells[$key][5];
                       }else{
                          $datetime=date('Y-m-d H:i:s');
                       }
                       $insert .= "('".$datacells[$key][1]."','".$datacells[$key][3]."-".$datacells[$key][4]."','".$datacells[$key][7]."--".$datacells[$key][6]."',1,'".$datacells[$key][2]."','".$datetime."','".$datacells[$key][8]."','".$datacells[$key][5]."','".$datacells[$key][8]."','".$insert_id."'),"; 
                    }
                    $insert=trim($insert,',');
                    $sql = "INSERT INTO message_bak (tel, addr, y_title, bs, wz, timeadd,ip,uid,telzt,excel_id) VALUES".$insert;
                    $res = mysql_query($sql);
                }

这里将$data中的数据提出并且销毁$data了。目的是要代码简便点，不过实际环境中无需如此，目前通过上面的程序，导入一个10000条的excel文件，仅需要

6秒。当然如果取消对变量的操作，再优化优化程序(缓冲区的使用是为了实时显示出来每次执行的sql语句，如果不需要这种做法，当然可以放弃使用缓冲区)，与excel类，速度应该还会更快一点，以下为程序执行时间对比：