尧图网站建设 ：Excel批量数据入库的完整实践)
简介面向需要将xls表格数据批量导入MySQL的PHP开发者这套轻量源码包提供了完整可运行的导入方案。压缩包仅13KB包含4个文件由3个PHP脚本和1个inc辅助库组成分别承担Excel文件解析、数据行读取与数据库写入等职责模块划分清晰便于在此基础上按业务需求调整。程序支持自定义数据库名、表名及字段映射导入前需将表格保存为xls格式并确保表头与目标表字段一一对应同时内置中文处理逻辑入库数据统一以UTF-8编码保存可避免中文乱码问题适合后台数据迁移、Excel批量录入或临时数据整理等场景。资源体积小巧不依赖复杂环境解压后即可部署到支持PHP的服务器或本地环境使用。目前已有276人学习使用对想了解PHP操作Excel并写入MySQL的实现思路、希望快速搭建导入工具的开发者具有一定的参考价值。1. xls导入Mysql(PHP程序)为什么 Excel 批量入库值得认真做后台管理网站上最常被低估的需求就是把 xls 批量导入 MySQL。仓库那边会递来一个 8000 行的商品表运营会递来 2 万条会员名单如果你只给一个逐条新增的表单他们就会一条条复制粘贴到天亮。最简单的 xls导入Mysql(PHP程序)方案是这样的用户上传 xls 文件PHP 用 PhpSpreadsheet 把单元格读成数组经过字段映射和去重校验之后用批量 INSERT 加事务写入 MySQL全程不到 300 行代码就能把原来半天的转录变成一次上传。这篇文章适合要做数据导入功能的 PHP 后端同学也适合接手老导入模块想重构的人。2. 读取 xls 的 PHP 方案PhpSpreadsheet 的加载、单元格解析与分批读取2.1 为什么旧的 PHPExcel 代码在 PHP 8 上越来越难用在搜索框里输入xls导入Mysql PHP翻出来的旧教程几乎清一色是 PHPExcel 1.8。这个库在 2015 年之后就没有大版本更新网上大量现成代码都建立在它上面拿来跑 PHP 5.6 没问题到了 PHP 7.4 开始冒出弃用警告PHP 8.0 之后更严重——构造函数风格、魔术方法、对象类型提示全部是老写法很多服务器开着 error_reporting(E_ALL)页面还没输出 JSON顶部先铺满一串黄色警告。PhpSpreadsheet 是 PHPExcel 的官方继任库composer 包名是phpoffice/phpspreadsheet。它保留了读取 .xlsExcel 97-2003 二进制格式的能力同时支持 xlsx、csv 等格式命名空间和 API 整体现代化PHP 8 兼容性比老库好得多。我自己的选型标准是四条能读真 xls 二进制文件而不是只支持 xlsx、单元格类型拿到手能判断和控制、支持按行过滤避免大文件撑爆内存、在 PHP 8 下没有成堆弃用提示。PhpSpreadsheet 四条基本满足。如果你手上只有 CSV 数据其实可以不引入这层库用 fgetcsv 原生解析更轻量但 xls 是二进制格式靠 fgetcsv 是读不了的这也是为什么导入模块里 PhpSpreadsheet 成了标配。2.2 最小可用代码20 行把 xls 变成 PHP 数组先看一段能跑通的最小代码它负责把整个工作表读进 PHP 数组?php require __DIR__ . /vendor/autoload.php; use PhpOffice\PhpSpreadsheet\IOFactory; $filePath __DIR__ . /upload/goods.xls; $reader IOFactory::createReaderForFile($filePath); $reader-setReadDataOnly(true); // 只要计算后的值不读公式和样式 $spreadsheet $reader-load($filePath); $sheet $spreadsheet-getActiveSheet(); // 返回二维数组$rows[0] 对应 Excel 第 1 行$rows[0][0] 对应 A 列 $rows $sheet-toArray(null, true, false); foreach ($rows as $lineNo $row) { if ($lineNo 0) { continue; // 跳过表头行 } // $row[0] A列 $row[1] B列 $row[2] C列 ... 这里取单元格内容 if (array_filter($row, fn($v) $v ! null $v ! ) []) { continue; // 跳过整行空白 } // 输出到 MySQL 的逻辑在下一章处理 }IOFactory::createReaderForFile 会自动按扩展名判断用 Xls 还是 Xlsx 读取器所以同一个入口能同时兼容 .xls 和 .xlsx。setReadDataOnly(true) 很关键它会忽略单元格样式和公式定义只把值读出来速度和内存表现都好很多。toArray 的三个参数分别表示空单元格用什么值占位、是否计算公式、是否格式化单元格数据。第三个参数传 false是为了拿到单元格原始值——日期、数字这类内容后面还要自己做转换提前格式化成字符串反而不好判断类型。toArray 对 5000 行以内的小文件非常合适代码短、好调试把表头行跳过去之后直接进业务处理。超过这个量级建议看 2.4 的分批读取方案。2.3 单元格类型暗坑日期、百分比、公式值怎么读才对xls 里的日期本质上不是字符串而是一个浮点数。Excel 把 1900 年 1 月 1 日记为 1之后每过一天数值加 1所以 2024 年 5 月 1 日在底层可能就是 45517 之类的数字。如果你直接把这个值写进 MySQL 的 datetime 字段库里就会出现一堆 45517。百分比同理单元格里看到的是 12.4%实际值却是 0.124。用 PhpSpreadsheet 判断和转换日期单元格常见做法是先看数据类型再用内置的 Date 类转换use PhpOffice\PhpSpreadsheet\Cell\DataType; use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate; $cell $sheet-getCell(C2); $value $cell-getValue(); if ($cell-getDataType() DataType::TYPE_NUMERIC ExcelDate::isDateTime($cell)) { $dateObj ExcelDate::excelToDateTimeObject((float)$value); $dateStr $dateObj-format(Y-m-d H:i:s); // 统一转成 MySQL 日期格式 } elseif (is_float($value) $value 0 $value 1) { $percent round($value * 100, 2) . %; // 0.124 - 12.4% }isDateTime 判断的是单元格数字格式比如单元格格式是 yyyy-m-d它就会返回 true这时候用 excelToDateTimeObject 转成 DateTime 对象再格式化成 MySQL 需要的字符串这一条在导入业务表时几乎是必踩的。还有一个容易忽略的点setReadDataOnly(true) 之后公式单元格读出来的是文件中缓存的计算结果。如果这个 xls 是 WPS 或者老版本 Excel 生成的缓存值不一定是可信的甚至有可能是空。遇到公式列要么在 Excel 侧先复制成值要么在读取时不要开 setReadDataOnly改用 getCalculatedValue() 让 PHP 实时计算一次。2.4 大 Excel 文件的内存控制分片读取与行迭代器toArray 会把整个工作表一次性装进 PHP 内存。我处理过一个 50MB 左右的 xls直接 toArray 的内存峰值超过 300MB虚拟主机环境基本直接 500 错误。这种体量必须改成按行读取。第一种做法是用 ReadFilter 过滤行让加载过程只读前 N 行$reader-setReadDataOnly(true); $reader-setReadEmptyCells(false); $reader-setReadFilter(new class implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter { public function readCell($columnAddress, $row, $worksheetName ): bool { return $row 3000; // 只放过前 3000 行 } }); $spreadsheet $reader-load($filePath);通过匿名类实现 IReadFilter在 readCell 里判断行号是否在范围内行号超出范围直接返回 false内存占用会稳定在低位。读下一批时把上限改成 6000、9000 再重新 load 一次。注意这个过滤器是按行放的不会影响行号本身所以读出来的行号仍然对应 Excel 原始行号。第二种做法是用行迭代器逐行取单元格$worksheet $spreadsheet-getActiveSheet(); foreach ($worksheet-getRowIterator() as $row) { $cellIterator $row-getCellIterator(); $cellIterator-setIterateOnlyExistingCells(true); // 跳过空单元格省内存 foreach ($cellIterator as $cell) { $value $cell-getValue(); // 拿到一个单元格值马上处理不用把整表囤在内存里 } }行迭代器的好处是内存占用恒定特别适合 3 万行以上的大表坏处是代码结构比 toArray 绕每一行的列值需要循环取业务逻辑会嵌进迭代器里。如果你只是做一次性导入脚本可以先让用户把 xls 另存为 xlsx 再上传解析速度会快不少这是一个低成本优化。读取方式内存表现适合场景toArray整表进内存小文件最方便5000 行以内表结构固定ReadFilter 分片每次只留一批稳定几万行分批入库RowIterator内存恒定代码稍复杂超大文件边读边处理3. 写入 MySQL批量 INSERT、事务与性能取舍3.1 为什么逐条 INSERT 到 2 万行就开始卡顿最直观的错误写法是 foreach 循环里每行执行一次 INSERT我自己早年也干过这种事2 万行的商品表导了 40 多秒用户以为页面卡死直接关掉重试结果又重复导入了一次。逐条插入慢在三个地方。第一每条 INSERT 都是独立的 SQL 请求MySQL 要重新做语法解析、权限检查、生成执行计划第二PHP 和 MySQL 之间每执行一次都要走一轮完整网络往返2 万条就是 2 万次往返第三在默认 autocommit 模式下每条 INSERT 都是独立事务InnoDB 要为每条事务准备 redo log这个开销远比想象中大。如果把 2 万条放进一个事务再一次性提交耗时能降到原来的五分之一甚至更低。这里还要提醒一点用字符串拼接 SQL 的方式本身就危险Excel 里的值可能带单引号、反斜杠、换行稍不注意就会拼出语法错误或者注入点。避免这个问题的办法就是预处理占位符这在批量导入里同时也是性能优化因为同一句 INSERT 可以反复执行 N 次MySQL 只需要解析一次。3.2 批量写入的正确姿势预处理 分段事务以商品表为例假设 goods 表有 name、price、stock 三个字段Excel 的 A、B、C 列分别对应这三列。推荐写成这样use PDO; $pdo new PDO( mysql:host127.0.0.1;dbnameimport_demo;charsetutf8mb4, root, password, [ PDO::ATTR_ERRMODE PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE PDO::FETCH_ASSOC, ] ); $stmt $pdo-prepare(INSERT INTO goods (name, price, stock) VALUES (?, ?, ?)); $batchSize 500; $pdo-beginTransaction(); try { foreach ($rows as $lineNo $row) { if ($lineNo 0) { continue; // 跳过表头 } $stmt-execute([ (string)$row[0], // name (float)$row[1], // price (int)$row[2], // stock ]); if ($lineNo % $batchSize 0) { $pdo-commit(); // 每 500 条提交一次 $pdo-beginTransaction(); // 开启下一个事务块 } } $pdo-commit(); // 最后一批提交 } catch (Throwable $e) { $pdo-rollBack(); // 回滚当前未提交的事务块 error_log($e-getMessage()); throw new RuntimeException(导入中止最近一批数据已回滚, 0, $e); }预处理的好处是双重的占位符自动处理转义SQL 注入这条线直接堵死同时同一句 INSERT 只需要在 MySQL 端解析一次后续 execute 只传参数执行开销小一个量级。每 500 条提交一次是为了不让单个事务太大否则 redo log 和 undo log 会把磁盘 IO 吃满行锁也长时间不释放影响同表其他查询。如果总行数只有几百直接一个事务提交就行不需要分段。分段事务最适合 2000 行到 5 万行之间的常规导入。超过 5 万行可以考虑用 MySQL 的 LOAD DATA LOCAL INFILE但它绕过逐行校验对数据清洗要求更高而且 PHP 侧要开启 PDO::MYSQL_ATTR_LOCAL_INFILE 才能用权限限制比较多日常业务导入我还是优先用上面的预处理方案。3.3 事务越界与回滚后悔药放在哪里MySQL 的事务回滚只能回滚未提交的内容一旦 commit 了这批数据就变成正式数据。所以在导入模块里真正的后悔药要提前备好。我一般会在导入前先把目标表复制一份带日期的备份表CREATE TABLE goods_bak_20240520 AS SELECT * FROM goods;这条语句会把表结构和数据一起复制过去。如果导入后运营反馈价格全错位了多了 8000 条重复数据直接拿备份表把目标表恢复比一条条 DELETE 靠谱得多。特别的如果目标表有自增主键备份表不会保留原自增主键的 AUTO_INCREMENT 属性恢复时要记得单独处理。另一个工程习惯是建一张导入任务日志表记录每次导入的文件名、成功行数、失败行数、失败原因、操作时间。日志的作用不是预防是定位哪一次导入出了问题、哪一批数据需要清理都有据可查而不是拍脑袋猜。我在实际项目中会把日志写入和业务写入放在同一个事务块里保证批次可追溯。4. 字段映射与数据清洗Excel 表头到数据库字段的最后一步4.1 表头映射用一张对照表撑住列名变化拿到 xls 之后第一件事不是写 SQL而是看表头。真实业务里的表头常常是商品名称必填零售价库存数量这类中文描述代码里直接写死 row[0] 对应 name表头一调整整个导入逻辑全崩。更麻烦的是运营经常在表中间插入一列备注列号瞬间错位。解决办法是维护一张对照表让列号和数据库字段解耦。我一般这样组织映射逻辑// 列位置 - 数据库字段 的映射同时标注是否必填 $columnMap [ A [field name, required true], B [field price, required true], C [field stock, required false], D [field brand, required false], ]; // 表头别名方便按名称匹配而不仅仅是列位置 $headerAlias [ name [商品名, 品名, 名称, title], price [零售价, 价格, 单价], stock [库存数量, 库存], brand [品牌, brand], ];操作顺序是先读表头行拿表头文本去比对 headerAlias建出一份 colIndex 到 field 的动态映射然后按这份映射取数。这样即使运营把 C 列和 D 列互换只要表头名称没变导入结果依然正确。我把这份映射定义写在所有导入处理之前后续清洗逻辑只认 field 不认列号改动起来非常省事。值得说明的是必填字段的校验以这份映射为基准而不是以 Excel 内容为基准这样在清洗阶段就能把差距拉齐缺一列的 xls 会直接提示缺少品牌列而不是导入完成之后才发现 brand 字段全是 NULL。4.2 去重与合法性校验让脏数据在入库之前暴露最常见的事故是重复导入。运营第一次导完觉得好像没成功又按了一次上传表里直接翻倍。对付这个要从两个层面下手数据库建唯一索引代码里做导入前查重。给 goods 表加唯一索引的 SQLALTER TABLE goods ADD UNIQUE KEY uk_code (code);然后导入逻辑里用 INSERT IGNORE 或者 ON DUPLICATE KEY UPDATE。两者差别在于IGNORE 碰到重复记录直接跳过保留库里旧数据ON DUPLICATE KEY UPDATE 会把现有记录更新成 Excel 里的最新值。导入会员信息、价格表这类业务我更常用 UPDATE 语义因为 Excel 往往是运营整理后的最终版本。// INSERT IGNORE重复的 code 静默跳过 $stmt $pdo-prepare(INSERT IGNORE INTO goods (code, name, price, stock) VALUES (?, ?, ?, ?)); // 或者冲突时更新价格和库存 $stmt $pdo-prepare( INSERT INTO goods (code, name, price, stock) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE price VALUES(price), stock VALUES(stock) );除了去重还有合法性校验。Excel 里写着1,299这种带千分位逗号的价格也可能有空字符串和肉眼不可见的空格入库前必须把这些转成干净的数据类型。我会把每条失败记录收集到数组里等整批处理完再统一报告而不是遇到一条脏数据就中断整个导入$failed []; foreach ($rows as $lineNo $row) { $name trim((string)($row[0] ?? )); $price str_replace(,, , trim((string)($row[1] ?? ))); if ($name || $price || !is_numeric($price)) { $failed[] [ line $lineNo 1, reason 商品名缺失或价格不是数字, raw $row, ]; continue; } // 通过校验的行进入导入队列 }这样导入结果可以分成两条线合格的进数据库不合格的进失败列表。失败列表我习惯写进日志表或者生成一份 CSV 给运营下载让对账的人知道具体是哪些行出了问题而不是看到一个笼统的导入失败。4.3 编码、空白与特殊符号的处理顺序中文乱码是导入模块的高频问题。处理顺序比处理动作本身更重要先做编码转换再做空白清洗最后才做类型校验。顺序反了会出很多怪问题比如在 GBK 编码的字符串上用正则会匹配错位置。如果你拿到的是 CSV可以先把整个文件读进来做编码探测和转换$content file_get_contents($filePath); $enc mb_detect_encoding($content, [UTF-8, GB18030, BIG-5], true); if ($enc ! UTF-8) { $content mb_convert_encoding($content, UTF-8, $enc); } file_put_contents($filePath, $content);注意这个方案只适用于 CSV 和纯文本。真正的 .xls 二进制格式不能这么整个文件转码因为编码信息嵌在二进制结构里硬转会把文件转坏。对 xls我一般是在读取完成后对读出来的字符串做一次 mb_convert_encoding 兜底$value mb_convert_encoding((string)$row[0], UTF-8, GB18030);不过这个兜底不能无脑跑得配合编码检测一起用否则 UTF-8 文件会被转成乱码。更稳妥的做法是在导入页面上提醒用户优先上传 UTF-8 编码的 xlsx 或 CSV编码相关的幺蛾子能少掉一大半。空白和特殊符号处理也要有顺序。Excel 单元格里经常带 \r\n 换行、零宽空格、不可见控制字符这些写进 MySQL 后显示会非常奇怪// 先统一换行符 $value str_replace([\r\n, \r], \n, $value); // 再把单元格内换行替换成空格避免单条数据里出现换行 $value str_replace(\n, , $value); // 最后去掉控制字符 $value preg_replace(/[\x00-\x08\x0B\x0C\x0E-\x1F]/u, , $value);顺序上要注意先处理换行再做控制字符过滤否则正则里的边界会受 \r 干扰。做完这一步再进 4.2 的类型校验和去重。5. 避坑xls导入Mysql(PHP程序)最常踩的 5 个问题5.1 现象手机号/订单号读出来全是科学计数法导入之后发现手机号变成了 1.3912345678E10或者 123456789012 变成了一串小数。原因是 Excel 用 double 存放数值单元格超过 11 位的整数在 toArray 过程中被转成了浮点数输出时自动变成科学计数法。解决方法是读取后判断浮点数是否为整数值如果是就转成普通字符串$value $cell-getValue(); if (is_float($value) floor($value) $value) { $value number_format($value, 0, , ); // 去掉科学计数法 }number_format 第三个参数空字符串表示不要小数分隔符第四个参数空字符串表示不分组所以 13912345678 能原样输出。这类字段在数据库里应该设计成 varchar 而不是 int避免溢出和显示问题。5.2 现象日期单元格读出来是 44221 这样的数字这是 2.3 节提到的 Excel 日期序列号问题导入后 datetime 字段全是一串整数一看就知道是把底层浮点数直接写进去了。原因和解决都在前面讲过核心是判断单元格数字格式里是否包含 yyyy/m/d 这类日期格式代码然后用 ExcelDate::excelToDateTimeObject 转换成 DateTime再 format 成 Y-m-d H:i:s 入库。5.3 现象中文字符导入 MySQL 后全是问号库里的中文变成 ????但 xls 里看着正常。通常有三个原因按优先级排查PDO DSN 里没有带 charsetutf8mb4连接字符集还是 latin1建表时字段或表本身是 utf8mb3某些生僻字存不进去文件源编码不是 UTF-8读出来的字符串本身已经乱码。第一层解决最直接PDO 连接串加上 charsetutf8mb4 和 MYSQL_ATTR_INIT_COMMAND 设置 SET NAMES utf8mb4$pdo new PDO( mysql:host127.0.0.1;dbnameimport_demo;charsetutf8mb4, root, password, [PDO::MYSQL_ATTR_INIT_COMMAND SET NAMES utf8mb4] );第二层要检查建表语句第三层按 4.3 的编码处理流程来。目前最新版本的 MySQL 8.0 默认字符集已经是 utf8mb4如果你还在维护 5.7 老库建表时把 CHARSETutf8mb4 写完整能少很多事。5.4 现象脚本跑到第 3 分钟超时前端一直转圈PHP 返回 504 或者直接白屏。原因是默认 max_execution_time 是 30 秒导入 2 万行加清洗逻辑很容易超时。解决方式分两种如果是一次性导入把脚本改成 CLI 方式执行摆脱 Web 超时限制php /path/to/import_script.php ./upload/goods.xls如果是后台界面触发的导入就不要把全部逻辑放在一次请求里可以拆成分批任务前端每次请求只处理 1000 行完成一批评返回进度下一批再继续。CLI 方式最简单可控但需要服务器能执行命令行脚本虚拟主机用户还是走分批方案更稳。5.5 现象重复导入后表里数据翻倍同一份 xls 提交两次表里出现两份相同数据。核心原因是没有唯一索引也没有事前查重。解决是给业务唯一键加 UNIQUE 索引配合 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 处理冲突。如果表里已有大量重复数据先清理一遍再加索引否则加索引本身也会因为重复值而失败。6. 导入完成不是结束校验报告与回滚演练导入完成后别急着关页面先做一次数据校验。我的习惯是在导入前复制一张备份表然后对比备份表和目标表的聚合值。以商品表为例导入前后分别跑两句 SQLSELECT COUNT(*), SUM(price), SUM(stock) FROM goods_bak_20240520; SELECT COUNT(*), SUM(price), SUM(stock) FROM goods;两条记录数完全一致价格总和、库存总和对得上这关才算过。如果对不上直接拿备份表覆盖回来重导不要手贱去 DELETE。这个习惯来自一次血泪教训某次我图省事没建备份表把一份含有错误编码的商品表直接覆盖导入价格全部错位半夜才发现最后只能从 binlog 里一点一点捞历史数据费了一整晚。从那以后凡是超过 500 行的数据导入第一句 SQL 永远是 CREATE TABLE ... AS SELECT 备份表。导入脚本我建议从一开始就做成 CLI 和 Web 双入口入口代码只差一小段if (PHP_SAPI cli) { $filePath $argv[1] ?? ; // 命令行直接传文件路径 } else { $filePath $_FILES[excel][tmp_name] ?? ; // Web 上传临时文件 } $import new ExcelImportService($pdo); $result $import-run($filePath);这样同一个导入服务可以被后台管理界面调用也能被计划任务定时跑。再加上失败行日志表运营可以直接下载一份失败清单改完再导比所有错误都堵在弹窗里体面得多。把备份、校验、失败清单这三样东西搭好xls 导入 MySQL 这个功能才算真正闭环。希望这套流程对你有帮助。本文还有配套的精品资源点击获取 xls导入MySQL(PHP程序)：Excel批量数据入库的完整实践
简介面向需要将xls表格数据批量导入MySQL的PHP开发者这套轻量源码包提供了完整可运行的导入方案。压缩包仅13KB包含4个文件由3个PHP脚本和1个inc辅助库组成分别承担Excel文件解析、数据行读取与数据库写入等职责模块划分清晰便于在此基础上按业务需求调整。程序支持自定义数据库名、表名及字段映射导入前需将表格保存为xls格式并确保表头与目标表字段一一对应同时内置中文处理逻辑入库数据统一以UTF-8编码保存可避免中文乱码问题适合后台数据迁移、Excel批量录入或临时数据整理等场景。资源体积小巧不依赖复杂环境解压后即可部署到支持PHP的服务器或本地环境使用。目前已有276人学习使用对想了解PHP操作Excel并写入MySQL的实现思路、希望快速搭建导入工具的开发者具有一定的参考价值。1. xls导入Mysql(PHP程序)为什么 Excel 批量入库值得认真做后台管理网站上最常被低估的需求就是把 xls 批量导入 MySQL。仓库那边会递来一个 8000 行的商品表运营会递来 2 万条会员名单如果你只给一个逐条新增的表单他们就会一条条复制粘贴到天亮。最简单的 xls导入Mysql(PHP程序)方案是这样的用户上传 xls 文件PHP 用 PhpSpreadsheet 把单元格读成数组经过字段映射和去重校验之后用批量 INSERT 加事务写入 MySQL全程不到 300 行代码就能把原来半天的转录变成一次上传。这篇文章适合要做数据导入功能的 PHP 后端同学也适合接手老导入模块想重构的人。2. 读取 xls 的 PHP 方案PhpSpreadsheet 的加载、单元格解析与分批读取2.1 为什么旧的 PHPExcel 代码在 PHP 8 上越来越难用在搜索框里输入xls导入Mysql PHP翻出来的旧教程几乎清一色是 PHPExcel 1.8。这个库在 2015 年之后就没有大版本更新网上大量现成代码都建立在它上面拿来跑 PHP 5.6 没问题到了 PHP 7.4 开始冒出弃用警告PHP 8.0 之后更严重——构造函数风格、魔术方法、对象类型提示全部是老写法很多服务器开着 error_reporting(E_ALL)页面还没输出 JSON顶部先铺满一串黄色警告。PhpSpreadsheet 是 PHPExcel 的官方继任库composer 包名是phpoffice/phpspreadsheet。它保留了读取 .xlsExcel 97-2003 二进制格式的能力同时支持 xlsx、csv 等格式命名空间和 API 整体现代化PHP 8 兼容性比老库好得多。我自己的选型标准是四条能读真 xls 二进制文件而不是只支持 xlsx、单元格类型拿到手能判断和控制、支持按行过滤避免大文件撑爆内存、在 PHP 8 下没有成堆弃用提示。PhpSpreadsheet 四条基本满足。如果你手上只有 CSV 数据其实可以不引入这层库用 fgetcsv 原生解析更轻量但 xls 是二进制格式靠 fgetcsv 是读不了的这也是为什么导入模块里 PhpSpreadsheet 成了标配。2.2 最小可用代码20 行把 xls 变成 PHP 数组先看一段能跑通的最小代码它负责把整个工作表读进 PHP 数组?php require __DIR__ . /vendor/autoload.php; use PhpOffice\PhpSpreadsheet\IOFactory; $filePath __DIR__ . /upload/goods.xls; $reader IOFactory::createReaderForFile($filePath); $reader-setReadDataOnly(true); // 只要计算后的值不读公式和样式 $spreadsheet $reader-load($filePath); $sheet $spreadsheet-getActiveSheet(); // 返回二维数组$rows[0] 对应 Excel 第 1 行$rows[0][0] 对应 A 列 $rows $sheet-toArray(null, true, false); foreach ($rows as $lineNo $row) { if ($lineNo 0) { continue; // 跳过表头行 } // $row[0] A列 $row[1] B列 $row[2] C列 ... 这里取单元格内容 if (array_filter($row, fn($v) $v ! null $v ! ) []) { continue; // 跳过整行空白 } // 输出到 MySQL 的逻辑在下一章处理 }IOFactory::createReaderForFile 会自动按扩展名判断用 Xls 还是 Xlsx 读取器所以同一个入口能同时兼容 .xls 和 .xlsx。setReadDataOnly(true) 很关键它会忽略单元格样式和公式定义只把值读出来速度和内存表现都好很多。toArray 的三个参数分别表示空单元格用什么值占位、是否计算公式、是否格式化单元格数据。第三个参数传 false是为了拿到单元格原始值——日期、数字这类内容后面还要自己做转换提前格式化成字符串反而不好判断类型。toArray 对 5000 行以内的小文件非常合适代码短、好调试把表头行跳过去之后直接进业务处理。超过这个量级建议看 2.4 的分批读取方案。2.3 单元格类型暗坑日期、百分比、公式值怎么读才对xls 里的日期本质上不是字符串而是一个浮点数。Excel 把 1900 年 1 月 1 日记为 1之后每过一天数值加 1所以 2024 年 5 月 1 日在底层可能就是 45517 之类的数字。如果你直接把这个值写进 MySQL 的 datetime 字段库里就会出现一堆 45517。百分比同理单元格里看到的是 12.4%实际值却是 0.124。用 PhpSpreadsheet 判断和转换日期单元格常见做法是先看数据类型再用内置的 Date 类转换use PhpOffice\PhpSpreadsheet\Cell\DataType; use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate; $cell $sheet-getCell(C2); $value $cell-getValue(); if ($cell-getDataType() DataType::TYPE_NUMERIC ExcelDate::isDateTime($cell)) { $dateObj ExcelDate::excelToDateTimeObject((float)$value); $dateStr $dateObj-format(Y-m-d H:i:s); // 统一转成 MySQL 日期格式 } elseif (is_float($value) $value 0 $value 1) { $percent round($value * 100, 2) . %; // 0.124 - 12.4% }isDateTime 判断的是单元格数字格式比如单元格格式是 yyyy-m-d它就会返回 true这时候用 excelToDateTimeObject 转成 DateTime 对象再格式化成 MySQL 需要的字符串这一条在导入业务表时几乎是必踩的。还有一个容易忽略的点setReadDataOnly(true) 之后公式单元格读出来的是文件中缓存的计算结果。如果这个 xls 是 WPS 或者老版本 Excel 生成的缓存值不一定是可信的甚至有可能是空。遇到公式列要么在 Excel 侧先复制成值要么在读取时不要开 setReadDataOnly改用 getCalculatedValue() 让 PHP 实时计算一次。2.4 大 Excel 文件的内存控制分片读取与行迭代器toArray 会把整个工作表一次性装进 PHP 内存。我处理过一个 50MB 左右的 xls直接 toArray 的内存峰值超过 300MB虚拟主机环境基本直接 500 错误。这种体量必须改成按行读取。第一种做法是用 ReadFilter 过滤行让加载过程只读前 N 行$reader-setReadDataOnly(true); $reader-setReadEmptyCells(false); $reader-setReadFilter(new class implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter { public function readCell($columnAddress, $row, $worksheetName ): bool { return $row 3000; // 只放过前 3000 行 } }); $spreadsheet $reader-load($filePath);通过匿名类实现 IReadFilter在 readCell 里判断行号是否在范围内行号超出范围直接返回 false内存占用会稳定在低位。读下一批时把上限改成 6000、9000 再重新 load 一次。注意这个过滤器是按行放的不会影响行号本身所以读出来的行号仍然对应 Excel 原始行号。第二种做法是用行迭代器逐行取单元格$worksheet $spreadsheet-getActiveSheet(); foreach ($worksheet-getRowIterator() as $row) { $cellIterator $row-getCellIterator(); $cellIterator-setIterateOnlyExistingCells(true); // 跳过空单元格省内存 foreach ($cellIterator as $cell) { $value $cell-getValue(); // 拿到一个单元格值马上处理不用把整表囤在内存里 } }行迭代器的好处是内存占用恒定特别适合 3 万行以上的大表坏处是代码结构比 toArray 绕每一行的列值需要循环取业务逻辑会嵌进迭代器里。如果你只是做一次性导入脚本可以先让用户把 xls 另存为 xlsx 再上传解析速度会快不少这是一个低成本优化。读取方式内存表现适合场景toArray整表进内存小文件最方便5000 行以内表结构固定ReadFilter 分片每次只留一批稳定几万行分批入库RowIterator内存恒定代码稍复杂超大文件边读边处理3. 写入 MySQL批量 INSERT、事务与性能取舍3.1 为什么逐条 INSERT 到 2 万行就开始卡顿最直观的错误写法是 foreach 循环里每行执行一次 INSERT我自己早年也干过这种事2 万行的商品表导了 40 多秒用户以为页面卡死直接关掉重试结果又重复导入了一次。逐条插入慢在三个地方。第一每条 INSERT 都是独立的 SQL 请求MySQL 要重新做语法解析、权限检查、生成执行计划第二PHP 和 MySQL 之间每执行一次都要走一轮完整网络往返2 万条就是 2 万次往返第三在默认 autocommit 模式下每条 INSERT 都是独立事务InnoDB 要为每条事务准备 redo log这个开销远比想象中大。如果把 2 万条放进一个事务再一次性提交耗时能降到原来的五分之一甚至更低。这里还要提醒一点用字符串拼接 SQL 的方式本身就危险Excel 里的值可能带单引号、反斜杠、换行稍不注意就会拼出语法错误或者注入点。避免这个问题的办法就是预处理占位符这在批量导入里同时也是性能优化因为同一句 INSERT 可以反复执行 N 次MySQL 只需要解析一次。3.2 批量写入的正确姿势预处理 分段事务以商品表为例假设 goods 表有 name、price、stock 三个字段Excel 的 A、B、C 列分别对应这三列。推荐写成这样use PDO; $pdo new PDO( mysql:host127.0.0.1;dbnameimport_demo;charsetutf8mb4, root, password, [ PDO::ATTR_ERRMODE PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE PDO::FETCH_ASSOC, ] ); $stmt $pdo-prepare(INSERT INTO goods (name, price, stock) VALUES (?, ?, ?)); $batchSize 500; $pdo-beginTransaction(); try { foreach ($rows as $lineNo $row) { if ($lineNo 0) { continue; // 跳过表头 } $stmt-execute([ (string)$row[0], // name (float)$row[1], // price (int)$row[2], // stock ]); if ($lineNo % $batchSize 0) { $pdo-commit(); // 每 500 条提交一次 $pdo-beginTransaction(); // 开启下一个事务块 } } $pdo-commit(); // 最后一批提交 } catch (Throwable $e) { $pdo-rollBack(); // 回滚当前未提交的事务块 error_log($e-getMessage()); throw new RuntimeException(导入中止最近一批数据已回滚, 0, $e); }预处理的好处是双重的占位符自动处理转义SQL 注入这条线直接堵死同时同一句 INSERT 只需要在 MySQL 端解析一次后续 execute 只传参数执行开销小一个量级。每 500 条提交一次是为了不让单个事务太大否则 redo log 和 undo log 会把磁盘 IO 吃满行锁也长时间不释放影响同表其他查询。如果总行数只有几百直接一个事务提交就行不需要分段。分段事务最适合 2000 行到 5 万行之间的常规导入。超过 5 万行可以考虑用 MySQL 的 LOAD DATA LOCAL INFILE但它绕过逐行校验对数据清洗要求更高而且 PHP 侧要开启 PDO::MYSQL_ATTR_LOCAL_INFILE 才能用权限限制比较多日常业务导入我还是优先用上面的预处理方案。3.3 事务越界与回滚后悔药放在哪里MySQL 的事务回滚只能回滚未提交的内容一旦 commit 了这批数据就变成正式数据。所以在导入模块里真正的后悔药要提前备好。我一般会在导入前先把目标表复制一份带日期的备份表CREATE TABLE goods_bak_20240520 AS SELECT * FROM goods;这条语句会把表结构和数据一起复制过去。如果导入后运营反馈价格全错位了多了 8000 条重复数据直接拿备份表把目标表恢复比一条条 DELETE 靠谱得多。特别的如果目标表有自增主键备份表不会保留原自增主键的 AUTO_INCREMENT 属性恢复时要记得单独处理。另一个工程习惯是建一张导入任务日志表记录每次导入的文件名、成功行数、失败行数、失败原因、操作时间。日志的作用不是预防是定位哪一次导入出了问题、哪一批数据需要清理都有据可查而不是拍脑袋猜。我在实际项目中会把日志写入和业务写入放在同一个事务块里保证批次可追溯。4. 字段映射与数据清洗Excel 表头到数据库字段的最后一步4.1 表头映射用一张对照表撑住列名变化拿到 xls 之后第一件事不是写 SQL而是看表头。真实业务里的表头常常是商品名称必填零售价库存数量这类中文描述代码里直接写死 row[0] 对应 name表头一调整整个导入逻辑全崩。更麻烦的是运营经常在表中间插入一列备注列号瞬间错位。解决办法是维护一张对照表让列号和数据库字段解耦。我一般这样组织映射逻辑// 列位置 - 数据库字段 的映射同时标注是否必填 $columnMap [ A [field name, required true], B [field price, required true], C [field stock, required false], D [field brand, required false], ]; // 表头别名方便按名称匹配而不仅仅是列位置 $headerAlias [ name [商品名, 品名, 名称, title], price [零售价, 价格, 单价], stock [库存数量, 库存], brand [品牌, brand], ];操作顺序是先读表头行拿表头文本去比对 headerAlias建出一份 colIndex 到 field 的动态映射然后按这份映射取数。这样即使运营把 C 列和 D 列互换只要表头名称没变导入结果依然正确。我把这份映射定义写在所有导入处理之前后续清洗逻辑只认 field 不认列号改动起来非常省事。值得说明的是必填字段的校验以这份映射为基准而不是以 Excel 内容为基准这样在清洗阶段就能把差距拉齐缺一列的 xls 会直接提示缺少品牌列而不是导入完成之后才发现 brand 字段全是 NULL。4.2 去重与合法性校验让脏数据在入库之前暴露最常见的事故是重复导入。运营第一次导完觉得好像没成功又按了一次上传表里直接翻倍。对付这个要从两个层面下手数据库建唯一索引代码里做导入前查重。给 goods 表加唯一索引的 SQLALTER TABLE goods ADD UNIQUE KEY uk_code (code);然后导入逻辑里用 INSERT IGNORE 或者 ON DUPLICATE KEY UPDATE。两者差别在于IGNORE 碰到重复记录直接跳过保留库里旧数据ON DUPLICATE KEY UPDATE 会把现有记录更新成 Excel 里的最新值。导入会员信息、价格表这类业务我更常用 UPDATE 语义因为 Excel 往往是运营整理后的最终版本。// INSERT IGNORE重复的 code 静默跳过 $stmt $pdo-prepare(INSERT IGNORE INTO goods (code, name, price, stock) VALUES (?, ?, ?, ?)); // 或者冲突时更新价格和库存 $stmt $pdo-prepare( INSERT INTO goods (code, name, price, stock) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE price VALUES(price), stock VALUES(stock) );除了去重还有合法性校验。Excel 里写着1,299这种带千分位逗号的价格也可能有空字符串和肉眼不可见的空格入库前必须把这些转成干净的数据类型。我会把每条失败记录收集到数组里等整批处理完再统一报告而不是遇到一条脏数据就中断整个导入$failed []; foreach ($rows as $lineNo $row) { $name trim((string)($row[0] ?? )); $price str_replace(,, , trim((string)($row[1] ?? ))); if ($name || $price || !is_numeric($price)) { $failed[] [ line $lineNo 1, reason 商品名缺失或价格不是数字, raw $row, ]; continue; } // 通过校验的行进入导入队列 }这样导入结果可以分成两条线合格的进数据库不合格的进失败列表。失败列表我习惯写进日志表或者生成一份 CSV 给运营下载让对账的人知道具体是哪些行出了问题而不是看到一个笼统的导入失败。4.3 编码、空白与特殊符号的处理顺序中文乱码是导入模块的高频问题。处理顺序比处理动作本身更重要先做编码转换再做空白清洗最后才做类型校验。顺序反了会出很多怪问题比如在 GBK 编码的字符串上用正则会匹配错位置。如果你拿到的是 CSV可以先把整个文件读进来做编码探测和转换$content file_get_contents($filePath); $enc mb_detect_encoding($content, [UTF-8, GB18030, BIG-5], true); if ($enc ! UTF-8) { $content mb_convert_encoding($content, UTF-8, $enc); } file_put_contents($filePath, $content);注意这个方案只适用于 CSV 和纯文本。真正的 .xls 二进制格式不能这么整个文件转码因为编码信息嵌在二进制结构里硬转会把文件转坏。对 xls我一般是在读取完成后对读出来的字符串做一次 mb_convert_encoding 兜底$value mb_convert_encoding((string)$row[0], UTF-8, GB18030);不过这个兜底不能无脑跑得配合编码检测一起用否则 UTF-8 文件会被转成乱码。更稳妥的做法是在导入页面上提醒用户优先上传 UTF-8 编码的 xlsx 或 CSV编码相关的幺蛾子能少掉一大半。空白和特殊符号处理也要有顺序。Excel 单元格里经常带 \r\n 换行、零宽空格、不可见控制字符这些写进 MySQL 后显示会非常奇怪// 先统一换行符 $value str_replace([\r\n, \r], \n, $value); // 再把单元格内换行替换成空格避免单条数据里出现换行 $value str_replace(\n, , $value); // 最后去掉控制字符 $value preg_replace(/[\x00-\x08\x0B\x0C\x0E-\x1F]/u, , $value);顺序上要注意先处理换行再做控制字符过滤否则正则里的边界会受 \r 干扰。做完这一步再进 4.2 的类型校验和去重。5. 避坑xls导入Mysql(PHP程序)最常踩的 5 个问题5.1 现象手机号/订单号读出来全是科学计数法导入之后发现手机号变成了 1.3912345678E10或者 123456789012 变成了一串小数。原因是 Excel 用 double 存放数值单元格超过 11 位的整数在 toArray 过程中被转成了浮点数输出时自动变成科学计数法。解决方法是读取后判断浮点数是否为整数值如果是就转成普通字符串$value $cell-getValue(); if (is_float($value) floor($value) $value) { $value number_format($value, 0, , ); // 去掉科学计数法 }number_format 第三个参数空字符串表示不要小数分隔符第四个参数空字符串表示不分组所以 13912345678 能原样输出。这类字段在数据库里应该设计成 varchar 而不是 int避免溢出和显示问题。5.2 现象日期单元格读出来是 44221 这样的数字这是 2.3 节提到的 Excel 日期序列号问题导入后 datetime 字段全是一串整数一看就知道是把底层浮点数直接写进去了。原因和解决都在前面讲过核心是判断单元格数字格式里是否包含 yyyy/m/d 这类日期格式代码然后用 ExcelDate::excelToDateTimeObject 转换成 DateTime再 format 成 Y-m-d H:i:s 入库。5.3 现象中文字符导入 MySQL 后全是问号库里的中文变成 ????但 xls 里看着正常。通常有三个原因按优先级排查PDO DSN 里没有带 charsetutf8mb4连接字符集还是 latin1建表时字段或表本身是 utf8mb3某些生僻字存不进去文件源编码不是 UTF-8读出来的字符串本身已经乱码。第一层解决最直接PDO 连接串加上 charsetutf8mb4 和 MYSQL_ATTR_INIT_COMMAND 设置 SET NAMES utf8mb4$pdo new PDO( mysql:host127.0.0.1;dbnameimport_demo;charsetutf8mb4, root, password, [PDO::MYSQL_ATTR_INIT_COMMAND SET NAMES utf8mb4] );第二层要检查建表语句第三层按 4.3 的编码处理流程来。目前最新版本的 MySQL 8.0 默认字符集已经是 utf8mb4如果你还在维护 5.7 老库建表时把 CHARSETutf8mb4 写完整能少很多事。5.4 现象脚本跑到第 3 分钟超时前端一直转圈PHP 返回 504 或者直接白屏。原因是默认 max_execution_time 是 30 秒导入 2 万行加清洗逻辑很容易超时。解决方式分两种如果是一次性导入把脚本改成 CLI 方式执行摆脱 Web 超时限制php /path/to/import_script.php ./upload/goods.xls如果是后台界面触发的导入就不要把全部逻辑放在一次请求里可以拆成分批任务前端每次请求只处理 1000 行完成一批评返回进度下一批再继续。CLI 方式最简单可控但需要服务器能执行命令行脚本虚拟主机用户还是走分批方案更稳。5.5 现象重复导入后表里数据翻倍同一份 xls 提交两次表里出现两份相同数据。核心原因是没有唯一索引也没有事前查重。解决是给业务唯一键加 UNIQUE 索引配合 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 处理冲突。如果表里已有大量重复数据先清理一遍再加索引否则加索引本身也会因为重复值而失败。6. 导入完成不是结束校验报告与回滚演练导入完成后别急着关页面先做一次数据校验。我的习惯是在导入前复制一张备份表然后对比备份表和目标表的聚合值。以商品表为例导入前后分别跑两句 SQLSELECT COUNT(*), SUM(price), SUM(stock) FROM goods_bak_20240520; SELECT COUNT(*), SUM(price), SUM(stock) FROM goods;两条记录数完全一致价格总和、库存总和对得上这关才算过。如果对不上直接拿备份表覆盖回来重导不要手贱去 DELETE。这个习惯来自一次血泪教训某次我图省事没建备份表把一份含有错误编码的商品表直接覆盖导入价格全部错位半夜才发现最后只能从 binlog 里一点一点捞历史数据费了一整晚。从那以后凡是超过 500 行的数据导入第一句 SQL 永远是 CREATE TABLE ... AS SELECT 备份表。导入脚本我建议从一开始就做成 CLI 和 Web 双入口入口代码只差一小段if (PHP_SAPI cli) { $filePath $argv[1] ?? ; // 命令行直接传文件路径 } else { $filePath $_FILES[excel][tmp_name] ?? ; // Web 上传临时文件 } $import new ExcelImportService($pdo); $result $import-run($filePath);这样同一个导入服务可以被后台管理界面调用也能被计划任务定时跑。再加上失败行日志表运营可以直接下载一份失败清单改完再导比所有错误都堵在弹窗里体面得多。把备份、校验、失败清单这三样东西搭好xls 导入 MySQL 这个功能才算真正闭环。希望这套流程对你有帮助。本文还有配套的精品资源点击获取