Автоматическая сортировка строк с вспомогательным порядковым столбцом — средствами MySQL
Недавно пришлось выполнить махинацию с БД которая, как кажется на первый взгляд, совершенно невыполнима средствами 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)