войти зарегистрироваться

MySQL whois

индекс
202,61

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

комментарии (31)

  • По-моему всё это можно выполнить одним запросом.
    • Я бы тоже не отказался. Правда.
      • Надо делать self-join. Сейчас попробую написать.
  • Хорошо что возникла «интересная» задача! И автор написал пост.)
  • /*сортировка товара по алфавиту*/
    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.
    • Наверное вы правы. Однако документация последней MySQL гласит, что нельзя одновременно делать UPDATE таблицы, из которой происходит чтение в подзапросе :)
      • Там же в комментариях к документации пишут, что такое ограничение обходится путем вынесения подзапроса на 2 уровня вглубь — в этом случае данные полностью материализуются и могут использоваться в обновлении. У нас именно такой запрос и получился — работает на 5.0 сервере.
        • Любопытно, у меня на 5.1.37 выдаётся ошибка «Can't reopen table: 'p'». Могли ошибиться с адаптацией примера, но это неважно: идея понятна, спасибо :) Правда, сработает она только если допустимо присвоение своих чисел в столбец сортировки, а я писал что этого делать нельзя: сортируются только товары в одной категории. Если таким запросом сортировать несколько категорий раздельно — получатся повторы в столбце сортировки.
  • слишком много воды ради 1 запроса…
    • Приятно, что у вас такие запросы пишутся с полпинка. А я-то дурак рассказываю про приёмы которые нагуглить непросто… ;)
      • спасибо что ткото за меня беспокоится, но и правда эта задача ничем не отличается от повседневных, есть куда более интересные вопросы в sql — например оптимизация запросов и структуры базы(когда это возможно ..) ради увеличения выборок и снижения нагрузки
        • Ну не постить же теперь тут главы из всем известных книжек?
          У автора возникла проблема и он ее оригинально решил. И комментарии к этой конкретной зарисовке довольно любопытны.
          • все было бы круто, если бы не

            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;

            для этого существует autoincrement, а не «глобальные переменные». ибо переменная тут — костыль. я уверен что эту задачу можно решить в 1 несложный запрос, если бы мне попалась такая задача, то я бы конечно попытался и уверен что нашел бы вариант :)

            а вот оптимизации всяческие — уже интересная тема, тем более что остро стоящая
            • Чтобы быть до конца уверенным, напишите же как именно следовало тут поступить без применения переменных. Кстати они не глобальные, а сессионные.
            • Я пробовал вариант «CREATE TEMPORARY TABLE `by_model` (… AUTO_INCREMENT… ) SELECT ...» но по какой-то причине отмёл его. Вспомню причину — поделюсь :) Вы правы, в общем случае этого будет достаточно, но есть и минус: придётся описать все поля временной таблицы вручную. Лишний труд имхо
              • ничего не лишний. посмотрите на структуры таблич откуда выбираете и что у вас получилось после create table…

                посмотреть можно через show create table `by_model` и вы можете быть приятно удивлены! длины столбцов не совпадают, типы (даже для int) тоже не совпадают, потому что мускуль выбрал САМ, а выбрал — что ему по душе было. надо ручками описывать если дальше ст абличкой хочется без проблем работать, может конечно мне мускули всегда говенные попадаются и я знаю о таких подводных камнях :)

                зы: переменная и правда сессионная, я имел ввиду глобальная — по отношению к запросу. то есть это не манипуляция со столбцами таблиц в запросе.
                • В данном случае всё просто, мускл вполне верно выбрал тип обоих полей INT. Спасибо, буду знать что не всё так сахарно :)
                  • ну у меня он разок выбрал tinyint вместо int и соответственно результаты были шокирующие, когда заметили эту «мелочь» :))
  • А можно пояснить, почему из «в частном случае проводилась сортировка товаров одной категории» следует, что «нельзя использовать порядковые 0,1,2,… — для использования годится только id товара.»
    Все равно ведь айдишки продуктов попадают в колонку order_num случайным в общем то образом и ситуации, когда айди продукта из одной категории попадает в ордер_нум продукта из другой категории, будут! Значит колонку order_num для определения категории использовать нельзя и… и 0,1,2 получается тогда ничем не хуже?
    • Не, не будут :) парочка дополнительных WHERE выберет только id товаров из нужной категории, и всё будет пучком :)
      Конкретно — в оба подзапроса ставится «WHERE `category_id`=69». Этот момент я опустил чтоб не загромождать суть частностями
  • А зачем вообще мучаться???
    Можно вполне даже без подзапросов обойтись:
    SET @order := 0;
    UPDATE `products` SET `order_num` = @order := @order + 10 ORDER BY `model` ASC;

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

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

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