Как правильно реализовать множественное обновление данных mysql?

Question

[Сергей]

Доброго времени суток. Встал вопрос о множественном обновлении данных. Изначально алгоритм был написан с использованием конструкции on duplicate key и отлично работал, но был слишком медленным из-за алгоритмического расточительства в коде php (квадратичная сложность, которая давала неимоверно большое количество итераций цикла и приводила к таймауту скрипта при больших объемах данных), сейчас алгоритм переписан и пришлось отказаться от конструкции insert on duplicate key update, потому что по алгоритму меня два массива данных теперь. Один который нужно внести в бд, другой который нужно обновить, следовательно если в секции, которую необходимо обновить делать insert on duplicate key, то мало того, что insert никогда не выполнится, так еще и auto_increment будет расти.
Вопрос в том, чтобы найти подход, который позволил бы заапдейтить, скажем, 10 000 записей в короткие сроки. Решения к которым я пришел, путем гугления:
1. Prepared statements
2. Оставить insert on duplicate key update
3. Использовать конструкцию типа:

UPDATE tbl_country SET price = CASE
WHEN code = 1 THEN 123;
WHEN code = 2 THEN 456;
…
END
WHERE code IN (1,2,…)

Comments

[Алексей Уколов]

Если вы покажете свой код, будет больше шансов получить ответ. Пока не очень ясно в чем проблема - INSERT ... ON DUPLICATE KEY UPDATE именно для таких целей и сделан.

Answer 1

[Сергей]

Провел тесты - insert on duplicate key update идеально работает, даже при условии, что insert никогда не выполнится. Обновление 10 000 строк происходит за 0.04 с. Всем спасибо за наводки.

Comments

[hjk]

Pjeroo А как же быть с разрастанием auto_increment?

Answer 2

[index0h]

Можно UPDATE + JOIN использовать.

Answer 3

[ivankomolin]

Чтобы обновить большое количество записей в таблице, можно использовать insert on duplicate key update в следующей конструкции:

INSERT INTO 
{{table}}
(id, field1, field2) 
VALUES 
(1, "значение1.1", "значение1.2"),
(2, "значение2.1", "значение2.2"),
(3, "значение3.1", "значение3.2"),
(4, "значение4.1", "значение4.2"),
(5, "значение5.1", "значение5.2")
ON DUPLICATE KEY UPDATE 
field1 = VALUES(field1), field2 = VALUES(field2)

Однако следует учесть количество данных в полях, при 10000 записей текст запроса может превысить лимит, в этом случае либо увеличивать лимит, либо разбивать данные на несколько частей.

Answer 4

[Евгений]

Это что, не одноразовая операция? Если такие апдейты запускаются каждый день, то это не в них проблема.