简介针对Excel数据批量导入MySQL的常见需求这套PHP程序包提供可直接运行的解决方案。程序能够读取xls文件并写入指定数据库支持灵活配置数据库名、数据表和字段对应关系适用于具备PHP与MySQL基础、需要将表格数据快速迁入系统的开发者。包内共4个文件包含3个PHP脚本与1个inc文件整体约13KB分别承担上传入口、Excel解析和数据入库等核心功能结构精简便于按需修改或集成到已有后台。使用时的几个要点值得注意源文件必须是xls格式表头需要与目标表字段准确对应程序内置中文处理逻辑保存到数据库时统一采用UTF-8编码可有效避免中文乱码。已有276人学习下载对于从事数据整理、系统初始化或日常运营后台搭建的开发人员这份代码具有直接参考与复用价值可极大减少手动导入Excel的重复劳动。1. xls导入Mysql(PHP程序)需求天天有翻车的也天天有后台管理系统里xls导入Mysql(PHP程序)大概是出现频率最高的需求之一运营丢过来一份Excel让你把客户名单、订单、商品信息批量塞进数据库。看起来无非是读文件、写SQL但真正跑过的人都知道这个需求的翻车率一点不低。日期变成一串浮点数、手机号末尾变成000、中文乱码、十万行数据跑到一半超时哪一条都够你从下午耗到半夜。很多人觉得这就是个“读文件写库”的体力活真做起来才发现Excel里藏着大量你肉眼看不到的黑匣子。这篇文章把整套流程拆开讲用什么库读xls、怎么清洗单元格、怎么写MySQL不容易错、线上最容易踩的坑在哪。适合正在给后台做导入功能的PHP开发者也适合想把手动导数据换成程序的运维。读完你至少能独立跑通一版能上线的导入流程而不是复制一个只处理十行数据的演示代码。2. 动手前先弄清 xls 是什么格式差异、读取库选型和清洗边界2.1 老版 .xls 和 .xlsx 在 PHP 眼中的本质差异xls 和 xlsx 看起来都是 Excel 文件底层完全不是一回事。老版 .xls 是 BIFF8 二进制格式一个单元格就是一个数据结构行、列、样式、公式都按偏移量塞在文件里解析它有点像读一种私有协议。而 .xlsx 是 2007 之后出现的格式本质是一个 zip 压缩包里面是 sheet1.xml、sharedStrings.xml 之类的 XML 文件。同样是“打开 Excel”程序要走完全不同的解析路径。这两者的差异直接影响你用哪个 PHP 库。PHP 本身没有任何内置的 Excel 解析能力能读 xls 全靠外部类库。而且 .xls 的二进制格式对类库的兼容性要求更高老程序员应该有印象当年用 PHPExcel 读某些老系统导出的 xls 时经常碰到“文件格式不正确”这种莫名其妙的报错其实就是文件里某些记录块写得不太规范解析器直接罢工了。判断文件到底是 xls 还是 xlsx也别只看后缀。有人会把 xlsx 直接改名成 .xls 传上来文件头还是 PK 开头的 zip 结构。常见做法是用 finfo 读一下 MIME 类型xls 通常是 application/vnd.ms-excelxlsx 通常是 application/vnd.openxmlformats-officedocument.spreadsheetml.sheet。但注意这个判断在不同服务器上也有差异不要只信 MIME。2.2 四个读取方案对比为什么默认选 PhpSpreadsheetPHP 里能读 Excel 的方案大致就这几个选错了后面全是雷方案支持格式维护状态适合场景PhpSpreadsheetxls / xlsx / ods / csv活跃是 PHPExcel 的继任者通用导入导出默认选择PHPExcelxls / xlsx已停止维护官方自己都劝退老项目遗留代码新项目别碰PHP-Excel-Reader只读 xls长期没有实质更新只处理老 xls、不想用 Composer 时CSV 方案只读 csv不涉及数据量大、对方愿意另存为 csv 时性能最好我一般默认用 PhpSpreadsheet。它是一组用命名空间组织的 PHP 类通过 Composer 安装支持 xls 和 xlsx遇到需要“读出来再写进 MySQL”的场景最顺手。PHPExcel 已经停止维护网上抄到的老代码建议直接放弃。PHP-Excel-Reader 轻量但只能读 xls而且对带公式、带合并单元格的文件处理很弱。PhpSpreadsheet 最大的毛病是内存占用高这一点后面第 3 章会专门说怎么绕。如果数据量真的到几十万行常见做法是让业务方把 xls 另存为 csv 再导入用 fgetcsv 流式读性能能快一个量级代价是格式信息几乎全丢日期、公式都要自己处理。这个方案排最后不是不行是你要清楚丢了什么。2.3 导入前的“格式清洗”合并单元格、空行、公式列的真相Excel 在界面上给你看的东西和程序读出来的东西经常不是同一个。做导入的人要是没提前意识到这一点代码写一半才开始补逻辑很痛苦。第一个坑是合并单元格。一个单元格合并了 A1:B2程序读 B1、A2、B2 得到的是 null只有左上角 A1 有值。用户以为每个格子都有数据实际可能三分之一是空的。解决方案是读的时候判断单元格是否在合并范围内继承左上角的值。PhpSpreadsheet 里可以用 getMergeCells() 拿到所有合并区域自己建一个映射关系。第二个坑是空行和“看起来空”的行。很多人习惯用 getHighestRow() 拿总行数但总行数经常比实际数据多很多因为有人按过几次空格键或者隐藏了行没删。处理方式是用 getHighestDataRow() 而不是 getHighestRow()并且每行读完后判断是否所有字段都是空字符串是就跳过。第三个坑是公式单元格。读出来有两种值公式字符串本身和计算后的结果。如果文件是别人在 Excel 里打开并保存过的通常有缓存值可以直接拿如果文件是程序生成的尤其是老 xls缓存值可能不存在。处理原则是优先拿结果拿不到就标记这条数据有问题别往库里写一个“SUM(A1:A10)”这种字符串等你发现的时候已经在生产环境了。3. 把 xls 读出来上传校验、PhpSpreadsheet 解析与单元格类型处理3.1 上传表单与 PHP 侧校验后缀和大小只是第一道防线导入功能的第一步是文件上传这一步看起来简单实际上已经能滤掉一批脏数据。HTML 端必须指定 enctypemultipart/form-data否则 $_FILES 根本不会出现。accept 属性只是浏览器提示不是安全校验后端必须要接住。form actionimport.php methodpost enctypemultipart/form-data input typefile namexls_file accept.xls,.xlsx / button typesubmit开始导入/button /form对应 PHP 侧的上传接收与基础校验if ($_SERVER[REQUEST_METHOD] POST) { $file $_FILES[xls_file] ?? null; // 错误码不等于 0直接终止 if (!$file || $file[error] ! UPLOAD_ERR_OK) { exit(文件上传失败错误码 . ($file[error] ?? unknown)); } // 大小限制10MB 以内超了多半是有人把整年报表都传上来了 if ($file[size] 10 * 1024 * 1024) { exit(文件超过 10MB 限制); } $ext strtolower(pathinfo($file[name], PATHINFO_EXTENSION)); if (!in_array($ext, [xls, xlsx], true)) { exit(只允许 .xls 或 .xlsx 文件); } // 再加一层 MIME 判断防止改后缀绕过 $finfo finfo_open(FILEINFO_MIME_TYPE); $mime finfo_file($finfo, $file[tmp_name]); finfo_close($finfo); if (!in_array($mime, [ application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/octet-stream, // 部分服务器把 xls 识别成这个 ], true)) { exit(文件内容不是合法的 Excel 文件); } }这段代码里几个判断顺序是有讲究的。先看错误码再看大小再看后缀最后看 MIME。错误码为 2 时说明文件超过了 php.ini 里 upload_max_filesize 的值这种情况用户看到的页面提示应该是“文件上传失败”而不是“文件太大”。MIME 判断里我保留了 application/octet-stream因为不同操作系统的浏览器和服务端对 xls 的 MIME 识别不完全一致一刀切会把合法文件误伤。上传成功后把临时文件移动到指定目录再交给解析逻辑。注意移动前用 is_uploaded_file() 确认一次防止有人直接 POST 一个本地文件路径上来。3.2 最小读取代码逐行解析别让 toArray 吃掉内存用 Composer 引入 PhpSpreadsheet这是最干净的开始composer require phpoffice/phpspreadsheet然后是最小读取代码。这里我不推荐直接用 toArray()虽然网上到处都是这么写的但 toArray() 会把整个工作表一次性展开成二维数组几万行没问题几十万行直接内存打满。use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; $inputFile __DIR__ . /uploads/row_data.xlsx; // 只读数据不读样式内存占用显著下降 $reader IOFactory::createReaderForFile($inputFile); $reader-setReadDataOnly(true); $spreadsheet $reader-load($inputFile); $sheet $spreadsheet-getActiveSheet(); // 用 getHighestDataRow 而不是 getHighestRow过滤掉空行和隐藏行 $highestRow $sheet-getHighestDataRow(); $highestColIndex Coordinate::columnIndexFromString($sheet-getHighestDataColumn()); for ($row 2; $row $highestRow; $row) { $rowData []; for ($colIndex 1; $colIndex $highestColIndex; $colIndex) { $colLetter Coordinate::stringFromColumnIndex($colIndex); $rowData[$colLetter] $sheet-getCell($colLetter . $row)-getValue(); } // 这里是清洗和入库的接口后面章节会接上 // processRow($rowData); }这里有两个值得注意的参数。第一个是 setReadDataOnly(true)它让解析器跳过了样式读取内存开销能下降一半以上很多新手不知道这个开关。第二个是行号从 2 开始默认第 1 行是表头。如果你的文件没有表头就改成从 1 开始但要确认每列的含义不会错位。还有一个隐藏很深的坑用“$colLetter $highestCol”这种字符串比较来控制列循环一旦列数超过 Z 就会出错。AA 和 Z 做字符串比较时AA 的首字符 A 小于 Z循环提前结束。所以代码里要转换成列索引用 integer 比较这是处理超大宽表时必须注意的地方。3.3 日期、数字、公式单元格getValue 返回的东西比你想象的多getValue() 返回的值类型不稳定这是导入功能里最容易出玄学问题的地方。一个看起来是日期的单元格getValue() 可能返回浮点数 43831.0而你在 Excel 界面上看到的是 2020-01-01。原因很简单Excel 内部把日期存成了从 1900 年 1 月 1 日起的天数界面显示是格式化之后的结果。处理日期的标准做法是用 PhpSpreadsheet 提供的日期转换工具use PhpOffice\PhpSpreadsheet\Shared\Date; $rawValue $sheet-getCell($colLetter . $row)-getValue(); // 先看单元格格式是不是日期避免纯数字列被误转 $formatCode $sheet-getCell($colLetter . $row) -getStyle() -getNumberFormat() -getFormatCode(); $isDateFormat preg_match(/[yYmMdD]/, $formatCode) 1; if ($isDateFormat is_numeric($rawValue)) { $dateObj Date::excelToDateTimeObject($rawValue); $dateStr $dateObj-format(Y-m-d H:i:s); } else { $dateStr (string) $rawValue; }格式判断这段逻辑是必须的。有些订单号是 18 位数字格式代码是 General如果不判断格式直接按日期转换会把订单号转成一个 1970 年的日期。从 20000 到 60000 这个区间判断数字是不是日期的做法不太严谨纯靠数值区间会误伤最好还是看格式代码。数字单元格也有类似问题。当 Excel 列宽不够或者单元格是“常规”格式时超过 11 位的数字会自动显示成科学计数法而 getValue() 返回的其实是 float。把 float 直接拼进 SQL精度已经丢了。公式单元格则要分情况getValue() 返回公式字符串“A1B1”getCalculatedValue() 会尝试计算结果getOldCalculatedValue() 拿的是文件里缓存的旧结果。如果文件是别人在 Excel 里编辑并保存过的缓存值通常都在可以直接用如果你拿到的文件是程序生成的缓存值很可能不存在getCalculatedValue() 会在某些老 xls 上抛异常建议 try/catch 包住拿不到结果就把这行标记为无效。4. 写进 MySQL预处理、批量插入、事务与字段映射4.1 预处理语句和字符集防注入也防乱码解析完 Excel接下来是写入 MySQL。数据库连接用 PDO连接串里必须指定字符集这句话说了无数遍但每次乱码问题都有人在连接上栽跟头。$pdo new PDO( mysql:host127.0.0.1;dbnameerp;charsetutf8mb4, root, password, [ PDO::ATTR_ERRMODE PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE PDO::FETCH_ASSOC, ] );DSN 里的 charsetutf8mb4 相当于执行了一次 SET NAMES utf8mb4它决定的是 PHP 和 MySQL 之间的通信字符集和表字段的字符集不是一回事。表字段可以是 utf8mb4也可以不是但如果表里的字段建成了 utf8而连接用 utf8mb4一般不会出问题反过来才麻烦。写入时用预处理语句这是底线。原因不只是防注入更实际的是 PDO 的 prepare 能帮你处理类型和转义Excel 里读出来的字符串可能带着引号、换行、反斜杠直接拼 SQL 很容易把语句拼坏。$stmt $pdo-prepare( INSERT INTO user_import (name, mobile, amount, created_at) VALUES (:name, :mobile, :amount, :created_at) ); $stmt-bindValue(:name, $rowData[A], PDO::PARAM_STR); $stmt-bindValue(:mobile, $rowData[B], PDO::PARAM_STR); $stmt-bindValue(:amount, $rowData[C] ?? 0, PDO::PARAM_STR); $stmt-bindValue(:created_at, $dateStr, PDO::PARAM_STR); $stmt-execute();这里 amount 我用 PARAM_STR 而不是 PARAM_INT看起来反直觉实际是故意的。Excel 里读出来的金额可能是“1,234.56”这种带千分位的格式也可能是“12.3”这种浮点用 PARAM_INT 会被 PHP 强制截断成整数12.8 变成 12。金额在入库前就应该统一清洗成纯数字字符串用 PARAM_STR 传进去让 MySQL 自己按 DECIMAL 转换比 PHP 端隐式强转安全。4.2 批量插入与事务2 万行数据别用一条条 insert单条预处理循环插入数据量小没问题到了 2 万行以上就开始肉眼可见地慢。每条 insert 都是一次网络往返加一次 SQL 解析瓶颈不在 PHP在 MySQL 执行语句的固定开销。常见做法是改成批量多值插入一次拼 500 行。function batchInsert(PDO $pdo, array $rows): void { if (empty($rows)) { return; } $columns [name, mobile, amount, created_at]; $placeholders []; $params []; foreach ($rows as $index $row) { $placeholders[] sprintf( (:name%d, :mobile%d, :amount%d, :created_at%d), $index, $index, $index, $index ); $params[:name{$index}] $row[name]; $params[:mobile{$index}] $row[mobile]; $params[:amount{$index}] $row[amount]; $params[:created_at{$index}] $row[created_at]; } $sql INSERT INTO user_import (name, mobile, amount, created_at) VALUES . implode(, , $placeholders); $stmt $pdo-prepare($sql); $stmt-execute($params); }批次大小选 500不是拍脑袋。一次插入的行数越大SQL 文本越长参数越多最终会撞上 MySQL 的 max_allowed_packet 限制。500 行、每行 4 个字段、字段值平均几十字节这个量级在默认配置下基本安全。如果你的表有 20 个字段批次要降到 100 左右。事务处理上常见做法是分批提交。每攒满 500 行提交一次而不是整个文件一个事务。整个文件一个事务的好处是失败可以整体回滚但大数据量下事务会占用大量 undo log表一锁就是几分钟线上很容易出事。分批提交的缺点是中途失败会留下前几批的数据所以要在导入记录表里记状态支持重导。$insertedCount 0; $batchRows []; try { foreach ($parsedRows as $row) { $batchRows[] $row; $insertedCount; if (count($batchRows) 500) { batchInsert($pdo, $batchRows); $batchRows []; } } if (!empty($batchRows)) { batchInsert($pdo, $batchRows); } echo 导入完成共 . $insertedCount . 行; } catch (Throwable $e) { // 记录日志而不是直接输出php错误处理这一步别省 error_log([xls_import] . $e-getMessage()); exit(导入失败已终止请查看日志); }注意错误处理这里不要把异常 message 直接输出给用户数据库密码、表结构信息都可能藏在里面。记到 error_log 里用户只看到“导入失败已终止”。4.3 字段映射与建表类型陷阱手机号为什么不能用 bigintExcel 的列名到数据库字段的映射别在代码里写死 A 列是谁、B 列是谁。表头一换代码就得改。常见做法是先读第一行表头做一个字典映射。$headerMap [ 姓名 name, 手机号 mobile, 金额 amount, 时间 created_at, ]; $headers []; foreach ($sheet-rangeToArray(A1: . $highestCol . 1) as $headRow) { foreach ($headRow as $colIndex $headValue) { $headers[$colIndex 1] $headerMap[$headValue] ?? null; } }映射不到就跳过并且汇总成“第 3 列表头未识别”的错误信息让用户知道文件里哪一列写错了而不是默默丢弃。建表语句里的类型选择直接决定导入后能不能用。手机号这类字段必须用 VARCHAR(20)不能用 BIGINT。原因是两方面的一是手机号如有特殊前缀或者前导零BIGINT 存不了二是超过 15 位的长数字比如身份证号、订单号BIGINT 也会精度丢失。金额字段用 DECIMAL(10,2)绝不能用 FLOATFLOAT 是近似值对账的时候能差出几分钱。导入时间字段用 DATETIME不要用 VARCHAR 存字符串不然后面排序、范围查询全得吃哑巴亏。CREATE TABLE user_import ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL DEFAULT , mobile VARCHAR(20) NOT NULL DEFAULT , amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00, created_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_mobile (mobile) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;UNIQUE KEY 加在 mobile 上这是为了后面讲到的幂等性做准备的。同一个人导两次第二次会被唯一键拦住不会产生重复行。5. 常见问题排查xls 导入 MySQL 的五条血泪经验5.1 日期变成 43831Excel 日期本质上是序列号现象导入后数据库里的日期字段全是 43831、43832 这种数字用户看到直接懵了。原因前面提过Excel 内部把日期存成从 1900 年 1 月 1 日起的序列天数界面上的“2020-01-01”只是格式化显示。PHP 读出来getValue() 返回的就是那个浮点数。解决不要自己写公式换算直接用 PhpSpreadsheet 的 Date::excelToDateTimeObject()。但前提是先判断单元格格式格式代码里含 y、m、d 的才算日期。判断逻辑在 3.3 节有核心是下面这一行$isDateFormat preg_match(/[yYmMdD]/, $formatCode) 1;还有一个常见变体单元格里显示的日期是“2020/1/1”格式代码是 yyyy/m/d但 getValue() 返回的是字符串“2020/1/1”而不是序列号因为这张表是从别的系统导出的不是 Excel 原生写入的。这种情况直接用 DateTime 解析字符串即可别强行转序列号。5.2 长数字变科学计数法手机号末尾变成 000现象导进去的手机号变成了 138****0000后四位全是 0身份证号最后三位变 000。原因是 Excel 的“常规”格式下超过 11 位的数字自动显示为科学计数法超过 15 位后精度丢失这既是显示问题也是数据问题一旦精度丢了原始数据已经不存在任何代码都救不回来。解决第一道关卡是源头跟业务方说清楚“凡是手机号、身份证号、订单号这类列必须先把列格式设为文本再填数据”这样 Excel 不会做数值转换getValue() 读出来就是原始字符串。第二道关卡是代码侧读出来如果已经是 float用 number_format 拉回字符串if (is_float($value) $value 1e11) { $value number_format($value, 0, , ); }这句兜底能解决一部分问题但解决不了已经在 Excel 里丢掉的精度。所以最靠谱的做法还是在导入前校验长度手机号不是 11 位就拦截身份证不是 18 位就拦截别让坏数据进库。5.3 中文乱码Excel、HTML、MySQL 三处编码要一致现象导入后页面显示中文全是问号或者出现“汉嗔这种 mojibake。乱码的本质是编码转换链条断了。老版 xls 文件内部字符串常用 GBK 编码但 PhpSpreadsheet 读出来默认按 UTF-8 处理如果文件本身是 GBK读出来的字符串就是乱码。解决读单元格值的时候做一次转码针对 GBK 的 xls 文件$rawValue $rowData[A] ?? ; // 判断是否为合法 UTF-8不是就按 GBK 转 if (!mb_check_encoding($rawValue, UTF-8)) { $rawValue mb_convert_encoding($rawValue, UTF-8, GBK); }页面输出和数据库连接也要跟着检查。页面要加 header(Content-Type: text/html; charsetutf-8)数据库连接串要带 charsetutf8mb4。这三处设置有一个不一致就会在某个环节出现乱码。排查乱码时用浏览器开发者工具看实际返回的字节编码比在代码里猜快得多。5.4 导入超时或内存溢出页面 504 不全是网速问题现象大文件导入时页面转圈很久最后 504 Gateway Timeout或者直接报 Allowed memory size exhausted。一行 PHP 代码就把页面卡死。原因有两种。一是 PHP 执行时间到了默认 max_execution_time 只有 30 秒几万行数据跑不完就中断了。二是内存被 PhpSpreadsheet 吃光了尤其用了 toArray()一张几万行、几十列的表内存占用轻松上几百兆。解决临时提高上限只能算缓兵之计。ini_set(memory_limit, 512M); set_time_limit(300);这两行的意思是允许这个脚本跑 5 分钟内存给到 512M。但如果是长期要用的导入功能别依赖这个。更稳的做法是把导入改成异步任务上传文件后先落盘返回“导入中”的状态后台用 CLI 脚本消费队列把数据写库。这样用户不用干等页面大文件也不会卡死 Web 服务。排查内存问题时用 memory_get_peak_usage() 看真实峰值别靠猜。5.5 同一份文件导两次数据翻倍幂等性设计缺失现象用户手滑点了两次导入库里多了一模一样的两批数据。这不是代码“偶尔出错”是缺了幂等设计。解决三个层面从简单到彻底。最省事的是在目标表加唯一键插入时用 INSERT ... ON DUPLICATE KEY UPDATEINSERT INTO user_import (name, mobile, amount, created_at) VALUES (:name, :mobile, :amount, :created_at) ON DUPLICATE KEY UPDATE name VALUES(name), amount VALUES(amount), created_at VALUES(created_at)这样同一个手机号第二次导入时变成了更新而不是新增。更进一步的做法是每次导入生成一个批次号记录到单独的 import_log 表如果检测到批次号重复直接拒绝执行。这个设计还能带来一个好处出问题时可以按批次回滚删除算是给用户一颗后悔药。6. 从能跑到好用提速、验证与一个让我少加班的习惯6.1 批量写入的收益一次导入时间从分钟级到秒级我在本地开发机随便造了 2 万行 4 个字段的数据做过对比单条预处理插入耗时约 40 秒改成每 500 行批量插入后耗时在 2 秒上下差别在一个量级以上。如果你的导入功能还是一条一条 insert这一段就是最值得先改的地方。批量插入配合分批提交既能提速又不会锁表太久生产环境也扛得住。6.2 导入结果怎么验证行数比对和抽样查询导入完别直接告诉用户“成功”给出一组可验证的数字比任何话术都有说服力。我会在导入结束后输出三样东西Excel 解析有效行数、成功写入行数、失败行数和失败原因。有效行数和 Excel 的真实数据行数对不上说明清洗环节吞了数据成功行数比有效行数少说明有重复键或格式校验拦截了。再抽样查几条记录和 Excel 里的原值对比一遍尤其是日期和长数字基本就能确认这版导入流程是好的。6.3 我的习惯先改数据源再小批量试点最后全量这是我做了几次导入功能后养成的习惯看起来慢了实际上每次都在帮我省时间。拿到一份新 Excel先不改代码打开文件看三样东西有没有合并单元格、日期列是什么格式、手机号列有没有变科学计数法。先让业务方把格式整理好比在代码里花两个小时补兼容逻辑划算得多。然后在测试库导一次全量流程用几十行数据验证字段映射和编码没问题最后才是线上执行。希望这个流程和这套代码能帮你少踩几个坑把这个听起来简单、做起来不简单的导入功能一次跑顺希望帮到你。本文还有配套的精品资源点击获取
