Програмування → MySQL, одним пострілом двох зайців

Іноді виникає потреба оновити поле в багатьох рядках таблиці, й для кожного рядка встановити своє значення. Нижче описано, як в MySQL можна зробити це одним запитом.

Як приклад розглянемо таблицю, в якій збережено меню:

id title url sort
9 Home / 1
7 Links /links 2
4 Photo /photo 3
3 Blog /blog 4
1 About /about 5

Я часто реалізую сортування списку через jQuery UI sortable, це дозволяє змінювати порядок елементів перетягуючи їх мишею, що досить наглядно і зручно. В результаті сортування повертається масив sort => id.

array (
    1 => 9,
    2 => 7,
    3 => 4,
    4 => 3,
    5 => 1
)

Після цього є два варіанти, генерувати update для кожного рядка, або ж користуватися спеціальними можливостями MySQL. Як виявилося в процесі написання нотатки, є два способи виконати поставлене завдання:

$values = implode(',', range(1, count($data)));
$ids = implode(',', $data);
$query = 'UPDATE some_table SET
    sort = ELT(FIELD(id, ' . $ids . '), ' . $values . ')
    WHERE id IN (' . $ids . ')';

та

$mstr = '';
foreach ( $data as $sort => $id ){
    $mstr .= "($id, $sort),";    
}

$query = 'INSERT INTO some_table (id, sort) VALUES
    ' . rtrim($mstr, ',') .
    ' ON DUPLICATE KEY UPDATE sort=VALUES(sort)';

$data — наш масив (sort => id).

Завжди користувався першим варіантом, та виявилося, що при оновленні 1000 рядків він програє ~6мс другому (що важливо у високо навантажених проектах).

Теги: ,
07.10.2011 10:51 Автор: Strange_V Хіти: 284

Напишіть відгук