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

已于 2023-04-28 12:51:15 修改
阅读量1.1k 收藏
点赞数 1
分类专栏： 漏刻有时 php 文章标签： php mysql 数据库
于 2023-04-26 12:29:40 首次发布
LOCKDATAV出品，为避免断章取义走弯路，请注意环境配置和时间。
本文链接：https://blog.csdn.net/weixin_41290949/article/details/130379412
版权
漏刻有时 同时被 2 个专栏收录
280 篇文章 16 订阅
订阅专栏
php
191 篇文章 3 订阅
订阅专栏

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

前言

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

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

@漏刻有时

漏刻有时
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 13
    评论
专栏目录
PHP快速导入大量数据到数据库,快速导出百万级数据到CSV或者EXCEL文件
hanzhuhuaa的博客
09-16 1389
前言： 很多时候，因为数据统计，我们需要将数据库的数据导出到Excel等文件中，以供数据人员进行查看，如果数据集不大，其实很容易；但是如果对于大数集的导出，将要考虑各种性能的问题，这里以导出数据库一百万条数据为例，导出时间不过20秒，值的学习的一种大数据导出方式。总结：在插入大批量数据时，第一种方法无疑是最差劲的，而第二种方法在实际应用中就比较广泛，第三种方法在插入测试数据或者其他低要求时比较合适，速度确实快。如果导出数量过大，推荐使用循环导出，每次循环这里以导出一万条为例，循环100次即可全部导出。
php时间人性化展示
赛时科技
04-22 141
函数，用于将时间戳转换为人性化的时间差。该函数遍历了不同的时间单位，找到合适的单位来表示时间差，并返回相应的字符串。这样，你就可以根据需要将时间以人性化的方式展示给用户了。你可以根据具体需求调整时间格式和单位。函数将其格式化为"年-月-日 时:分:秒"的形式。然后，我们定义了一个。在上面的示例中，我们首先获取了当前的时间戳，并使用。函数来实现时间的人性化展示。函数将"-2 days"转换为时间戳），然后调用。最后，我们假设有一个过去的时间戳（这里使用。函数将其转换为人性化的时间差，并输出结果。
php mysql百万级数据_PHP + MySQL百万级数据插入的优化
weixin_33980343的博客
02-08 442
插入分析在MySQL中插入记录所需的时间由以下因素组成，其中的数字代表大约比例:如果每次插入一条语句都执行一条SQL语句，则需要执行除连接和关闭N次以外的所有步骤，这非常耗时. 有几种优化方法:在每个insert语句中写入多行，批量插入以将所有查询语句写入事务中，并使用Load Data导入数据每种方法的性能如下.Innodb引擎InnoDB为MySQL提供具有事务处理(提交)，回滚(回滚)和崩溃...
Mysql千万级别数据批量插入只需简单三步！
十指波课堂的博客
07-27 608
第一步：配置my.ini文件文件中配置bulk_insert_buffer_size=120M 或者更大将insert语句的长度设为最大。Max_allowed_packet=1MNet_buffer_length=8k保存第二步：查看设置的参选有没有生效.Mysql > SHOW VARIABLES;bulk_insert_buffer_size默认是8M，我们要把它调成100M或百兆以上...
mysql分页查询优化,大数据量优化
01-19
传统的mysql分页查询 select * from table limit n , m MySQL 执行此类SQL时需要先分页(默认一页1000条数据)通过全表扫描到N行，然后再去取M行。对于此类操作，获取前面少数几行数据会很快，但是随着扫描的记录数越多，SQL的性能就会越差，因为N的值越大，MySQL需要扫描越多的数据来定位到具体的N行，这样耗费大量的 IO 成本和时间成本。 特别是上线后数据量积累比较快,必须重视SQL优化,否则影响系统运行和用户使用体验 性能实验 直接用limit start, count分页语句， 也是我程序中用的方法： select * from table lim
PHP实现计算程序执行的时间1
08-08
二、主要技术本案例主要使用PHP 7中的日期和时间函数microtime()来返回当前的unix时间戳和微秒数，从而来实现计算程序运行时间的功能 三、效果图展示
【PHP】怎么计算PHP程序内存占用、运行时长执行耗时？
大白菜啊
09-28 2956
使用range生成数组1-1000000所消耗的时间和内存 //开始 $startMemory = memory_get_usage(); $t1 = microtime(true); //执行代码 $arr = range(1, 1000000); //结束 $t2 = microtime(true); $endMemory = memory_get_usage(); //输出 echo ...
php处理海量数据的几种方案
weixin_45090572的博客
01-02 476
php处理海量数据的几种方案
php开发中大数据量优化的问题总结(1)：smarty循环优化、API掉包丢失数据排查、负载平衡配置
漏刻有时数据可视化大屏（PHP&ECHARTS智能化开源软件系统）
06-09 1206
在 Smarty 模板中，可以使用Smarty自带的foreach循环标签来遍历一个数组，输出数组中的每个元素。如果希望将数组中的第一个元素单独拿出来进行操作，可以使用Smarty中的附加变量来实现
PHP 大数据 批量 插入
csdn9645的博客
03-22 213
通过 array_chunk 分批处理。
php导入大量数据到mysql性能优化技巧
10-24
主要介绍了php导入大量数据到mysql性能优化技巧,通过针对SQL语句的优化实现了mysql性能的提高,非常具有实用价值,需要的朋友可以参考下
MySQL大数据量快速插入方法和语句优化
01-31
MySQL大数据量快速插入方法和语句优化是本文我们主要要介绍的内容，接下来我们就来一一介绍，希望能够对您有所收获！插入一个记录需要的时间由下列因素组成，其中的数字表示大约比例：连接：（3）发送查询给服务器：（2）分析查询：（2）插入记录：（1x记录大小）插入索引：（1x索引）关闭：（1）这不考虑打开表的初始开销，每个并发运行的查询打开。表的大小以logN（B树）的速度减慢索引的插入。如果同时从同一个客户端插入很多行，使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快（在某些情况下快几倍）。如果你正向一个非空表添加数据，可以调节bulk_insert_buffe
MySQL 大数据量快速插入方法和语句优化分享
09-11
对于事务表，应使用BEGIN和COMMIT代替LOCK TABLES来加快插入
TCP相关问题总结
最新发布
qq_62835094的博客
04-23 1121
假设TCP是两次握手，则可能会发生以下情况客户端发起TCP握手，向服务器发送SYN，但该数据因网络波动滞留此时触发超时重传，重新简历TCP连接当新建立的连接断开后，之前在网络中滞留的SYN连接请求传递到服务端，服务端误以为客户端需要建立TCP连接，因此会发送SYN+ACK请求，并进入连接建立状态，但客户端并不需要建立连接，不会发送数据，因此会造成服务端资源的浪费三次握手可以防止客户端无效连接信息再次到达服务器，导致重新连接之所以需要四次挥手，因为半关闭tcp通道允许双方互相发送数据，数据是双向流动的。
spdlog 日志库部分源码说明——让你可以自定义的指定自动切换日志时间
ALex_zry的博客
04-22 469
针对 网络上spdlog日志库目前存在的使用方式固定，不能发挥这个库本身应有价值的情况，这里对一些支持场景进行说明，以供初学者省去阅读源码的时间，直接上手使用。
webman 事务回滚失效问题记录
juan9872的博客
04-21 747
webman是一款基于workerman开发的高性能HTTP服务框架。webman用于替代传统的php-fpm架构，提供超高性能可扩展的HTTP服务。你可以用webman开发网站，也可以开发HTTP接口或者微服务。除此之外，webman还支持自定义进程，可以做workerman能做的任何事情，例如websocket服务、物联网、游戏、TCP服务、UDP服务、unix socket服务等等。
bugku ezbypass
m_de_g的博客
04-23 184
bugku ezbypass $++,$_++
JetBrains PhpStorm v2024.1 安装教程 (PHP集成开发IDE)
wyqshusan的博客
04-23 535
PhpStorm是由JetBrains推出的一款轻量级集成开发环境，专为PHP开发者而设计。该软件融合了智能的HTML/CSS/JavaScript/PHP编辑器、代码质量分析工具、版本控制系统集成（包括SVN和GIT）、调试和测试等功能。除此之外，PhpStorm还提供了诸如代码自动完成、语法高亮、实时导航、实时错误检查和代码重构等实用功能。在新版本中，PhpStorm引入了许多新功能，并对现有功能进行了优化和调整。其中包括新增了死代码检测功能，可以帮助检测出冗余代码，并突出显示之前可能未被使用过的类、
WordPress自动采集发布AutoPostPro汉化版插件
pengqingwen的博客
04-23 231
WP-AutoPostPro 是一款极为出色的WordPress自动采集发布插件，其显著优势在于能够从任何网站抓取内容并自动将其发布到你的WordPress网站上。它实现了对任何网页内容的自动采集和发布，整个采集过程完全自动化，无需手动操作。此外，它还提供了一系列实用功能，如内容筛选、HTML标签过滤、关键词替换、自动添加链接、自动生成标签、自动将远程图片保存到本地服务器、自动为文章添加前缀和后缀，以及使用微软翻译引擎将采集到的文章自动翻译成多种语言进行发布。
mysql批量更新大数据量
06-01
当需要更新大量数据时，使用MySQL的批量更新功能可以提高更新效率。以下是一些常用的方法： 1. 使用多个UPDATE语句：将需要更新的数据分成多个小批次，每次更新一部分。这样可以避免一次更新过多数据导致数据库崩溃的风险。但是，这种方法需要手动分批次，并且执行多次SQL语句，有些麻烦。 2. 使用INSERT INTO SELECT语句：将需要更新的数据先插入到一个临时表中，然后通过UPDATE语句更新原表。这种方法需要创建一个临时表，需要多次执行SQL语句，不太方便。 3. 使用LOAD DATA INFILE语句：将需要更新的数据保存到CSV文件中，然后使用LOAD DATA INFILE语句将数据导入到数据库。这种方法可以批量导入大量数据，速度比较快。 4. 使用存储过程：将需要更新的数据分成多个小批次，每次更新一部分。将多个UPDATE语句放在一个存储过程中，并使用循环结构来实现批量更新。这种方法比较灵活，可以根据实际情况进行调整。 需要注意的是，在使用批量更新功能时，要确保更新的数据符合要求，否则可能会导致数据不一致或者数据丢失的情况。同时，也要注意数据库的性能，避免一次更新过多数据导致数据库崩溃。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 13
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

漏刻有时

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值