Загрузка файла CSV для обработки обновления статуса и вставки новых записей

Как работает LOAD DATA INFILE

На основе индекса UNIQUE LOAD DATA INFILE вставляет новую запись или обновляет существующую (только если активна опция REPLACE).

(1) Что касается вставки :

Если входное значение csv для столбца индекса UNIQUE НЕ найдено в таблице db, добавляется новая запись с (определенными) входными значениями из csv-файла.

(2) Что касается обновления :

Если входное значение csv для столбца индекса UNIQUE находится в таблице db, тогда запрос LOAD DATA INIFILE выполняет следующие операции (в этом порядке!):

• Он вставляет новые значения csv в качестве новой записи с новым идентификатором PRIMARY KEY;
• Он удаляет старую запись из db.

NB: В оставшейся части моего ответа я буду говорить только о части обновления (2).

ДО INSERT-TRIGGER как решение для условных обновлений

Поскольку LOAD DATA INFILE запускает операцию вставки перед удалением, вы можете использовать тот факт, что старая запись db все еще существует, когда вставлена ​​новая запись со значениями csv . Таким образом, вы можете настроить новые входные значения на основе значений, содержащихся в старой записи. Действительно крутая часть этого: вы можете даже сохранить старую ценность поля PRIMARY KEY .

Ключ состоит в том, чтобы определить ПЕРЕД НАСТРОИМ-ТРИГГЕР, в котором находятся все необходимые настройки, проверки и назначения:

• Извлеките значения старой записи, запустив инструкцию SELECT sql;
• Сохранять выбранные значения в ранее определенные пользовательские переменные;
• Используйте переменные пользователя для сравнения старых значений с входными значениями csv;
• Исходя из этих сравнений: присвойте старое значение поля PRIMARY KEY новым и измените новые значения csv на старые или на другие, если это необходимо.

Затем выполните запрос LOAD DATA INFILE из PHP.

Коды

Создать синтаксис таблицы:

 CREATE TABLE `tracking` ( `tracking_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `odanumber` int(11) DEFAULT NULL, `airwaybill` int(11) DEFAULT NULL, `courierful` varchar(100) DEFAULT NULL, `delstatus` tinyint(1) DEFAULT NULL, `deliverydate` varchar(19) DEFAULT NULL, PRIMARY KEY (`tracking_id`), UNIQUE KEY `uni_airwaybill` (`airwaybill`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8; 

ДО ВСТАВКИ-ТРИГГЕРА:

 USE `tests`; DELIMITER $$ DROP TRIGGER IF EXISTS tests.tracking_BEFORE_INSERT$$ USE `tests`$$ CREATE DEFINER = CURRENT_USER TRIGGER `tests`.`tracking_BEFORE_INSERT` BEFORE INSERT ON `tracking` FOR EACH ROW BEGIN /* Define vars to store old record values. */ SET @old_tracking_id = NULL; SET @old_odanumber = NULL; SET @old_courierful = NULL; SET @old_delstatus = NULL; SET @old_deliverydate = NULL; /* Fetch the existing record if exists and pass its values into the correspnding vars. */ SELECT tracking_id, odanumber, courierful, delstatus, deliverydate INTO @old_tracking_id, @old_odanumber, @old_courierful, @old_delstatus, @old_deliverydate FROM tracking WHERE airwaybill = NEW.airwaybill LIMIT 1; /* If an old record was found... */ IF @old_tracking_id IS NOT NULL THEN /* ...set the new record's tracking_id to it. */ SET NEW.tracking_id = @old_tracking_id; /* ...and if delstatus are the same... */ IF NEW.delstatus = @old_delstatus THEN /* ...maintain the old record values. */ SET NEW.odanumber = @old_odanumber; SET NEW.courierful = @old_courierful; SET NEW.deliverydate = @old_deliverydate; END IF; END IF; END$$ DELIMITER ; 

CSV-файл (tracking.csv)

 odanumber,airwaybill,"courierful",delstatus,"deliverydate" 19,1,abc,0,2017-04-31 25,2,def,1,2017-05-31 103,3,ghi,1,2017-06-31 324,4,jkl,1,2017-07-31 564,5,mno,0,2017-08-31 

Функция LOAD DATA INFILE (вызывается из PHP)

 LOAD DATA INFILE "<PATH-TO>/tracking.csv" REPLACE INTO TABLE tests.tracking FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (odanumber, airwaybill, courierful, delstatus, deliverydate); 

Заметки:

*) Что касается LOAD DATA INFILE, возможно, вы столкнулись с ошибкой:

ERROR 1290 (HY000): сервер MySQL работает с параметром –secure-file-priv, поэтому он не может выполнить это утверждение

Это означает: У LOAD DATA INFILE нет разрешения на чтение файла csv. Поэтому вы должны установить secure-file-priv в файле конфигурации вашей базы данных (my.cnf или my.ini) самостоятельно. Как это:

 [mysqld] secure-file-priv = "<PATH-TO-FOLDER-CONTAINING-THE-CSV-FILES>/" 

*) Вы не можете определить хранимую процедуру, из которой запускается LOAD DATA INFILE.

В конце концов, есть и другие решения, связанные с временными таблицами, которые, без сомнения, могут работать отлично. Один из них представлен в этой замечательной статье . Таким образом, триггерное решение – это еще один подход.

Удачи!

Здесь есть два сценария:

1. столбцы таблицы точно соответствуют столбцам csv. в этом случае REPLACE – это ответ – это ключевое слово для LOAD DATA INFILE см. запись doc

2. столбцы таблицы не соответствуют столбцам csv: REPLACE заставит конфликтующие записи удалять и повторно вставлять, эффективно удаляя дополнительные данные. В этом случае LOAD DATA INFILE неэффективен сам по себе, вам нужен другой подход с фильтрацией файла до этого, с обновлениями через php или каким-либо другим способом.

В любом случае, если вы хотите добавить больше «логики» в процесс импорта, возможно, LOAD DATA INFIlE самом деле не подходит, но использование временных таблиц может быть очень полезным для всех благ.