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

Question

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

这是当前工作的代码：

首先，从SQL服务器搜索行：

 $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
)

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

问题：

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

提前谢谢

Answer 1

解决了的

我们找到的完成所需任务的最快形式是：

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();

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语句的执行。