php 结合缓冲区读取excel数据并批量导入mysql

在工作中，很多时候需要把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类，速度应该还会更快一点，以下为程序执行时间对比：

 5.7213270664215   单独使用excel类读取数据花费的时间

 6.3743650913239   使用excel类读取数据以及插入数据库所使用的时间

    由此可以看出，大部分时间是花费在了读取excel上面，使用缓冲区插入msyql还是比较可取的，有个小提示，每一次使用缓冲区之后一定要进行 刷新操作

以上只是我的测试代码，具体的sql需要根据各位同学们的需求进行改写，而且上面的代码增加了一个关于导入数据的字典表，即实时导入数据的操作记录，不需要的也可以删除