Automatic sorting of rows with auxiliary ordinal column - using MySQL

Recently I had to perform a scam from the database, which, at first glance, seems completely impossible with MySQL. Before my eyes I had a table of products, the sorting of which is carried out by the auxiliary column `order_num` ('order number'): it allows you to set the manual sorting of goods.

But it was necessary to automatically fill this column so that the goods were sorted by name: that is, with a number of restrictions, change the ʻorder_num` column in the entire table. I really wanted to do without MySQL using any additional tools, and the task was solved :)



The complexity of the task is also in the fact that MySQL does not know how to make UPDATE tables and at the same time read from it: in MyISAM, the table is exclusively locked when writing and there is no possibility to read in the subquery.

Task

There is a table of goods of an ordinary 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 the devil knows how. We need to sort them in the database by `model`: that is, so that the sorting column` order_num` of goods increases in parallel to the sorting by `model`.

As a result, the table should look like this:

`product_id`	`model`	`order_num`
70	Ahh	ten
ten	Bbb	20
thirty	Ibv	thirty
20	Yy	70

The engine of this store is buggy if several products have `order_num` the same. Sort items by `model` in the output can not: lost the ability to sort manually.

')

Idea

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



I note that in the particular case, goods of one category were sorted, therefore it is impossible to use ordinal 0,1,2, ... - only the id of the product is suitable for use.



So, there is the task of distributing the id array of goods on the ʻorder_num` column.

α-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 one more column: row counter. The result will look like this:

`rowid`	`product_id`
one	70
2	ten
3	thirty
four	20

This is almost trivial. It is only necessary to add a column `rowid` 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 `by_prod` is created: a list of all` product_id`, sorted in ascending order. Similarly, a column counter is added:

`rowid`	`ord`
one	ten
2	20
3	thirty
four	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 .

Let us try to declare 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 the following data set:

`product_id`	`order_num`
70	ten
ten	20
thirty	thirty
20	70

It becomes obvious that if you assign a serial number from the second column to products with id's from the first column of the `products-sort` table, then the goal will be reached :)

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 get by with only `products-sort`, and place the rest as a subquery inside the WHERE clause. And this is how it will look like, collected in a heap:

#

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 requests, and pure MySQL.

In this case it is not necessary to take care of the speed of execution, but nevertheless it is very decent :)