Как сделать массовое обновление записей в mysql?

Question

[rinaz22]

Всем привет! Есть очень большой объем данных. Нужно все это обработать и обновить столбец stat.
Как можно сделать массовое обновление записей в mysql? Чтобы не делать например 100-500 запросов UPDATE, а одним запросом.
Всего параметров stat не так много(около 10).
Пример, если сделать по одному запросу:

UPDATE `data` SET `stat` = '1' WHERE `id` = '123'
UPDATE `data` SET `stat` = '2' WHERE `id` = '321'

Нашел такой код:

UPDATE a 
SET fruit = (CASE id WHEN 1 THEN 'apple'
                     WHEN 2 THEN 'orange'
                     WHEN 3 THEN 'peach'
             END)
WHERE id IN(1,2 ,3);

Но не могу понять как переделать под свою задачу.

Comments

[rinaz22]

Dr. Bacon, к сожалению я не знаю что такое autocommit
Пример запроса:

$result = mysqli_query($db, "UPDATE `data` SET `stat` = '1' WHERE `id` = '123'");

По идее это должно быть внутри цикла, если по одному обновлять

[Akina]

Надо сформировать данные в виде набора записей (id, stat) и использовать его в запросе в секции FROM.

[rinaz22]

Я понял как это использовать для своей задачи:

UPDATE a 
SET fruit = (CASE id WHEN 1 THEN 'apple'
                     WHEN 2 THEN 'orange'
                     WHEN 3 THEN 'peach'
             END)
WHERE id IN(1,2 ,3);

Вопрос лишь в том, на сколько это правильно с точки зрения нагрузки?
Будет разница между одиночным обновлением и таким массовым обновлением, если поставить лимит 500 за раз?

Answer 1

[Akina]

WITH `cte` AS (
    SELECT 123 `id`, 1 `stat` UNION ALL
    SELECT 321     , 2        UNION ALL
 -- ...
    SELECT 456     , 8        UNION ALL
    SELECT 789     , 9
)
UPDATE `data`
JOIN   `cte` USING ( `id` )
SET `data`.`stat` = `cte`.`stat`;

Comments

[rinaz22]

А чем отличается этот запрос от одиночных запросов? Я так понял, будет столько же SELECT, сколько записей нужно обновить?

[Akina]

rinaz22,

чем отличается этот запрос от одиночных запросов?

Это - один запрос. Который один раз передаётся на сервер, один раз парсится, для которого один раз создаётся план запроса. Но обновляет он сразу все записи.

Я так понял, будет столько же SELECT, сколько записей нужно обновить?

Неправильно понял.

[rinaz22]

Akina,

Неправильно понял.

Например если нужно обновить 100 записей, сколько всего будет SELECT?
И при использовании этого запроса, возникает ошибка на UPDATE после ')'

[Akina]

rinaz22,

Например если нужно обновить 100 записей, сколько всего будет SELECT?

В CTE - сто. Но запрос-то всё равно один. Ну и скалярный запрос без источника данных (это SELECT-ы в CTE) - штука быстрая.

при использовании этого запроса, возникает ошибка на UPDATE после ')'

Ну где-то косячишь, значит. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e0d6b5...

[rinaz22]

Да, UNION ALL в последнем SELECT нужно было убрать. Спасибо!
Еще один вопрос. Есть какие-либо ограничения по количеству SELECT внутри? В документации про это не видел

[Akina]

rinaz22,

Есть какие-либо ограничения по количеству SELECT внутри?

Нет.
Есть только ограничение на размер входящего пакета.

Но если количество значений большое (ну где-то от 10 тысяч и более), то лучше передавать сериализованный массив значений (CSV, JSON) и парсить его в запросе.