Как стать автором
Обновить

Автоматическая сортировка строк с вспомогательным порядковым столбцом — средствами MySQL

MySQL *
sort
Недавно пришлось выполнить махинацию с БД которая, как кажется на первый взгляд, совершенно невыполнима средствами MySQL. Перед глазами у меня была таблица товаров, сортировка которых осуществляется вспомогательным столбцом `order_num` ('порядковый номер'): она позволяет задавать ручную сортировку товаров.
Но вот потребовалось автоматически заполнить этот столбец так, чтобы товары оказались отсортированы по названию: то есть, с рядом ограничений, изменить столбец `order_num` во всей таблице. Очень хотелось обойтись средствами MySQL без привлечения каких-либо дополнительных инструментов, и задача была решена :)

Сложность задачи также в том, что MySQL не умеет делать UPDATE таблицы и одновременно читать из неё: в MyISAM таблица эксклюзивно блокируется при записи и нет возможности произвести чтение в подзапросе.


Задача


есть таблица товаров обычного интернет-магазина:
CREATE TABLE `products` (
    `product_id`    INT    NOT NULL    PRIMARY KEY    COMMENT 'id товара',
    `model`            VARCHAR(255)    NOT NULL    COMMENT 'название товара',
    `order_num`        INT    NOT NULL    COMMENT 'номер товара при сортировке',
    ) COMMENT 'товары';


* This source code was highlighted with Source Code Highlighter.

Изначально товары отсортированы чёрт знает как. Нужно отсортировать их в базе по `model`: то есть чтобы столбец сортировки `order_num` товаров увеличивался параллельно сортировке по `model`.
В результате таблица должна выглядеть так:
`product_id` `model` `order_num`
70 Ааа 10
10 Ббб 20
30 Ввв 30
20 Ггг 70

Движок этого магазина глючит если у нескольких товаров `order_num` совпадает. Сортировать товары по `model` при выводе нельзя: потеряется возможность сортировки вручную.

Идея



В столбце сортировки `order_num` должны стоять те же id товаров `product_id`, но в правильном порядке. Так мы точно избежим дублирования значений сортировки, а «ORDER BY `order_num`» выведет товары, отсортированные по `model` — то есть правильно :)

Замечу, что в частном случае проводилась сортировка товаров одной категории, поэтому нельзя использовать порядковые 0,1,2,… — для использования годится только id товара.

Итак, стоит задача распределения массива id товаров по столбцу `order_num`.

α-Решение


Сперва нужно создать временную таблицу `by_model`, в которую разместим `product_id` товаров, отсортированных по `model`. Кроме того, нужно добавить ещё один столбец: счётчик строк. Результат будет выглядеть, например, так:
`rowid` `product_id`
1 70
2 10
3 30
4 20

Это почти тривиально. Нужно только добавить столбец `rowid` с номерами строк:
SET @n_row := 0; # Начальное значение счётчика строк
CREATE TEMPORARY TABLE `by_model`
    SELECT @n_row:=@n_row+1 AS `rowid`, `product_id`
    FROM `products`
    ORDER BY `model` ASC;


* This source code was highlighted with Source Code Highlighter.


Аналогично создаётся ещё одна временная таблица `by_prod`: список всех `product_id`, отсортированный по возрастанию. Точно так же добавляется столбец-счётчик:
`rowid` `ord`
1 10
2 20
3 30
4 70

SET @n_ord := 0;
CREATE TEMPORARY TABLE `by_model`
    SELECT @n_ord:=@n_ord+1 AS `rowid`, `product_id` AS `ord`
    FROM `products`
    ORDER BY `product_id` ASC;


* This source code was highlighted with Source Code Highlighter.


Попробуем-ка заJOIN'ить эти две таблицы по общему столбцу `rowid`:
CREATE TEMPORARY TABLE `products-sort`
    SELECT `product_id`, `ord`
    FROM `by_model` NATURAL JOIN `by_prod`;


* This source code was highlighted with Source Code Highlighter.


И получаем такой набор данных:
`product_id` `order_num`
70 10
10 20
30 30
20 70


Становится очевидно, что если товарам с id'шниками из первого столбца таблицы `products-sort` присвоить порядковый номер из второго столбца, то цель будет достигнута :)
Вот так:
UPDATE `products` NATURAL JOIN `products-sort` SET `order_num`=`ord`;

* This source code was highlighted with Source Code Highlighter.


Финальная реализация & оптимизация



Вместо создания трёх временных таблиц можно обойтись одной лишь `products-sort`, а остальные поместить как вложенный запрос внутрь конструкции WHERE. И вот как будет выглядеть всё это, собранное в кучу:
# Счётчики для нумерации строк в двух вспомогательных таблицах в подзапросе
SET @n_row := 0, @n_ord := 0;
;;;
# Создание временной таблицы, содержащей отображение нового порядкового номера `ord` на изменяемый товар `product_id`. Это отображение используется при обновлении основной таблицы.
CREATE TEMPORARY TABLE `products-sort`
    SELECT `product_id`, `ord` FROM
        # Вспомогательная таблица: счётчик + id товаров, отсортированные по `model`
        (SELECT @n_row:=@n_row+1 AS `rowid`, `product_id`
                FROM `products`
                ORDER BY `model` ASC
                ) AS `by_model` # имя алиаса для подзапроса: используется наподобие временной таблицы
        # Соединение по столбцу счётчиков: `rowid`
        NATURAL JOIN
        # Вспомогательная таблица: счётчик + id товаров, отсортированные по id
        (SELECT @n_ord:=@n_ord+1 AS `rowid`, `product_id` AS `ord`
                FROM `products`
                ORDER BY `product_id` ASC
                ) AS `by_prod`;
;;;
# Обновление исходной таблицы с использованием данных из временной `products-sort`
UPDATE `products` NATURAL JOIN `products-sort` SET `order_num`=`ord`;


* This source code was highlighted with Source Code Highlighter.


Всего три запроса, и чистый MySQL.
О скорости исполнения в данном случае заботиться не приходится, но тем не менее она весьма приличная :)
Теги:
Хабы:
Всего голосов 33: ↑25 и ↓8 +17
Просмотры 6.5K
Комментарии Комментарии 31