简介这是一款基于PHP开发的xls文件导入MySQL数据库的小工具主要面向需要将Excel表格数据批量写入数据库的PHP开发者可大幅减少手工录入与重复操作适合日常办公数据迁移、网站后台数据初始化等场景。资源包共包含四个文件其中三个PHP脚本分别负责上传、解析与入库逻辑一个inc文件用于公共配置压缩包总大小仅13KB代码量精简便于快速阅读和二次开发。程序允许自定义数据库名、数据表名称及字段映射关系读取时要求文件必须为xls格式且Excel表头需与目标数据表字段一一对应同时内置中文处理机制入库统一使用UTF-8编码能较好兼容中文内容避免乱码问题。目前已有275人学习下载。对于PHP初学者来说这是一份不错的Excel导入参考实现对项目开发者而言也可以在此代码基础上扩展为通用的数据导入模块节省从零开发的时间。 先交代一下背景。当时我在给一个图书管理系统做后台运营同事手里攒了一百多张xls表格每张几千行需要合并导入MySQL。数据源五花八门有从其他系统导出的有手工录入的还有老板改过三版的。如果让我手动复制进数据库估计得加班到天亮让运营自己装MySQL客户端学SQL更不现实。所以我就写了一个PHP程序把“上传xls → 解析 → 清洗 → 导入MySQL”整条链路打通。这篇博文把整个过程拆开讲从环境准备、解析选型到批量入库适合正在做PHP后台导入功能的朋友参考尤其是对PHP不熟、之前没处理过Excel导入的同学。1. 业务场景先想清楚这个功能不只是“解析一个Excel”1.1 运营的表格里什么都有就是没有规范很多人一看到“xls导入Mysql”这个标题第一反应是找个库把excel读出来再循环insert到数据库里。真正在业务系统里做过一次导入功能就知道这活儿远没那么简单。图书管理系统要导书单开源OA要导员工花名册逍遥商城这类PHP商城系统要导商品库存每个场景的表格格式都不同而且使用表格的人永远不按你预期的格式填。运营同事给的xls里表头下面可能藏着合并单元格金额列里会出现“1,200元”这种带逗号带单位的写法日期列同一天出现三种格式有的单元格还带换行符。这些脏数据如果直接入库轻则页面显示错乱重则导致SQL报错、数据错位。所以我在动手写代码之前先把需求文档里的“导入”拆成了四个环节文件上传、读取解析、数据清洗、分批入库。这四个环节缺一不可后面每个环节都有坑。1.2 为什么选PHP而不是Python或Java有同事问我为什么不直接用Python写个脚本把xls导进去或者用Java写个独立服务。答案很现实这个导入功能不是一次性工具而是要集成在现有PHP后台里给运营同事长期使用的功能。后台已有的权限、登录态、操作日志、审计逻辑全是用PHP写的我用PHP实现导入模块直接复用现有体系就行不需要额外部署一套服务。数据量也是一个考量。正常的业务导入场景几千行到几万行居多PHP完全能处理得过来。只有到百万级数据量才需要考虑换Go、Java或者引入datax这类同步工具。另外PHP的生态里PhpSpreadsheet已经把Excel读取做得很成熟了不需要自己造轮子。所以“用PHP程序做xls导入MySQL”不是性能最优解但一定是最贴合现有系统、最好维护的解法。2. 环境准备从零把PHP和MySQL跑起来2.1 本机环境集成环境还是Docker写代码之前得先把环境跑通。如果你是第一次接触PHP我建议直接用集成环境把Apache/Nginx、PHP、MySQL一次装好。很多mysql安装教程会让你去官网下载安装包然后手动配置my.ini、初始化data目录、设置root密码这套流程对新手来说太容易卡住。集成环境能让你跳过这些繁琐步骤先把注意力放在功能开发上。如果是团队协作项目我更推荐用Docker。docker安装mysql只需要一条命令就能起来一个指定版本的实例比如这样docker run -d --name mysql8 -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDroot123 \ -e MYSQL_DATABASEimport_demo \ mysql:8.0这样做的好处是团队所有人都能用同一个镜像、同一个版本不会出现“我本机好好的你环境怎么跑不起来”的问题。等代码写完Docker也能保证线上和本机的MySQL版本一致。2.2 必须开启的PHP扩展和常见配置报错环境装好之后先看PHP加载了哪些扩展。直接在命令行执行php -m导入xls用到的核心扩展是pdo_mysql、mbstring、zip、xml。PhpSpreadsheet底层要读压缩文件和XMLzip和xml少一个都会报错。调试阶段我踩过一个很典型的坑启动服务时看到一条警告PHP Warning: Module mbstring is already loaded in unknown on line 0这个提示的意思是mbstring被重复加载了。常见原因是我在php.ini里加了extensionmbstring然后PHP另外一个配置文件比如php.d目录下的ini文件又加载了一遍。解决办法很简单搜索php.ini和conf.d目录下所有加载mbstring的行注释掉其中一处就行。这类小坑看着不起眼但确实能卡住人半天。3. 解析xls的选型与实操从CSV到PhpSpreadsheet3.1 三种方案放一起比较解析xls文件我试过三种方案各有利弊。方案支持格式维护状态性能适用场景原生fgetcsv仅csv语言内置高数据规整、系统导出的csvPHPExcelxls/xlsx/csv已停止维护中老项目遗留代码PhpSpreadsheetxls/xlsx/csv活跃维护中新项目首选第一版我图省事直接让运营把xls另存为csv然后用fgetcsv读。结果发现两个问题一是运营转出来的csv经常因为Excel编码问题打开后中文乱码二是有的xls里带公式、带格式另存为csv后数据就变了。后来换成PHPExcel发现这个库在PHP 7.0之后基本跑不动官方也停止维护了。最终我选了PhpSpreadsheet它是PHPExcel的官方继任者支持xls、xlsx、csv而且PHP 7.4和PHP 8都能用。虽然性能不算极致但做后台导入完全够用。3.2 用PhpSpreadsheet读xls的完整步骤用Composer安装一行命令composer require phpoffice/phpspreadsheet接下来是最基本的读取代码?php require vendor/autoload.php; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; $inputFileName ./demo.xls; $spreadsheet IOFactory::load($inputFileName); $sheet $spreadsheet-getActiveSheet(); $highestRow $sheet-getHighestRow(); $highestColumn $sheet-getHighestColumn(); $highestColumnIndex Coordinate::columnIndexFromString($highestColumn); $data []; for ($row 1; $row $highestRow; $row) { for ($col 1; $col $highestColumnIndex; $col) { $cell $sheet-getCellByColumnAndRow($col, $row); $data[$row][$col] $cell-getValue(); } }注意一个关键点IOFactory::load()会连单元格样式一起读文件很大时非常吃内存。如果只需要读数据用setReadDataOnly(true)能明显提升性能。还可以用setReadFilter只读取指定列。几万行的xls文件打开方式不同内存占用差距能到三四倍这个优化一定要做。3.3 编码问题中文乱码和json编码xls文件里的中文乱码是新手最容易碰到的问题。PhpSpreadsheet读取xlsx格式时一般不会乱码因为xlsx内部是UTF-8编码的XML。但老式xls文件可能是GBK或者GB2312编码读出来就是一堆乱码。解决办法是读取后做编码检查和转换$value $cell-getValue(); if (!mb_check_encoding($value, UTF-8)) { $value mb_convert_encoding($value, UTF-8, GBK); }这里用mb_check_encoding判断是否已经是UTF-8避免重复转换把正常字符搞坏。还有一个坑是PHP序列化中文的问题。如果业务需要把一行的多个字段拼成数组用serialize或json_encode存到MySQL直接存会出现\uXXXX之类的转义或者入库后中文变成问号。建议在序列化前统一转成UTF-8保存后用json_decode还原。字符集不统一后面做查询、排序、搜索全是麻烦。4. 入库前的数据清洗与校验脏数据是最大的隐患4.1 先学会看数据常见脏数据长什么样我拿到任何一份xls第一件事不是写导入逻辑而是先写几行代码把前二十行全部打印出来肉眼看看格式。因为不看到真实的脏数据后面写的校验全是在猜。常见的脏数据有这么几类第一个是空行Excel里看起来空了实际有隐藏的换行符或空格第二个是数字被存成文本单元格左上角有绿色三角这在金额、数量字段上非常致命直接用PHP做加法运算会得到错误结果第三个是日期格式不统一有的写“2024-01-01”有的写“2024.01.01”还有的写“2024年1月1日”第四个是字符串首尾有不可见空格导致去数据库里比对时明明看起来一样其实不匹配。这些脏数据如果不在入库前处理导入成功的假象会在后续查询、统计、报表阶段集中爆发。4.2 唯一性校验先查再插还是加唯一索引导入场景里最容易出问题的就是重复数据。运营拿来的表格可能是从旧系统导出的库里已经有部分数据再导一遍就会重复。针对这个问题我一般做两道防线。第一道防线是代码层校验。导入前根据业务唯一键比如图书的ISBN、员工的工号查一遍数据库把已有的唯一键放到一个数组里逐行比对。已经存在的记录可以选择跳过、更新或者报错提示具体看业务需求。第二道防线是数据库唯一索引。哪怕代码里漏了判断MySQL的唯一索引也能兜底。这个场景对应的就是经常有人搜的“mysql设置唯一已经有重复数据库”问题本质上是给表加UNIQUE KEY约束然后处理原有的重复数据。注意加唯一索引前要先清理旧数据否则索引创建会失败。4.3 错误收集别让用户对着白屏干着急业务人员用导入功能时最怕遇到一种情况点完导入按钮页面转圈然后直接白屏也不知道哪里错了。所以错误处理一定要做友好。我的做法是逐行校验把所有错误收集到一个数组里每一行配一个错误原因。全部校验完后如果有错误就返回一个带行号和错误信息的列表给前端。例如[ [row 5, error ISBN不能为空], [row 12, error 价格格式不正确], ]数据量特别大的时候可以把错误信息导出成Excel文件让运营照着行号修改后重新上传。这个体验比“报错让用户自己猜”好得多也省掉很多来回沟通的时间。5. 写入MySQL批量插入、事务与存储过程5.1 一条条insert太慢改成批量才靠谱刚开始做导入的时候我直接在一个foreach循环里写insert into table values(...)3000行数据跑了将近一分钟。问题不在MySQL而在PHP和数据库之间的网络往返。每执行一次insert都是一次完整的请求1000行就是1000次语句解析和通信开销。改成批量插入之后速度提升非常明显$rows []; foreach ($data as $item) { $rows[] ( . implode(,, $item) . ); } $sql INSERT INTO books (title, author, price) VALUES . implode(,, $rows); $pdo-exec($sql);一次拼接几千条VALUESSQL语句会非常长所以一般控制在每批500到1000条。如果数据里有特殊字符记得用预处理或转义函数避免SQL注入和语法错误。也可以直接用PDO的预处理机制循环绑定参数性能和安全性都有保障。5.2 事务和分批提交怎么平衡导入过程中如果有一行数据因数据库约束失败前面的所有数据要不要回滚这取决于业务规则。如果是把xls当作一个整体批次导入任何一个错误都应该导致整个批次被回滚这时候一定要用事务。$pdo-beginTransaction(); try { // 批量插入 $pdo-commit(); } catch (Exception $e) { $pdo-rollBack(); // 记录错误 }但事务也不是越大越好。几万行数据放在一个事务里MySQL要持有大量行锁持续时间长容易影响线上其他读写。我常用的策略是分批事务每500条数据一个事务一个批次失败只回滚这500条前面导入成功的保留。这样既能保证部分一致性又不至于锁太久。5.3 存储过程什么时候用mysql存储过程在导入场景里有没有用这个问题我纠结过。后来我的结论是纯数据插入没必要用但如果导入过程涉及复杂的业务计算或需要操作临时表可以考虑。例如导入订单数据时要生成订单编号、扣减库存、写流水这些逻辑放在PHP里需要多次数据库往返。写成存储过程可以一次性把逻辑做完减少网络开销。但存储过程的缺点也很明显排错困难、版本管理麻烦、不同环境同步容易遗漏。我的建议是除非你有明确的性能瓶颈否则业务逻辑尽量留在PHP里。存储过程可以处理一些纯数据库层面的辅助操作比如创建临时表、批量更新某个字段。不要在存储过程里堆砌大量业务判断不然半年后你自己都看不懂。5.4 内存超时优化Web请求默认有超时时间PHP脚本也有内存限制。导入几万行的xls如果一次性把整个文件读到内存再一次性写入数据库非常容易触发memory_limit或max_execution_time。我的优化思路是分块处理读取文件时用setReadFilter分批读取一个区块处理完之后unset释放变量再读下一个区块。写入也是一样每500条提交一次。如果导入任务耗时特别长建议把导入脚本放在命令行下跑用crontab定时执行或者做一个导入任务队列彻底避开浏览器请求超时问题。6. 翻车记录实际导入过程中我踩过的坑6.1 日期、空字符串、字段类型Excel里的日期本质上是数字序列号比如“2024-01-01”在底层可能是45292这样的浮点数。如果直接用PhpSpreadsheet读出来的值入库你会得到一个天书一样的数字而非日期字符串。解决办法是用PhpSpreadsheet自带的日期转换方法或者自己判断单元格格式后转成通用日期格式。空单元格读出来有时候是null有时候是空字符串。空字符串如果插入到DATE或INT字段MySQL会报错或者存一个默认值。所以入库前要统一判断是空值的就置为null不要留空字符串。网上有人搜“mysql中int5”大概率是踩到了字段类型运算的坑。比如order by一个varchar类型的数字列排序结果是1、10、11、2而不是1、2、10、11。这就是因为导入时没有把列类型定义为int。数值和金额字段入库前一定要转成int或decimal别用varchar存数字。6.2 PHP错误处理不友好的坑PhpSpreadsheet读取文件失败时抛出的异常类型和其他PHP异常不一样。如果不做catch页面会直接返回500或者白屏前端和后端都看不明原因。我习惯在代码入口处写个try-catch把异常信息记录到日志文件里。另外要注意PHP错误处理和异常是两回事。PhpSpreadsheet抛的是异常普通PHP警告和Notice不会抛出异常但会打印到页面。如果开启了display_errors这些信息会把JSON接口返回值污染掉导致前端报格式错误。生产环境一定要关闭display_errors改用日志记录。6.3 防止重复提交重复导入用户点一次导入按钮前端可能因为网络延迟连点了三次结果同一份数据被导入了三遍。这个问题不解决数据库里全是重复数据。我的处理方式是双保险。前端在点击后立即把按钮置灰并显示loading防止连续点击。后端在导入接口里加一个文件级别的幂等判断用文件的MD5值和导入时间做唯一标识如果同一个文件在短时间内重复提交直接返回“请勿重复提交”。数据库唯一索引仍然是最底层的兜底三层都做上才能安心。最后再分享一点个人经验。像flight php这类轻量框架配合命令行脚本做定时导入比在Web框架里折腾更顺手在开源OA、图书管理系统、商城这类PHP项目上二次开发导入功能尽量做成独立模块别把解析逻辑和业务控制器揉在一起后面维护的时候你会感谢自己。等系统数据量真正大到PHP撑不住时再考虑数据同步工具或分库分表那就是另一个故事了。本文还有配套的精品资源点击获取
简介这是一款基于PHP开发的xls文件导入MySQL数据库的小工具主要面向需要将Excel表格数据批量写入数据库的PHP开发者可大幅减少手工录入与重复操作适合日常办公数据迁移、网站后台数据初始化等场景。资源包共包含四个文件其中三个PHP脚本分别负责上传、解析与入库逻辑一个inc文件用于公共配置压缩包总大小仅13KB代码量精简便于快速阅读和二次开发。程序允许自定义数据库名、数据表名称及字段映射关系读取时要求文件必须为xls格式且Excel表头需与目标数据表字段一一对应同时内置中文处理机制入库统一使用UTF-8编码能较好兼容中文内容避免乱码问题。目前已有275人学习下载。对于PHP初学者来说这是一份不错的Excel导入参考实现对项目开发者而言也可以在此代码基础上扩展为通用的数据导入模块节省从零开发的时间。 先交代一下背景。当时我在给一个图书管理系统做后台运营同事手里攒了一百多张xls表格每张几千行需要合并导入MySQL。数据源五花八门有从其他系统导出的有手工录入的还有老板改过三版的。如果让我手动复制进数据库估计得加班到天亮让运营自己装MySQL客户端学SQL更不现实。所以我就写了一个PHP程序把“上传xls → 解析 → 清洗 → 导入MySQL”整条链路打通。这篇博文把整个过程拆开讲从环境准备、解析选型到批量入库适合正在做PHP后台导入功能的朋友参考尤其是对PHP不熟、之前没处理过Excel导入的同学。1. 业务场景先想清楚这个功能不只是“解析一个Excel”1.1 运营的表格里什么都有就是没有规范很多人一看到“xls导入Mysql”这个标题第一反应是找个库把excel读出来再循环insert到数据库里。真正在业务系统里做过一次导入功能就知道这活儿远没那么简单。图书管理系统要导书单开源OA要导员工花名册逍遥商城这类PHP商城系统要导商品库存每个场景的表格格式都不同而且使用表格的人永远不按你预期的格式填。运营同事给的xls里表头下面可能藏着合并单元格金额列里会出现“1,200元”这种带逗号带单位的写法日期列同一天出现三种格式有的单元格还带换行符。这些脏数据如果直接入库轻则页面显示错乱重则导致SQL报错、数据错位。所以我在动手写代码之前先把需求文档里的“导入”拆成了四个环节文件上传、读取解析、数据清洗、分批入库。这四个环节缺一不可后面每个环节都有坑。1.2 为什么选PHP而不是Python或Java有同事问我为什么不直接用Python写个脚本把xls导进去或者用Java写个独立服务。答案很现实这个导入功能不是一次性工具而是要集成在现有PHP后台里给运营同事长期使用的功能。后台已有的权限、登录态、操作日志、审计逻辑全是用PHP写的我用PHP实现导入模块直接复用现有体系就行不需要额外部署一套服务。数据量也是一个考量。正常的业务导入场景几千行到几万行居多PHP完全能处理得过来。只有到百万级数据量才需要考虑换Go、Java或者引入datax这类同步工具。另外PHP的生态里PhpSpreadsheet已经把Excel读取做得很成熟了不需要自己造轮子。所以“用PHP程序做xls导入MySQL”不是性能最优解但一定是最贴合现有系统、最好维护的解法。2. 环境准备从零把PHP和MySQL跑起来2.1 本机环境集成环境还是Docker写代码之前得先把环境跑通。如果你是第一次接触PHP我建议直接用集成环境把Apache/Nginx、PHP、MySQL一次装好。很多mysql安装教程会让你去官网下载安装包然后手动配置my.ini、初始化data目录、设置root密码这套流程对新手来说太容易卡住。集成环境能让你跳过这些繁琐步骤先把注意力放在功能开发上。如果是团队协作项目我更推荐用Docker。docker安装mysql只需要一条命令就能起来一个指定版本的实例比如这样docker run -d --name mysql8 -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDroot123 \ -e MYSQL_DATABASEimport_demo \ mysql:8.0这样做的好处是团队所有人都能用同一个镜像、同一个版本不会出现“我本机好好的你环境怎么跑不起来”的问题。等代码写完Docker也能保证线上和本机的MySQL版本一致。2.2 必须开启的PHP扩展和常见配置报错环境装好之后先看PHP加载了哪些扩展。直接在命令行执行php -m导入xls用到的核心扩展是pdo_mysql、mbstring、zip、xml。PhpSpreadsheet底层要读压缩文件和XMLzip和xml少一个都会报错。调试阶段我踩过一个很典型的坑启动服务时看到一条警告PHP Warning: Module mbstring is already loaded in unknown on line 0这个提示的意思是mbstring被重复加载了。常见原因是我在php.ini里加了extensionmbstring然后PHP另外一个配置文件比如php.d目录下的ini文件又加载了一遍。解决办法很简单搜索php.ini和conf.d目录下所有加载mbstring的行注释掉其中一处就行。这类小坑看着不起眼但确实能卡住人半天。3. 解析xls的选型与实操从CSV到PhpSpreadsheet3.1 三种方案放一起比较解析xls文件我试过三种方案各有利弊。方案支持格式维护状态性能适用场景原生fgetcsv仅csv语言内置高数据规整、系统导出的csvPHPExcelxls/xlsx/csv已停止维护中老项目遗留代码PhpSpreadsheetxls/xlsx/csv活跃维护中新项目首选第一版我图省事直接让运营把xls另存为csv然后用fgetcsv读。结果发现两个问题一是运营转出来的csv经常因为Excel编码问题打开后中文乱码二是有的xls里带公式、带格式另存为csv后数据就变了。后来换成PHPExcel发现这个库在PHP 7.0之后基本跑不动官方也停止维护了。最终我选了PhpSpreadsheet它是PHPExcel的官方继任者支持xls、xlsx、csv而且PHP 7.4和PHP 8都能用。虽然性能不算极致但做后台导入完全够用。3.2 用PhpSpreadsheet读xls的完整步骤用Composer安装一行命令composer require phpoffice/phpspreadsheet接下来是最基本的读取代码?php require vendor/autoload.php; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; $inputFileName ./demo.xls; $spreadsheet IOFactory::load($inputFileName); $sheet $spreadsheet-getActiveSheet(); $highestRow $sheet-getHighestRow(); $highestColumn $sheet-getHighestColumn(); $highestColumnIndex Coordinate::columnIndexFromString($highestColumn); $data []; for ($row 1; $row $highestRow; $row) { for ($col 1; $col $highestColumnIndex; $col) { $cell $sheet-getCellByColumnAndRow($col, $row); $data[$row][$col] $cell-getValue(); } }注意一个关键点IOFactory::load()会连单元格样式一起读文件很大时非常吃内存。如果只需要读数据用setReadDataOnly(true)能明显提升性能。还可以用setReadFilter只读取指定列。几万行的xls文件打开方式不同内存占用差距能到三四倍这个优化一定要做。3.3 编码问题中文乱码和json编码xls文件里的中文乱码是新手最容易碰到的问题。PhpSpreadsheet读取xlsx格式时一般不会乱码因为xlsx内部是UTF-8编码的XML。但老式xls文件可能是GBK或者GB2312编码读出来就是一堆乱码。解决办法是读取后做编码检查和转换$value $cell-getValue(); if (!mb_check_encoding($value, UTF-8)) { $value mb_convert_encoding($value, UTF-8, GBK); }这里用mb_check_encoding判断是否已经是UTF-8避免重复转换把正常字符搞坏。还有一个坑是PHP序列化中文的问题。如果业务需要把一行的多个字段拼成数组用serialize或json_encode存到MySQL直接存会出现\uXXXX之类的转义或者入库后中文变成问号。建议在序列化前统一转成UTF-8保存后用json_decode还原。字符集不统一后面做查询、排序、搜索全是麻烦。4. 入库前的数据清洗与校验脏数据是最大的隐患4.1 先学会看数据常见脏数据长什么样我拿到任何一份xls第一件事不是写导入逻辑而是先写几行代码把前二十行全部打印出来肉眼看看格式。因为不看到真实的脏数据后面写的校验全是在猜。常见的脏数据有这么几类第一个是空行Excel里看起来空了实际有隐藏的换行符或空格第二个是数字被存成文本单元格左上角有绿色三角这在金额、数量字段上非常致命直接用PHP做加法运算会得到错误结果第三个是日期格式不统一有的写“2024-01-01”有的写“2024.01.01”还有的写“2024年1月1日”第四个是字符串首尾有不可见空格导致去数据库里比对时明明看起来一样其实不匹配。这些脏数据如果不在入库前处理导入成功的假象会在后续查询、统计、报表阶段集中爆发。4.2 唯一性校验先查再插还是加唯一索引导入场景里最容易出问题的就是重复数据。运营拿来的表格可能是从旧系统导出的库里已经有部分数据再导一遍就会重复。针对这个问题我一般做两道防线。第一道防线是代码层校验。导入前根据业务唯一键比如图书的ISBN、员工的工号查一遍数据库把已有的唯一键放到一个数组里逐行比对。已经存在的记录可以选择跳过、更新或者报错提示具体看业务需求。第二道防线是数据库唯一索引。哪怕代码里漏了判断MySQL的唯一索引也能兜底。这个场景对应的就是经常有人搜的“mysql设置唯一已经有重复数据库”问题本质上是给表加UNIQUE KEY约束然后处理原有的重复数据。注意加唯一索引前要先清理旧数据否则索引创建会失败。4.3 错误收集别让用户对着白屏干着急业务人员用导入功能时最怕遇到一种情况点完导入按钮页面转圈然后直接白屏也不知道哪里错了。所以错误处理一定要做友好。我的做法是逐行校验把所有错误收集到一个数组里每一行配一个错误原因。全部校验完后如果有错误就返回一个带行号和错误信息的列表给前端。例如[ [row 5, error ISBN不能为空], [row 12, error 价格格式不正确], ]数据量特别大的时候可以把错误信息导出成Excel文件让运营照着行号修改后重新上传。这个体验比“报错让用户自己猜”好得多也省掉很多来回沟通的时间。5. 写入MySQL批量插入、事务与存储过程5.1 一条条insert太慢改成批量才靠谱刚开始做导入的时候我直接在一个foreach循环里写insert into table values(...)3000行数据跑了将近一分钟。问题不在MySQL而在PHP和数据库之间的网络往返。每执行一次insert都是一次完整的请求1000行就是1000次语句解析和通信开销。改成批量插入之后速度提升非常明显$rows []; foreach ($data as $item) { $rows[] ( . implode(,, $item) . ); } $sql INSERT INTO books (title, author, price) VALUES . implode(,, $rows); $pdo-exec($sql);一次拼接几千条VALUESSQL语句会非常长所以一般控制在每批500到1000条。如果数据里有特殊字符记得用预处理或转义函数避免SQL注入和语法错误。也可以直接用PDO的预处理机制循环绑定参数性能和安全性都有保障。5.2 事务和分批提交怎么平衡导入过程中如果有一行数据因数据库约束失败前面的所有数据要不要回滚这取决于业务规则。如果是把xls当作一个整体批次导入任何一个错误都应该导致整个批次被回滚这时候一定要用事务。$pdo-beginTransaction(); try { // 批量插入 $pdo-commit(); } catch (Exception $e) { $pdo-rollBack(); // 记录错误 }但事务也不是越大越好。几万行数据放在一个事务里MySQL要持有大量行锁持续时间长容易影响线上其他读写。我常用的策略是分批事务每500条数据一个事务一个批次失败只回滚这500条前面导入成功的保留。这样既能保证部分一致性又不至于锁太久。5.3 存储过程什么时候用mysql存储过程在导入场景里有没有用这个问题我纠结过。后来我的结论是纯数据插入没必要用但如果导入过程涉及复杂的业务计算或需要操作临时表可以考虑。例如导入订单数据时要生成订单编号、扣减库存、写流水这些逻辑放在PHP里需要多次数据库往返。写成存储过程可以一次性把逻辑做完减少网络开销。但存储过程的缺点也很明显排错困难、版本管理麻烦、不同环境同步容易遗漏。我的建议是除非你有明确的性能瓶颈否则业务逻辑尽量留在PHP里。存储过程可以处理一些纯数据库层面的辅助操作比如创建临时表、批量更新某个字段。不要在存储过程里堆砌大量业务判断不然半年后你自己都看不懂。5.4 内存超时优化Web请求默认有超时时间PHP脚本也有内存限制。导入几万行的xls如果一次性把整个文件读到内存再一次性写入数据库非常容易触发memory_limit或max_execution_time。我的优化思路是分块处理读取文件时用setReadFilter分批读取一个区块处理完之后unset释放变量再读下一个区块。写入也是一样每500条提交一次。如果导入任务耗时特别长建议把导入脚本放在命令行下跑用crontab定时执行或者做一个导入任务队列彻底避开浏览器请求超时问题。6. 翻车记录实际导入过程中我踩过的坑6.1 日期、空字符串、字段类型Excel里的日期本质上是数字序列号比如“2024-01-01”在底层可能是45292这样的浮点数。如果直接用PhpSpreadsheet读出来的值入库你会得到一个天书一样的数字而非日期字符串。解决办法是用PhpSpreadsheet自带的日期转换方法或者自己判断单元格格式后转成通用日期格式。空单元格读出来有时候是null有时候是空字符串。空字符串如果插入到DATE或INT字段MySQL会报错或者存一个默认值。所以入库前要统一判断是空值的就置为null不要留空字符串。网上有人搜“mysql中int5”大概率是踩到了字段类型运算的坑。比如order by一个varchar类型的数字列排序结果是1、10、11、2而不是1、2、10、11。这就是因为导入时没有把列类型定义为int。数值和金额字段入库前一定要转成int或decimal别用varchar存数字。6.2 PHP错误处理不友好的坑PhpSpreadsheet读取文件失败时抛出的异常类型和其他PHP异常不一样。如果不做catch页面会直接返回500或者白屏前端和后端都看不明原因。我习惯在代码入口处写个try-catch把异常信息记录到日志文件里。另外要注意PHP错误处理和异常是两回事。PhpSpreadsheet抛的是异常普通PHP警告和Notice不会抛出异常但会打印到页面。如果开启了display_errors这些信息会把JSON接口返回值污染掉导致前端报格式错误。生产环境一定要关闭display_errors改用日志记录。6.3 防止重复提交重复导入用户点一次导入按钮前端可能因为网络延迟连点了三次结果同一份数据被导入了三遍。这个问题不解决数据库里全是重复数据。我的处理方式是双保险。前端在点击后立即把按钮置灰并显示loading防止连续点击。后端在导入接口里加一个文件级别的幂等判断用文件的MD5值和导入时间做唯一标识如果同一个文件在短时间内重复提交直接返回“请勿重复提交”。数据库唯一索引仍然是最底层的兜底三层都做上才能安心。最后再分享一点个人经验。像flight php这类轻量框架配合命令行脚本做定时导入比在Web框架里折腾更顺手在开源OA、图书管理系统、商城这类PHP项目上二次开发导入功能尽量做成独立模块别把解析逻辑和业务控制器揉在一起后面维护的时候你会感谢自己。等系统数据量真正大到PHP撑不住时再考虑数据同步工具或分库分表那就是另一个故事了。本文还有配套的精品资源点击获取
