#php #mysql #laravel #eloquent
Вопрос:
У меня есть команда 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
Вопросы:
- Есть ли более быстрый способ заполнения массивов?
- Из — за ограничений клиента мы не можем изменять существующие таблицы в базе данных MySQL. Как следствие, я не могу определить корреляционный столбец как уникальный ключ, который требуется в MySQL для использования функции Laravel
upsert
. Если я не могу использоватьupsert
, есть ли более оптимальный способ, чем приведенный выше код, для вставки или обновления данных?
Заранее спасибо
Комментарии:
1. Пожалуйста, предоставьте сгенерированный SQL для
INSERT
ввода или вывода.2. «мы не можем изменить существующие таблицы в базе данных MySQL», Если это означает, что вы даже не можете добавить
INDEX
, то вам следует поискать другую работу.3. Мы не можем, потому что клиент этого не позволяет. Вот почему я написал «из-за ограничений клиента». Мы знаем, как это сделать. Кроме того, сгенерированный SQL слишком длинный, потому что представляет собой вставку до 100 строк, и для этого мы используем красноречивый.
4. Такая «пакетная вставка» намного быстрее, чем 100 однорядных вставок. (Я не говорю на языке Laravel, поэтому не могу обратиться к этой стороне вопроса.)
5.
insert
Функция в Laravel позволяет вставлять одну или несколько строк в одну таблицу. Это позволяет, в моем случае, вставлять до 100 строк одним вызовом вместо того, чтобы делать это с одним вызовом на строку.
Ответ №1:
решенный
Самая быстрая форма, которую мы нашли, чтобы сделать то, что нам нужно, — это:
- Создайте новую таблицу (с именем labels_alt), которая является копией меток
- Замените цикл 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_a<
END
В двух словах, заключается в том, чтобы максимально сократить выполнение инструкций SQL от Laravel, переместив указанные инструкции в хранимую процедуру.