前言
近期在MySQL报表开发过程中，需要爬取多API返回JSON数据，然后插入到数据库中。因不同API的返回时间、返回数据、返回格式、插入数据表等因素各不相同。如果前期架构不完善，会导致服务器的直接崩溃，为此，做了下php+mysql的批量插入大数据量的测试工作。
一、PHP计算程序执行时间
microtime() 函数，返回当前 Unix 时间戳的微秒数，都是以秒为单位返回。
执行代码:
$start_time = microtime(); /* * 执行程序代码; * */ $end_time = microtime(); echo '执行时间为：' . ($end_time - $start_time) . ' s';
原理：分别记录函数开始时间和结束时间，然后时间差就是函数执行的时间。
二、Mysql批量插入数据
1.INSERT INTO 语句
- 向表格中插入新的行
INSERT INTO 表名称 VALUES (值1, 值2,....)
- 指定所要插入数据的列
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
2.批量插入大数据
- Mysql插入少量数据的时候，一般用for循环：读取固定结构化的数据或JSON数据，通过遍历的方式插入；
上图是一次性插入3000条的测试数据。
2.1 使用循环$sql的方式
- MySQL使用insert语句进行合并插入的，比如INSERT INTO user_info (name, age) VALUES (‘name1’, 18), (‘name2’, 19);表示一次插入两条数据。
执行代码：
$sql = ' INSERT INTO ' . $db->table('log') . ' (user_name,logs,equipment,log_time,log_ip) VALUES ("' . $data[$i][0] . '","' . $data[$i][1] . '","' . $data[$i][2] . '","' . $data[$i][3] . '","' . $data[$i][4] . '")'; $db->query($sql);
$sql = ' INSERT INTO ' . $db->table('log') . ' VALUES ("' . ($i + 1) . '","' . $data[$i][0] . '","' . $data[$i][1] . '","' . $data[$i][2] . '","' . $data[$i][3] . '","' . $data[$i][4] . '")'; $db->query($sql);
执行结果：不仅未完成数据的全部插入，直接将服务器拖成了500 Internal Server Error 。
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator at admin@example.com to inform them of the time this error occurred, and the actions you performed just before this error.
More information about this error may be available in the server error log.
Additionally, a 500 Internal Server Error error was encountered while trying to use an ErrorDocument to handle the request.
2.2 循环(值1, 值2,…)的语句
- 执行效率奇高；
- 对服务器的压力可以忽略不计；
- 批量插入大数据量MySQL的优化，推荐本方案；
- 大数据量插入mysql的语句(执行时间)★★★
//批量添加数据； public function addData() { global $db; dbc(); $start_time = microtime();//开始时间 require_once 'data.php';//默认数据 $sql = 'INSERT INTO ' . $db->table('log') . ' (user_name,logs,equipment,log_time,log_ip) VALUES '; for ($i = 0; $i < count($data); $i++) { $itemStr = '("'; $itemStr .= $data[$i][0] . '","' . $data[$i][1] . '","' . $data[$i][2] . '","' . $data[$i][3] . '","' . $data[$i][4]; $itemStr .= '"),'; //echo $itemStr; $sql .= $itemStr; } $sql = rtrim($sql, ',');//去除最后一个逗号，并且加上结束分号 $sql .= ';'; $db->query($sql); $end_time = microtime();//结束时间 $res["time"] = '执行时间:' . ($end_time - $start_time) . 's'; $res["data"] = "数据录入完毕."; die(json_encode_lockdata($res)); }
- 实际开发取消时间验证★★★
//批量添加数据； public function addData() { global $db; dbc(); require_once 'data.php';//默认数据 $sql = 'INSERT INTO ' . $db->table('log') . ' (user_name,logs,equipment,log_time,log_ip) VALUES '; for ($i = 0; $i < count($data); $i++) { $itemStr = '("'; $itemStr .= $data[$i][0] . '","' . $data[$i][1] . '","' . $data[$i][2] . '","' . $data[$i][3] . '","' . $data[$i][4]; $itemStr .= '"),'; //echo $itemStr; $sql .= $itemStr; } $sql = rtrim($sql, ',');//去除最后一个逗号，并且加上结束分号 $sql .= ';'; $db->query($sql); $res["data"] = "数据录入完毕."; die(json_encode_lockdata($res)); }
- 拼接语句
for ($i = 0; $i < count($data); $i++) { $itemStr = '('; $itemStr .= $data[$i][0] . ',' . $data[$i][1] . ',' . $data[$i][2] . ',' . $data[$i][3] . ',' . $data[$i][4]; $itemStr .= '),'; //echo $itemStr; $sql .= $itemStr; }
2.3测试过程出现的错误
“Unknown column ‘xxx’ in 'where clause
主要意思就是这个字段不存在，但实际情况有可能插入字段符合规范导致的。比如：username字段是varchar类型，而变量解析之后由于不带单引号，在PHP里面虽然可以当成字符串用，但执行sql语句就不符合Mysql的规范了。
错误的写法1：
$sql = ' INSERT INTO ' . $db->table('log') . ' (user_name,logs,equipment,log_time,log_ip) VALUES (' . $data[$i][0] . ',' . $data[$i][1] . ',' . $data[$i][2] . ',' . $data[$i][3] . ',' . $data[$i][4] . ')'; $db->query($sql);
错误的写法2：
$sql = ' INSERT INTO ' . $db->table('log') . ' (user_name,logs,equipment,log_time,log_ip) VALUES ($data[$i][0],$data[$i][1],$data[$i][2],$data[$i][3],$data[$i][4])'; $db->query($sql);
SQL语句错误号：Column count doesn’t match value count at row 1
SQL语句错误号：You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ‘.162.76),(NO.0000007,10,LOCKDATAV机器人模拟数据-SHELL,1681302901,无法’ at
line 1
上面的错误，基本上都是因文本没有加引号导致的。
三、实战PHPExcel批量导入大数据量优化
循环读取excel表格,读取一条,插入一条，需要每次都连接数据库，执行效率比较低下。
//循环读取excel表格,读取一条,插入一条 //j表示从哪一行开始读取 从第二行开始读取，因为第一行是标题不保存 //$a表示列号 for ($j = 2; $j <= $highestRow; $j++) { $user_province = $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue(); $user_city = $objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue(); $user_area = $objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue(); $user_depart = $objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue(); $user_titles = $objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue(); $user_name = $objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue(); $user_phone = $objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue(); $user_pwd = $objPHPExcel->getActiveSheet()->getCell("H" . $j)->getValue(); $user_auth = $objPHPExcel->getActiveSheet()->getCell("I" . $j)->getValue(); //判断手机号重名; $row = $db->fetch('user', 'user_phone', array('user_phone' => trim($user_phone)), ' user_id DESC'); if ($row) { $res['code'] = '0'; $res['msg'] = $user_phone . '系统已存在，删除后重新导入'; die(json_encode_lockdata($res)); } //数据入库; if ($user_phone) { $db->insert('user', array('user_province' => $user_province, 'user_city' => $user_city, 'user_area' => $user_area, 'user_depart' => $user_depart, 'user_titles' => $user_titles,'user_name' => $user_name, 'user_phone' => $user_phone, 'user_pwd' => md5($user_pwd), 'user_auth' => $user_auth)); } } $res['code'] = '1'; $res['msg'] = '文件已导入数据库！'; die(json_encode_lockdata($res));
升级后的代码：
global $db; dbc(); $sql = 'INSERT INTO ' . $db->table('user') . ' (user_province,user_city,user_area,user_depart,user_titles,user_name,user_phone,user_pwd,user_auth) VALUES '; for ($j = 2; $j <= $highestRow; $j++) { $user_province = $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue(); $user_city = $objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue(); $user_area = $objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue(); $user_depart = $objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue(); $user_titles = $objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue(); $user_name = $objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue(); $user_phone = $objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue(); $user_pwd = $objPHPExcel->getActiveSheet()->getCell("H" . $j)->getValue(); $user_auth = $objPHPExcel->getActiveSheet()->getCell("I" . $j)->getValue(); //判断手机号重名; $row = $db->fetch('user', 'user_phone', array('user_phone' => trim($user_phone)), ' user_id DESC'); if ($row) { $res['code'] = '0'; $res['msg'] = $user_phone . '系统已存在，删除后重新导入'; die(json_encode_lockdata($res)); } //数据入库; $itemStr = '("'; $itemStr .= $user_province . '","' . $user_city . '","' . $user_area . '","' . $user_depart . '","' . $user_titles . '","' . $user_name . '","' . $user_phone . '","' . md5($user_pwd) . '","' . $user_auth; $itemStr .= '"),'; $sql .= $itemStr; } $sql = rtrim($sql, ',') . ";"; $db->query($sql); $res['code'] = '1'; $res['msg'] = '文件已导入数据库！'; die(json_encode_lockdata($res)); }
如在上传过程过程中出现错误，请务必核对插入列数是否前后一致即可。
@漏刻有时
