Automatic row sorting with auxiliary ordinal column - MySQL tools

    sort
    Recently, I had to perform a fraud with the database, which, as it seems at first glance, is completely impossible by means of MySQL. Before my eyes I had a table of goods, the sorting of which is carried out by the auxiliary column `order_num` ('serial number'): it allows you to set manual sorting of goods.
    But it was required to automatically fill this column so that the goods are sorted by name: that is, with a number of restrictions, change the column `order_num` in the entire table. I really wanted to do without MySQL using any additional tools, and the problem was solved :)

    The difficulty of the task is that MySQL is not able to do UPDATE tables and at the same time read from it: in MyISAM the table is exclusively locked when writing and there is no way to read in the subquery.


    Task


    There is a table of goods of a regular online store:
    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.

    Initially, the goods are sorted. God knows how. You need to sort them in the database by `model`: that is, so that the sorting column` order_num` of goods increases in parallel with sorting by `model`.
    As a result, the table should look like this:
    `product_id``model``order_num`
    70Aah10
    10Bbb20
    thirtyBBBthirty
    20Yyyy70

    The engine of this store is buggy if several order_num matches. Sorting products by `model` at the output is not possible: manual sorting will be lost.

    Idea



    In the sort column `order_num` should be the same product id` product_id`, but in the correct order. So we will definitely avoid duplication of sorting values, and “ORDER BY` order_num` ”will display goods sorted by` model` - that is, right :)

    I note that in a particular case, goods of one category were sorted, so you cannot use ordinal 0.1, 2, ... - only the product id is suitable for use.

    So, the task is to distribute an array of goods id on the column `order_num`.

    α-Solution


    First you need to create a temporary table `by_model`, in which we place the` product_id` of goods sorted by `model`. In addition, you need to add another column: row counter. The result will look, for example, like this:
    `rowid``product_id`
    170
    210
    3thirty
    420

    This is almost trivial. You only need to add the `rowid` column with line numbers:
    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.


    Similarly, another temporary table is created, `by_prod`: a list of all` product_id`, sorted in ascending order. In the same way, a counter column is added:
    `rowid``ord`
    110
    220
    3thirty
    470

    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.


    Let's try for JOIN'it these two tables on the common column `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.


    And we get this data set:
    `product_id``order_num`
    7010
    1020
    thirtythirty
    2070


    It becomes obvious that if goods with id 's from the first column of the `products-sort` table are assigned a serial number from the second column, then the goal will be achieved :)
    Like this:
    UPDATE `products` NATURAL JOIN `products-sort` SET `order_num`=`ord`;

    * This source code was highlighted with Source Code Highlighter.


    Final implementation & optimization



    Instead of creating three temporary tables, you can do just one `products-sort`, and put the rest as a subquery inside the WHERE clause. And this is how all this, collected in a heap, will look like:
    # Счётчики для нумерации строк в двух вспомогательных таблицах в подзапросе
    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.


    Only three queries, and pure MySQL.
    In this case, you don’t have to worry about speed of execution, but nevertheless it is very decent :)

    Also popular now: