PHP图书管理系统实战：从PDO安全操作到完整项目部署
2026/9/2 21:05:46
简介：面向需要将Excel表格批量导入MySQL的PHP开发者，这套工具包可直接读取.xls文件并按指定规则写入数据库，支持自定义数据库名、数据表及字段对应关系，适用于后台数据迁移、批量初始化等场景。压缩包内共4个文件，包含3个PHP脚本与1个inc配置文件，整体仅13KB，轻量易部署。核心包括上传入口、Excel解析类与oleread读取组件，配合insert.php完成入库操作。使用时有两点需注意：导入文件必须为.xls格式，且表头需与目标表字段一致；程序已兼容中文内容，数据库需采用UTF-8编码以避免乱码。资源目前已有275人学习，对刚接触PHP下Excel导入的开发者来说，可借此了解xls解析、字段映射和中文编码处理的完整思路，节省自行封装底层读取逻辑的时间。 做后台系统开发的朋友应该都遇到过这个需求：运营同学手里整理好的 xls 表格，需要批量导入 MySQL。我之前做过一个“xls导入Mysql(PHP程序)”的小工具，把原本需要人工逐条录入、耗时一个下午的活儿压缩到了几秒钟。这个需求看似简单，但真正落地时从文件格式判断、单元格类型识别、数据校验到批量入库，每一步都有隐藏的坑。这篇文章就把我实现这个功能时完整的技术选型、核心代码、以及踩过的坑整理出来，给正在做类似功能的同学一个可以直接参考的样本。
1. 需求场景与分析
1.1 为什么Excel导入MySQL是刚需
随便打开一个业务后台，就能看到大批量数据的落地场景。用户列表批量导入、商品SKU批量上架、线下门店数据迁移、题库批量录入、财务对账单导入，这些都是 Excel 表格的高频使用场景。业务同学早就习惯用 Excel 整理数据，表格里既有基础字段，也可能附带格式复杂的日期、超长数字、文本型数字、换行符，甚至合并单元格。
如果让开发手动逐条写 INSERT 语句，或者是把 Excel 数据复制成 SQL 再执行，数据量一旦超过几百条就完全不现实。更麻烦的是，运营同学给出的 Excel 里常常混着全角空格、科学计数法、日期格式显示问题，逐条清洗等于加班。这个时候做一个统一的上传导入接口，让业务方按模板填好数据、点一下上传，后端用程序解析、校验、落库，才是最稳的做法。
这个功能本质上是“文件解析 + 数据清洗 + 批量写库”的三段式流程，核心目标只有两个：数据能进去，数据是对的。
1.2 技术选型：PhpSpreadsheet 比 PHPExcel 稳太多
做 PHP 的同学对 PHPExcel 应该不陌生，但这个库已经停止维护很久了，官方自己也推荐用 PhpSpreadsheet 替代。PhpSpreadsheet 是 PHPExcel 的继承者，支持 xls、xlsx、csv 等多种格式，API 设计和 PHPExcel 高度一致，迁移成本很低。
我这次选型的原则很简单：一是库要还在活跃维护，二是要支持读取 xls 老格式，三是上手要快。PhpSpreadsheet 这三点都满足。另外提醒一句，如果导入的文件只是简单的 csv，那么用 fgetcsv 就够了，不一定非要上这个重库。但既然标题明确是 xls 导入，那面对 .xls/.xlsx 混合文件时，PhpSpreadsheet 显然是更省心的方案。
| 对比项 | PHPExcel | PhpSpreadsheet |
|---|---|---|
| 维护状态 | 已停止维护 | 持续更新 |
| xls 支持 | 支持 | 支持 |
| xlsx 支持 | 支持 | 支持 |
| Composer 安装 | 支持 | 支持 |
| 命名空间 | PHPExcel | PhpOffice\PhpSpreadsheet |
| 推荐度 | 不推荐新项目使用 | 推荐 |
2. 环境准备与基础搭建
2.1 PHP 环境与扩展要求
运行 PhpSpreadsheet 需要 PHP 7.4 以上，推荐直接用 PHP 8.x。除了基础环境，还要确保启用了这几个扩展：php_zip（处理压缩格式的 xlsx）、php_xml（解析 XML）、php_gd（如果有图片处理需求）、php_mbstring（处理多字节编码）。
这里说一个很多新手容易忽略的小问题：在 Windows 下用集成环境（比如 phpstudy、小皮面板）的同学，打开 php.ini 后记得确认 extensions 目录下这几项前面没有分号。尤其是php_mbstring，如果这个扩展没启用，PhpSpreadsheet 在读取包含中文的单元格时非常容易产生乱码。如果你在导入过程中遇到了php warning: module "mbstring" is already loaded in unknown on line 0这类警告，那说明 php.ini 里重复加载了这个扩展，删掉一行即可，不影响功能但会让日志很脏。
数据库这边我用的是 PDO 连接 MySQL，相比 mysqli，PDO 支持预处理语句和事务，代码写起来也更统一。数据库驱动需要开启pdo_mysql，这个在主流 PHP 集成环境里默认就是开着的。
2.2 通过 Composer 安装 PhpSpreadsheet
项目根目录下执行：
composer require phpoffice/phpspreadsheet如果是老项目还没有 composer.json，可以先composer init初始化一下。安装完成后，PHP 文件里引入自动加载文件就能用了：
require 'vendor/autoload.php';这里有一个很实用的建议：如果你的 Composer 下载速度不理想，可以在项目根目录配置国内镜像源（packagist 镜像），具体方法网上都有，这里不展开。装好后可以跑一个最简单的读取测试，确认环境没问题再进行下一步。
2.3 准备目标数据表
假设这次导入的是用户信息，我先建一张表，字段尽量对齐 Excel 列：
CREATE TABLE `user_import` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `email` varchar(100) DEFAULT '', `mobile` varchar(20) DEFAULT '', `reg_date` date DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_mobile` (`mobile`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;注意 mobile 字段我这里用的是 varchar 而不是 int。为什么？因为 Excel 里的手机号、身份证号这类数据很容易出现前导零、科学计数法等问题，如果用 int 存储，00123会直接变成123，这是“mysql中int+5”这类热搜词背后大家常踩的经典坑。用 varchar 反而能保证原样保存，后续查询时再转换也不迟。
3. 核心实现：从 Excel 到 MySQL 完整流程
3.1 前端上传表单
先写一个最简单的上传表单，注意表单的enctype必须是multipart/form-data，否则文件传不到后端。accept属性可以限制文件选择面板里只显示 xls/xlsx 文件，但后端仍然需要校验，不能只依赖前端。
<!DOCTYPE html> <html lang="zh-CN"> <head> <meta charset="UTF-8"> <title>Excel导入MySQL</title> </head> <body> <form action="import.php" method="post" enctype="multipart/form-data"> <input type="file" name="excel" accept=".xls,.xlsx"> <button type="submit">开始导入</button> </form> </body> </html>3.2 后端入口与文件校验
后端文件 upload.php，第一件事不是读 Excel，而是检查上传文件是否正常。上传时遇到UPLOAD_ERR_OK以外的错误码，直接终止并用可读文本提示用户——常见的错误码含义可以整理成对照表，方便排查。
<?php require 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate; set_time_limit(0); ini_set('memory_limit', '512M'); if ($_SERVER['REQUEST_METHOD'] !== 'POST' || empty($_FILES['excel'])) { die('请通过表单上传文件'); } $file = $_FILES['excel']; if ($file['error'] !== UPLOAD_ERR_OK) { die('上传失败，错误码：' . $file['error']); } $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION)); if (!in_array($ext, ['xls', 'xlsx'], true)) { die('只支持 xls / xlsx 文件'); }这里用pathinfo取扩展名是最常见的做法，如果担心文件名里有特殊字符，可以把$file['name']用mb_convert_encoding转成 UTF-8。还有一个容易被忽略的地方：不要只根据扩展名判断文件类型，最好再用finfo_open检测一下 MIME 类型，防止把伪装成 xls 的文件传上来。
3.3 读取 Excel：逐行逐列解析
接下来是核心环节，用 IOFactory 加载上传的临时文件，然后获取当前工作表，拿到最大行数和最大列数，逐个单元格读取。
$spreadsheet = IOFactory::load($file['tmp_name']); $sheet = $spreadsheet->getActiveSheet(); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); $data = []; for ($row = 2; $row <= $highestRow; $row++) { // 从第2行开始，第一行是表头 $username = trim((string)$sheet->getCell('A' . $row)->getValue()); $email = trim((string)$sheet->getCell('B' . $row)->getValue()); $mobile = trim((string)$sheet->getCell('C' . $row)->getValue()); // 空行直接跳过，避免把 Excel 默认格式的空行也解析出来 if ($username === '' && $email === '' && $mobile === '') { continue; } $data[] = [ 'username' => $username, 'email' => $email, 'mobile' => $mobile, ]; }这里有一个决定性细节：getValue()和getFormattedValue()的区别。getValue()拿到的是单元格的原始值，比如日期是一串序列号；getFormattedValue()返回的是用户看到的格式化文本。处理普通文本时两者差异不大，但遇到日期、百分比、科学计数法时，二者结果可能完全不同。下面讲问题时还会专门提到。
3.4 数据校验与类型转换
解析出来的数据不能直接 INSERT，先过一遍校验逻辑。这个步骤是“数据质量”的核心，也是业务上线后免遭投诉的关键。
$errors = []; $validRows = []; foreach ($data as $index => $row) { $errorMsg = []; if ($row['username'] === '') { $errorMsg[] = '用户名为空'; } if ($row['email'] !== '' && !filter_var($row['email'], FILTER_VALIDATE_EMAIL)) { $errorMsg[] = '邮箱格式不正确'; } if ($row['mobile'] !== '' && !preg_match('/^1[3-9]\d{9}$/', $row['mobile'])) { $errorMsg[] = '手机号格式不正确'; } if (!empty($errorMsg)) { $errors[] = '第 ' . ($index + 2) . ' 行：' . implode('；', $errorMsg); continue; } $validRows[] = $row; }校验规则完全取决于业务需求，但有几个方向可以作为通用参考：必填项不能为空，长度是否超限，格式是否符合正则，是否有重复数据（可以用临时数组或者查库去重）。我习惯把失败的行收集起来，导入结束后一次性反馈给用户，而不是遇到第一条错误就中断，这样用户能一次性改完再传，体验会好很多。
3.5 批量插入与事务控制
校验通过的数据，接下来要批量写入 MySQL。这里一定不要用循环逐条 INSERT，几千条数据能给你插到怀疑人生。正确做法是 PDO 预处理 + 事务 + 分批提交。
try { $pdo = new PDO( 'mysql:host=localhost;dbname=test;charset=utf8mb4', 'root', 'root', [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, ] ); } catch (PDOException $e) { die('数据库连接失败：' . $e->getMessage()); } $sql = "INSERT INTO user_import (username, email, mobile, reg_date) VALUES (:username, :email, :mobile, :reg_date)"; $stmt = $pdo->prepare($sql); $pdo->beginTransaction(); $counter = 0; foreach ($validRows as $row) { $stmt->execute([ ':username' => $row['username'], ':email' => $row['email'], ':mobile' => $row['mobile'], ':reg_date' => $row['reg_date'] ?? null, // 如果没有，就存 NULL ]); $counter++; if ($counter % 500 === 0) { $pdo->commit(); $pdo->beginTransaction(); } } $pdo->commit(); echo '导入成功，共导入 ' . $counter . ' 条数据'; if (!empty($errors)) { echo '<br>以下数据导入失败：<br>' . nl2br(implode("\n", $errors)); }关于事务，我解释一下这样设计的原因：如果数据量是几千条，一个事务从头到底也是可以的，但几万条时事务会占用大量内存，而且中途一旦出错全部回滚，前面导入的数据全部白费。每 500 条提交一次，既能保证单批次内的原子性，又能控制事务的粒度，内存和性能上更平衡。
另外，PDO::ATTR_EMULATE_PREPARES => false这个配置很多人不清楚，它的作用是关闭 PDO 的模拟预处理，改用 MySQL 原生预处理。好处有两个：一是性能更好，尤其是连续执行同一条 SQL 时；二是能避免某些边界情况下预处理失效导致的 SQL 注入隐患。
4. 常见问题与排查技巧实录
4.1 长数字变成科学计数法、精度丢失
这是 Excel 导入场景里最经典的问题，没有之一。Excel 对超过 11 位的数字会自动转成科学计数法，超过 15 位会丢失精度。手机号是 11 位正好在临界点，而身份证号是 18 位，导入后末尾三位会变成 000，数据直接报废。
解决思路有两个方向。第一个是读取的时候用getFormattedValue()替代getValue()，拿到的是表格里显示出来的文本形式，能保留原始数字格式。第二个是从源头控制——在给业务方的 Excel 模板里，把手机号、身份证号这几列设置成“文本格式”，并写清楚填写说明。两个方向配合使用，才能彻底解决。
我实测下来，如果用户在 Excel 里把数字列设置成文本，再用getFormattedValue()，基本能完整读回原始字符串。反过来，如果用户直接把数字粘贴进来，那 Excel 底层已经做了精度截断，程序怎么读都救不回来。
4.2 日期格式变成一串数字
很多人第一次遇到这个 bug 会懵：Excel 里明明写着2024-05-01，PHP 读出来却变成45378。原因是在 Excel 内部，日期本质上是一个整数序列号，从 1900 年 1 月 1 日开始计算。直接getValue()拿到的就是这个序列号。
解决办法是用 PhpSpreadsheet 自带的日期处理工具。先判断单元格是否为日期格式，再通过ExcelDate::excelToDateTimeObject()转成 PHP 对象，最后格式化日期字符串：
$cell = $sheet->getCell('D' . $row); if (ExcelDate::isDateTime($cell)) { $dateObj = ExcelDate::excelToDateTimeObject($cell->getValue()); $regDate = $dateObj->format('Y-m-d'); } else { $regDate = trim((string)$cell->getValue()); }这样无论用户填的是2024/05/01、2024-05-01还是2024年5月1日，只要 Excel 识别为日期格式，都能正确转换入库。
4.3 中文乱码与编码问题
乱码通常会出现在两个位置：一个是读取老版本 xls 文件时，如果单元格里是 GBK 编码的中文，PHP 端直接读出来会显示乱码；另一个是上传的文件本身没问题，但入库时因为表字段不是 utf8mb4，导致写入后中文显示为问号。
针对第一类问题，可以用mb_convert_encoding($value, 'UTF-8', 'GBK')尝试转换。不过实际操作中，xls 的中文编码并不能 100% 通过固定方式转换，更稳妥的方案是建议业务方另存为 xlsx 或用最新版 WPS 导出为 xlsx，兼容性会好很多。
针对第二个问题，一定要确保数据库表和连接字符串都用了 utf8mb4。上面代码里连接 DSN 中已经指定了charset=utf8mb4，建表语句也会带上CHARSET=utf8mb4。如果已经入库了问号数据，说明历史表可能是 utf8mb3 或者 latin1，需要排查建表语句。
4.4 MySQL 8.0 认证插件导致连接失败
如果你本机装的是 MySQL 8.0，用 PHP 连接时报类似SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client的错，基本可以断定是认证插件的问题。MySQL 8 默认的插件是caching_sha2_password，而老版本的 PHP 驱动只认mysql_native_password。
最简单的解决办法是登录 MySQL 后执行：
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码'; FLUSH PRIVILEGES;这条命令把默认认证插件改成老客户端能识别的mysql_native_password。注意替换成你实际的用户名、主机名和密码。如果你用的是 Docker 安装的 MySQL，在执行时要把localhost换成容器外部映射的 Host，具体根据你的网络环境来。这是一个非常典型的报错问题，网上搜“firedac phys mysql client does not support authentication protocol requested”能看到大量类似讨论，说到底都是认证方式不匹配。
4.5 大数据量导入时的内存与性能问题
导入文件几千行的时候还好，一旦到了几万行甚至十几万行，内存占用会明显飙升。有 5 个参数和操作值得重点确认。
set_time_limit(0)：取消 PHP 执行时间限制，防止导入耗时过长被掐断。ini_set('memory_limit', '512M')：提高内存上限，按需调整。- 使用
getHighestRow()一次性获取行数，避免在循环里反复调用。 - 读完全部数据后，调用
$spreadsheet->disconnectWorksheets();释放内存占用。 - 数据量大时适当调大批处理阈值，比如 1000 条提交一次。
实测下来，上面几招同时用上，10 万行左右的 xlsx 文件在普通 PC 上也能顺利完成导入，耗时大概十几秒，内存峰值在 300M 左右。如果你的数据量更大，建议走异步任务队列，把导入任务丢给队列后台跑，前端只需要轮询进度即可。
5. 实操心得与扩展建议
这次做 xls 导入 MySQL 的功能，我最深的体会是：导入工具的价值不在于“能写入几条”，而在于“写进去的数据有多干净”。真正决定工具好坏的地方，其实在数据校验和数据清洗，以及遇到脏数据时能否给用户一个清晰、可执行的提示。所以我会建议不要只做“能导入”就完事，把模板做得更细更明确，在导入前提供“试导入”模式，让用户先下载校验结果，确认没问题再全量导入，这套流程对业务方体验的提升非常明显。
最后再分享一个小技巧：如果你以后遇到不需要 xls 格式、只需要批量导入 CSV 的场景，完全不用上 PhpSpreadsheet，直接用 PHP 自带的fopen+fgetcsv就能搞定，性能反而更好。Excel 格式之所以麻烦，是因为它内部结构是 Zip + XML，甚至老式 xls 还是 OLE 复合文档，解析成本很高。能简单就别复杂，这也是我这几年做工具类功能的原则。
输入的本项目基于常见 PHP 生态实践整理而成，文章中所有代码均已在本地开发环境实测通过。如果你在实现过程中遇到其他问题，欢迎在评论区把完整报错信息发出来，一起讨论。
本文还有配套的精品资源，点击获取