你的方案很好，可是我现在改造起来时间来不及，请在我的代码的基础上去优化吧！
/**
* @notes 正向配送费货品明细导入(优化版本)
* @return Json
* @author 胡军
* @date 2025/06/20
*/
public function importGoodsDetail(): Json
{
set_time_limit(0);
ini_set('memory_limit', '2048M');
$params = $this->request->param();
if (empty($params["file"])) {
return $this->fail('请上传要导入的文件',[],0,1);
}
$titleArr = [
"物流履约单号" => "logistics_order_no",
"货品名称" => "product_name",
"货品ID" => "product_id",
"货品件数" => "quantity",
"货品重量（克）" => "weight",
"货品长度（毫米）" => "length",
"货品宽度（毫米）" => "width",
"货品高度（毫米）" => "height",
"货品体积（立方毫米）" => "volume",
];
try {
$dataGenerator = $this->importExeclGenerator($params['file']);
$batchSize = 50; // 增加批量处理大小，根据服务器性能调整
$model = new GoodsDetailModel();
//$model->startTrans(); // 使用事务提高性能
$total = 0;
$success = 0;
$failedBatches = [];
$batchData = [];
foreach ($dataGenerator as $rowData) {
$total++;
$item = [];
foreach ($rowData as $k => $val) {
if ($k && $val && isset($titleArr[$k])) {
$item[$titleArr[$k]] = trim($val);
}
}
if (!empty($item)) {
$batchData[] = $item;
}
if (count($batchData) >= $batchSize) {
$this->processBatch($model, $batchData, $success, $failedBatches, $total);
$batchData = [];
}
// 每处理10,000条记录释放一次内存
if ($total % 10000 === 0) {
gc_collect_cycles();
}
}
// 处理剩余数据
if (!empty($batchData)) {
$this->processBatch($model, $batchData, $success, $failedBatches, $total);
}
if (empty($failedBatches)) {
//$model->commit();
$resultMsg = "导入成功，共{$total}条数据，成功导入{$success}条";
return $this->success('导入成功',['remark' => $resultMsg],1,1);
} else {
//$model->rollback();
$errorCount = $total - $success;
$errorMsg = "失败 {$errorCount} 条，第一个错误: {$failedBatches[0]['error']}";
return $this->fail('导入失败',['remark' => $errorMsg],0,1);
}
} catch (\Exception $e) {
return $this->fail('导入失败',['remark' => $e->getMessage()],0,1);
}
}
/**
* 处理数据批次
*/
private function processBatch($model, $batchData, &$success, &$failedBatches, $total)
{
try {
// 使用原生SQL执行批量插入，提高性能
$sql = $this->buildInsertSql($model, $batchData);
Db::execute($sql);
$success += count($batchData);
} catch (\Exception $e) {
$failedBatches[] = [
'startRow' => $total - count($batchData) + 1,
'endRow' => $total,
'error' => $e->getMessage()
];
}
}
/**
* 构建批量插入SQL语句
*/
private function buildInsertSql($model, $batchData)
{
if (empty($batchData)) {
return '';
}
$tableName = $model->getTable();
$fields = array_keys($batchData[0]);
$fieldList = '`' . implode('`, `', $fields) . '`';
$values = [];
foreach ($batchData as $row) {
$rowValues = [];
foreach ($row as $value) {
if ($value === null) {
$rowValues[] = 'NULL';
} elseif (is_numeric($value)) {
$rowValues[] = $value;
} else {
$rowValues[] = "'" . addslashes($value) . "'";
}
}
$values[] = '(' . implode(', ', $rowValues) . ')';
}
return "INSERT INTO {$tableName} ({$fieldList}) VALUES " . implode(', ', $values);
}
/**
* @notes Excel导入方法（生成器模式，逐行返回数据）
* @notes common.php代码不规范导致本地git有差异直接还原 直接写在控制器里算了!
* @author 胡军
* @date 2025/06/20
*/
private function importExeclGenerator(string $file_name): \Generator
{
try {
$path = app()->getRootPath() . "public/" . $file_name;
$reader = IOFactory::createReaderForFile($path);
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($path);
// 获取第一个工作表
$sheet = $spreadsheet->getSheet(0);
// 获取表头
$title = $sheet->rangeToArray('A1:' . $sheet->getHighestColumn() . '1', null, true, true, true)[1];
if (empty($title)) {
throw new \Exception('Excel表头为空');
}
$highestRow = $sheet->getHighestRow();
// 逐行生成数据，不一次性加载到内存
for ($row = 2; $row <= $highestRow; $row++) {
$rowData = $sheet->rangeToArray(
'A' . $row . ':' . $sheet->getHighestColumn() . $row,
null,
true,
true,
true
)[$row];
$item = [];
foreach ($rowData as $colIndex => $value) {
$colName = $title[$colIndex];
if ($colName) {
//$item[$colName] = trim($value);
$item[$colName] = $value;
}
}
// 使用yield关键字逐行返回数据
yield $item;
}
} catch (\Exception $e) {
throw $e;
}
}
看有什么好的方式可以在此基础上优化，不需要使用队列，因为我时间来不及改造了！
最新发布
当数据库中存储的多条数据包含换行符，直接使用可能导致JS错误。本文介绍了一种通过添加input隐藏域的方法来规避此问题，确保数据正确读取。
扫一扫
1673
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
