php执行语句在MySQL批量插入大数据量的解决方案及计算程序执行时间(大数据量、MySQL语句优化)

MySQL里批量插入大数据的解决方案

  • 前言
  • 一、PHP计算程序执行时间
  • 二、Mysql批量插入数据
    • 1.INSERT INTO 语句
    • 2.批量插入大数据
      • 2.1 使用循环$sql的方式
      • 2.2 循环(值1, 值2,....)的语句
      • 2.3测试过程出现的错误
  • 三、实战PHPExcel批量导入大数据量优化

前言

近期在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));
    }

如在上传过程过程中出现错误，请务必核对插入列数是否前后一致即可。

@漏刻有时

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/15311.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【微服务笔记22】微服务组件之Sentinel控制台的使用（Sentinel Dashboard）

【微服务笔记22】微服务组件之Sentinel控制台的使用（Sentinel Dashboard）

这篇文章&#xff0c;主要介绍微服务组件之Sentinel控制台的使用&#xff08;Sentinel Dashboard&#xff09;。 目录 一、Sentinel控制台 1.1、下载Dashboard控制台 1.2、搭建测试工程 &#xff08;1&#xff09;引入依赖 &#xff08;2&#xff09;添加配置信息 &#…
阅读更多...
R语言的基本数学运算

R语言的基本数学运算

目录 一、对象命名原则 二、基本数学运算 2.1 四则运算 2.2 余数和整除 2.3 次方或平方根 2.4 绝对值 2.5 exp()与对数 2.6 科学符号e 2.7 圆周率与三角函数 2.8 四舍五入函数 2.9 近似函数 2.10 阶乘 三、R语言控制运算的优先级 四、无限大 五、非数字&#xf…
阅读更多...
3.7 Linux shell脚本编程（分支语句、循环语句）

3.7 Linux shell脚本编程（分支语句、循环语句）

目录 分支语句&#xff08;对标C语言中的if&#xff09; 多路分支语句&#xff08;对标C语言中的swich case&#xff09; 分支语句&#xff08;对标C语言中的if&#xff09; 语法结构: if 表达式 then 命令表 fi 如果表达式为真, 则执行命令表中的命令; 否则退出if语句,…
阅读更多...
Linxu下性能指标采集工具之nmon工具的使用

Linxu下性能指标采集工具之nmon工具的使用

前言 近期在测试JefLogTail&#xff0c;由于JefLogTail使用的是轮询的方式来监听文件夹&#xff0c;所以对cpu的消耗可能会高一些&#xff0c;所以在测试的时候着重关注CPU,Linux下查看CPU信息一般采用top命令来实时观察&#xff0c;但是这种对于只是通过观察数据的变化来评估…
阅读更多...
Anaconda,CUDA注意事项

Anaconda,CUDA注意事项

2. 呜呜呜&#xff01;&#xff01;&#xff01;用别人的环境&#xff0c;如果他是非GPU版本的TF&#xff0c;你把非GPU版本的TF卸载后安装GPU版本的TF他也装不上。。。会默认给你装非GPU版本的TF&#xff01;&#xff01;&#xff01;大坑比&#xff01;&#xff01;&#xf…
阅读更多...
streamlit （python构建web可视化框架）笔记

streamlit （python构建web可视化框架）笔记

文章目录 一、安装使用streamlit二、streamlit使用1.展示和数据样式2.dataframe()生成交互表和table()方法生成静态表3.绘制折线图4.绘制地图5.一些组件slider()滑动条 checkbox()确认框 selectbox()选择器6.侧边栏7.布局分列8.多页 三、Steamlit可视化简单应用--冒泡排序可视化…
阅读更多...
科学防雷接地和雷电防护方案

科学防雷接地和雷电防护方案

说到防雷&#xff0c;可能不少人首先会想到避雷针&#xff0c;而“避雷针”这一概念&#xff0c;很容易让大家对防雷的概念造成误解。 误解1: 避雷针是用来“避雷”的。 其实&#xff0c;避雷针的学名叫“接闪器”&#xff0c;不是用来“避开雷击”的&#xff0c;而是用来“迎…
阅读更多...
传统机器学习(七)支持向量机(2)sklearn中的svm

传统机器学习(七)支持向量机(2)sklearn中的svm

