时间戳

首页 > 解决方案 > 使用 laravel 批量插入和更新数据的最佳方法是什么

php - 使用 laravel 批量插入和更新数据的最佳方法是什么

问题描述

我有一个 laravel 命令，它从 SQL Server 数据库中搜索数据并将每一行（如果已经存在，则更新它）插入到 MySQL 数据库中。

这是当前工作的代码：

首先，从 SQL Server 中搜索行：

 $etiquetas = DB::connection('delisur_picking')
            ->table('deli_eti_correl')
            ->where('correl', '>', $lastLabel)
            ->orderBy('correl', 'asc')
            ->get()
            ->chunk(100);

然后插入和/或更新：

$i = 0;
$numins=0;
$numupd=0;
$tot=0;
$locations=DB::connection("mysql")->table("locations")->select("id", "code")->get();
$alocations=array();
foreach($locations as $l){
    $alocations[$l->id]=$l->code;
}
foreach ($etiquetas as $chunk) {
    $i = $i + 1;
    Log::info('Cargando '.$i.' chunks de '.count($etiquetas));
    $arrlabelI=array();
    $arrlabelU=array();
    foreach ($chunk as $etiqueta) {
        $tot++;
        $label=array();
        $labelExists=DB::connection("mysql")->table("labels")->where("correlative", $etiqueta->correl)->exists();
        $locationid=array_search($etiqueta->origen, $alocations);
        $label["cod_prod"]=$etiqueta->cod_prod;
        $label["nom_prod1"]=trim($etiqueta->nom_prod1);
        $label["nom_prod2"]=trim($etiqueta->nom_prod2);
        $label["q_caja"]=$etiqueta->q_caja;
        $label["fec_elab"]= $etiqueta->fec_elab;
        $label["lote"]= $etiqueta->lote;
        $label["fec_sys"]= $etiqueta->fec_sys;
        $label["actual_location"]= trim($etiqueta->origen) == '' ? null : ($locationid!==false ? $locationid : null);
        if($labelExists){
            $arrlabelU[$etiqueta->correl]=$label;
            Log::info("Correlativo marcado para actualizacion: $etiqueta->correl");                
        }else{
            $label["correlative"]=$etiqueta->correl;
            $arrlabelI[]=$label;
            Log::info("Correlativo marcado para insercion: $etiqueta->correl");
        }
    }

    DB::connection('mysql')->table("labels_alt")->insert($arrlabelI);
    $numins+=count($arrlabelI);
    foreach($arrlabelU as $idx => $row){
        DB::connection('mysql')->table("labels")->where("correlative", $idx)->update($row);
        $numupd++;
    }
}

标签表定义如下：

CREATE TABLE `labels` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `correlative` BIGINT(20) NULL DEFAULT NULL,
    `cod_prod` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `nom_prod1` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `nom_prod2` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `q_caja` BIGINT(20) NULL DEFAULT NULL,
    `fec_elab` DATETIME NULL DEFAULT NULL,
    `lote` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `fec_sys` DATETIME NULL DEFAULT NULL,
    `actual_location` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
)

对于每个块，填充数组需要 1/2 秒，$alabelsI并且$alabelsU（给定每个块的行数，大约为 50 秒）半秒将数据插入$alabelsI

问题：

  1. 有没有更快的方法来填充数组？
  2. 由于客户的限制，我们无法修改 MySQL 数据库中现有的表。因此，我无法将相关列定义为唯一键，这是 MySQL 中使用 Laravelupsert函数所必需的。如果我不能使用upsert，有没有比上面的代码更优化的方法来插入或更新数据？

提前致谢

标签： phpmysqllaraveleloquent

解决方案

解决了

我们发现做我们需要的最快的形式是：

  1. 创建一个作为标签副本的新表（名为 labels_alt）
  2. 将上面的 foreach 循环替换为以下循环。此代码在新表中插入所有行
