Автоматическая сортировка строк с вспомогательным порядковым столбцом — средствами 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.
    О скорости исполнения в данном случае заботиться не приходится, но тем не менее она весьма приличная :)

    Комментарии 31

      +2
      По-моему всё это можно выполнить одним запросом.
        0
        Я бы тоже не отказался. Правда.
          0
          Надо делать self-join. Сейчас попробую написать.
        0
        Хорошо что возникла «интересная» задача! И автор написал пост.)
          +2
          /*сортировка товара по алфавиту*/
          SET @rownum := 0;
          UPDATE
           products p
          SET
           p.order_num = (SELECT
            temp.new_order_num
           FROM
            (SELECT
             p2.product_id,
             ((@rownum := @rownum + 1) * 10) AS new_order_num
            FROM
             products p2
            ORDER BY
             p2.model) AS temp
           WHERE
            temp.product_id = p.product_id)


          * This source code was highlighted with Source Code Highlighter.
            0
            Наверное вы правы. Однако документация последней MySQL гласит, что нельзя одновременно делать UPDATE таблицы, из которой происходит чтение в подзапросе :)
              +3
              Там же в комментариях к документации пишут, что такое ограничение обходится путем вынесения подзапроса на 2 уровня вглубь — в этом случае данные полностью материализуются и могут использоваться в обновлении. У нас именно такой запрос и получился — работает на 5.0 сервере.
                0
                Любопытно, у меня на 5.1.37 выдаётся ошибка «Can't reopen table: 'p'». Могли ошибиться с адаптацией примера, но это неважно: идея понятна, спасибо :) Правда, сработает она только если допустимо присвоение своих чисел в столбец сортировки, а я писал что этого делать нельзя: сортируются только товары в одной категории. Если таким запросом сортировать несколько категорий раздельно — получатся повторы в столбце сортировки.
          • НЛО прилетело и опубликовало эту надпись здесь
              +1
              Приятно, что у вас такие запросы пишутся с полпинка. А я-то дурак рассказываю про приёмы которые нагуглить непросто… ;)
              • НЛО прилетело и опубликовало эту надпись здесь
                  +4
                  Ну не постить же теперь тут главы из всем известных книжек?
                  У автора возникла проблема и он ее оригинально решил. И комментарии к этой конкретной зарисовке довольно любопытны.
                  • НЛО прилетело и опубликовало эту надпись здесь
                      +2
                      Чтобы быть до конца уверенным, напишите же как именно следовало тут поступить без применения переменных. Кстати они не глобальные, а сессионные.
                        0
                        Я пробовал вариант «CREATE TEMPORARY TABLE `by_model` (… AUTO_INCREMENT… ) SELECT ...» но по какой-то причине отмёл его. Вспомню причину — поделюсь :) Вы правы, в общем случае этого будет достаточно, но есть и минус: придётся описать все поля временной таблицы вручную. Лишний труд имхо
                        • НЛО прилетело и опубликовало эту надпись здесь
                            0
                            В данном случае всё просто, мускл вполне верно выбрал тип обоих полей INT. Спасибо, буду знать что не всё так сахарно :)
                            • НЛО прилетело и опубликовало эту надпись здесь
                +1
                А можно пояснить, почему из «в частном случае проводилась сортировка товаров одной категории» следует, что «нельзя использовать порядковые 0,1,2,… — для использования годится только id товара.»
                Все равно ведь айдишки продуктов попадают в колонку order_num случайным в общем то образом и ситуации, когда айди продукта из одной категории попадает в ордер_нум продукта из другой категории, будут! Значит колонку order_num для определения категории использовать нельзя и… и 0,1,2 получается тогда ничем не хуже?
                  +1
                  Не, не будут :) парочка дополнительных WHERE выберет только id товаров из нужной категории, и всё будет пучком :)
                  Конкретно — в оба подзапроса ставится «WHERE `category_id`=69». Этот момент я опустил чтоб не загромождать суть частностями
                  0
                  А зачем вообще мучаться???
                  Можно вполне даже без подзапросов обойтись:
                  SET @order := 0;
                  UPDATE `products` SET `order_num` = @order := @order + 10 ORDER BY `model` ASC;


                  * This source code was highlighted with Source Code Highlighter.
                    +1
                    Для надёжности лучше даже
                    SET @order := 0;
                    UPDATE `products` SET `order_num` = @order := (@order + 10) ORDER BY `model` ASC;


                    * This source code was highlighted with Source Code Highlighter.
                      0
                      Ну видимо автор забыл что UPDATE тоже имеет сортировку, соот-но и пытался задачу сортировки переложить на SELECT, что добавило своих ограничений. Я, читая условия задачи, тоже об этом не вспомнил, все-таки не так часто это используется, или в случае когда есть внутренний счетчик, или когда обновление/удаление ограниченно через LIMIT.
                        0
                        Видимо вы невнимательно читали топик. «Замечу, что… нельзя использовать порядковые 0,1,2,… — для использования годится только id товара.». Глупый движой поставил мне жёсткие рамки :)
                          0
                          Ограничение из-за того что поле сортировки должно быть уникально по всей таблице, а пересортировка может производится отдельно по категориям?
                            0
                            Именно :)
                            +1
                            Да, уже вник в смысл танцев с бубном. Чтобы было понятнее данные надо немного изменить, тогда станет понятнее, например, вместо 40 поставить 70.

                            P.S. Надо будет в следующий раз внимательнее читать.
                              –1
                              P.S.S. а еще надо взять за привычку перечитывать свой комментарий перед отправлением :(
                                +1
                                Вы правы, изменил для наглядности. Спасибо! :)
                        +1
                        Но ведь можно поддерживать уникальный order_num при вставке новой записи. То есть, при вставке берётся максимальный и увеличивается на единицу.
                          0
                          В принципе да, можно. Единственное что мне в этом не нравится — теоретически неограниченный рост столбца order_num :)

                        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                        Самое читаемое