传统机器学习(七)支持向量机(2)sklearn中的svm 2 sklearn中的svm 2.1 LinearSVC及SVC参数详解 2.1.1 SVC参数 class sklearn.svm.SVC(*,C1.0, kernelrbf, degree3, gammascale, coef00.0, shrinkingTrue, probabilityFalse, tol0.001, cache_size200, class_weightNone, ve…
阅读更多...
为什么企业要做大规模敏捷？

为什么企业要做大规模敏捷？

背景 软件工程里一个重要的指标就是“可用的软件”&#xff0c;敏捷宣言里也同样告诉我们“工作的软件高于详尽的文档”&#xff0c;那“可用的软件”、“工作的软件”意味着什么呢&#xff1f;在我的理解里&#xff0c;可以经历用户 “千锤百炼”的软件就是一个“可用的软件”…
阅读更多...
这些vue基本语法，你掌握了吗

这些vue基本语法，你掌握了吗

文章目录 一、 vue 项目重点概念介绍1. 单页面应用程序2. 单文件组件3.数据驱动视图 二、 vue 基本结构1、template2、script3、style 三、 vue 常用指令介绍1、内容渲染指令&#xff08;1&#xff09;插值表达式 {{xxx}} —常用方式&#xff08;2&#xff09;v-text&#xff0…
阅读更多...
912. 排序数组

912. 排序数组

1.题目&#xff1a; 2.我的代码&#xff1a; C语言&#xff1a; /*** Note: The returned array must be malloced, assume caller calls free().*/ int* sortArray(int* nums, int numsSize, int* returnSize) {//希尔排序int gap numsSize;//多次预排while (gap > 1) {/…
阅读更多...
Ansys Zemax | 如何模拟双折射偏振器件

Ansys Zemax | 如何模拟双折射偏振器件

这篇文章介绍了什么是双折射现象、如何在OpticStudio中模拟双折射 (birefringence)、如何模拟双晶体的双折射偏振器以及如何计算偏振器的消光比。&#xff08;联系我们获取文章附件&#xff09; 什么是双折射现象 一般的光学材料都是均匀的各向同性的&#xff0c;也就是说无论光…
阅读更多...
STM32物联网实战开发（6）——PWM驱动LED灯

STM32物联网实战开发（6）——PWM驱动LED灯

PWM驱动LED灯 之前是使用标准库函数配置引脚输出PWM控制呼吸灯&#xff0c;因为开发板上的蜂鸣器是有源的&#xff0c;所以这次还是用来确定LED灯&#xff0c;这次使用的是HAL库&#xff0c;用CubeMX软件初始化PWM功能 PWM输出原理 Period&#xff1a;周期&#xff0c;单位是秒…
阅读更多...
语音处理加窗分帧

语音处理加窗分帧

语音处理加窗分帧 一、分帧 语音数据和视频数据不同&#xff0c;本没有帧的概念&#xff0c;但是为了传输与存储&#xff0c;我们采集的音频数据都是一段一段 的。为了程序能够进行批量处理&#xff0c;会根据指定的长度(时间段或者采样数)进行分段&#xff0c;结构化为我们编程…
阅读更多...
Javaweb | 转发、重定向

Javaweb | 转发、重定向

&#x1f497;wei_shuo的个人主页 &#x1f4ab;wei_shuo的学习社区 &#x1f310;Hello World &#xff01; 转发 转发与页面跳转 转发 转发的作用在服务器端&#xff0c;将请求发送给服务器上的其他资源&#xff0c;以共同完成一次请求的处理 页面跳转 使用forward跳转时&am…
阅读更多...
拍卖小程序开发：从需求分析到设计实现

拍卖小程序开发：从需求分析到设计实现

在当今数字时代&#xff0c;拍卖小程序已经成为了一个重要的销售和交易工具。拍卖小程序的开发不仅能够提供高效的销售渠道&#xff0c;还能够为用户提供全新的购物体验。因此&#xff0c;开发一个拍卖小程序成为了许多商家的首要任务。 拍卖小程序的开发可以帮助商家拓展销售…
阅读更多...
Linux下实现共享内存的两种机制(附源码)

Linux下实现共享内存的两种机制(附源码)

START Hello 大家好。 今天来讲一下Linux进程通信中重要的通信方式&#xff1a;共享内存作为Linux软件开发攻城狮&#xff0c;进程间通信是必须熟练掌握的重要技能&#xff0c;而共享内存是在程序开发中常用的也是重要的一种进程间通信方式。 下面我们就来聊一聊Linux下进程间…
阅读更多...
pytest自动化框架之allure测试报告的用例描述设置

pytest自动化框架之allure测试报告的用例描述设置

allure测试报告的用例描述相关方法&#xff1b;如下图 allure标记用例级别severity 在做自动化测试的过程中&#xff0c;测试用例越来越多的时候&#xff0c;如果执行一轮测试发现了几个测试不通过&#xff0c;我们也希望能快速统计出缺陷的等级。 pytest结合allure框架可以对…
阅读更多...
每日学术速递4.26

每日学术速递4.26

CV - 计算机视觉 | ML - 机器学习 | RL - 强化学习 | NLP 自然语言处理 Subjects: cs.CV 1.AutoNeRF: Training Implicit Scene Representations with Autonomous Agents 标题&#xff1a;AutoNeRF&#xff1a;使用自主代理训练隐式场景表示 作者&#xff1a;Pierre Marz…
阅读更多...
SaaS云HIS系统源码功能介绍

SaaS云HIS系统源码功能介绍

SaaS云HIS首页功能&#xff1a;包括工作计划、预警、主功能菜单、医院机构公告。 一、工作计划 1.值班概况&#xff1a;值班日期、值班时间、值班科室&#xff08;内科、外科等&#xff09; 2.待处理患者&#xff1a;内科人数、外科人数等 病历统计&#xff1a;入院病历、出…
阅读更多...
最新文章

Copyright @ 2022~2023