foreach ($etiquetas as $chunk) {
    $i = $i + 1;
    Log::info('Cargando '.$i.' chunks de '.count($etiquetas));
    $arrlabelI=array();
    foreach ($chunk as $etiqueta) {
        $tot++;
        $label=array();
        $locationid=array_search($etiqueta->origen, $alocations);
        $label["cod_prod"]=iconv('Windows-1252', 'UTF-8//TRANSLIT', $etiqueta->cod_prod);
        $label["nom_prod1"]=iconv('Windows-1252', 'UTF-8//TRANSLIT', trim(iconv('Windows-1252', 'UTF-8//TRANSLIT', "$etiqueta->nom_prod1")));
        $label["nom_prod2"]=iconv('Windows-1252', 'UTF-8//TRANSLIT', trim(iconv('Windows-1252', 'UTF-8//TRANSLIT', "$etiqueta->nom_prod2")));
        $label["q_caja"]=iconv('Windows-1252', 'UTF-8//TRANSLIT', $etiqueta->q_caja);
        $label["fec_elab"]= iconv('Windows-1252', 'UTF-8//TRANSLIT', $etiqueta->fec_elab);
        $label["lote"]= iconv('Windows-1252', 'UTF-8//TRANSLIT', $etiqueta->lote);
        $label["fec_sys"]= iconv('Windows-1252', 'UTF-8//TRANSLIT', $etiqueta->fec_sys);
        $label["actual_location"]= trim($etiqueta->origen) == '' ? null : ($locationid!==false ? $locationid : null);
        $label["correlative"]= iconv('Windows-1252', 'UTF-8//TRANSLIT', $etiqueta->correl);
        $arrlabelI[]=$label;
    }

    DB::connection('mysql')->table("labels_alt")->insert($arrlabelI);
    $numins+=count($arrlabelI);
}
$pstmt=DB::connection("mysql")->getPDO()->prepare("call insertLabels()");
$pstmt->execute();
  1. 在数据库中创建一个存储过程，在标签中进行插入和更新：
CREATE DEFINER=`root`@`localhost` PROCEDURE `insertLabels`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE icorrelative, qcaja BIGINT(20);
    DECLARE codprod, nomprod1, nomprod2, ilote VARCHAR(191);
    DECLARE fecelab, fecsys DATETIME; 
    DECLARE createdat, updatedat TIMESTAMP;
    DECLARE done INT DEFAULT FALSE;
    DECLARE ext_cursor CURSOR FOR 
    SELECT labels_alt.correlative, labels_alt.q_caja, labels_alt.cod_prod, labels_alt.nom_prod1, labels_alt.nom_prod2, labels_alt.lote, labels_alt.fec_elab, labels_alt.fec_sys, labels_alt.created_at 
    FROM labels_alt INNER JOIN labels ON labels_alt.correlative=labels.correlative;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    INSERT INTO labels (correlative, q_caja, cod_prod, nom_prod1, nom_prod2, lote, fec_elab, fec_sys, created_at, updated_at) SELECT correlative, q_caja, cod_prod, nom_prod1, nom_prod2, lote, fec_elab, fec_sys, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP FROM labels_alt a NATURAL LEFT JOIN labels b WHERE b.correlative IS NULL;
    OPEN ext_cursor;
    read_loop: LOOP
        FETCH ext_cursor INTO icorrelative, qcaja, codprod, nomprod1, nomprod2, ilote, fecelab, fecsys, createdat; 
        IF done THEN
            LEAVE read_loop;
        END IF;
        UPDATE labels SET q_caja=qcaja, cod_prod=codprod, nom_prod1=nomprod1, lote=ilote, fec_elab=fecelab, fec_sys=fecsys, created_at=createdat, updated_at=CURRENT_TIMESTAMP WHERE correlative=icorrelative;
    END LOOP;
    CLOSE ext_cursor;
    TRUNCATE labels_alt;
END

简而言之，就是通过将 Laravel 中的 SQL 语句移动到存储过程中来尽可能地减少 SQL 语句的执行。

推荐阅读