CODEWITHSUNDEEP
mysqlsorting
rakesh
rakesh

Reputation: 273

mysql sorting of version numbers

I have values like:

1.1.2 
9.1 
2.2
4
1.2.3.4
3.2.14
3.2.1.4.2
.....

I need to sort those values using mysql. The data type for this one is varbinary(300).

The desired output will be like:

1.1.2
1.2.3.4
2.2
3.2.1.4.2
3.2.14
4
9.1

The Query is: 

select version_number from table order by version_number asc

it does not give the correct sorting order.

The desired output of this is: 

1.1.2
1.2.3.4
2.2
3.2.1.4.2
3.2.14 
4
9.1

The version numbers are up to 20 digits (like 1.2.3.4.5.6.7.8.9.2.34) and more also. There is no particular max size and the standard version is just like above mentioned.

Upvotes: 27

Views: 12640

Answers (5)

Vitaliy
Vitaliy

Reputation: 63

Although, the @TrinitronX answer is pretty elegant, it does not work for the wide range of possible semantic versioning values. In particular those, that consist of the pre-release part like 1.0.0-beta.1.

Another way is to keep a sorted list of versions in the MySQL database table. Let's create the following database table:

CREATE TABLE `versions` (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    version VARCHAR(255) COLLATE utf8mb4_bin NOT NULL,
    sort_order BIGINT UNSIGNED NOT NULL,
    UNIQUE KEY unique_version (version),
    KEY idx_sort_order_version (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Every time we add a new version to the versions DB table we calculate the sort_order value before inserting the record. In theory the complexity of this operation should be O(log n). Having this, we can get the sorted list of versions using the SQL query below:

SELECT `version` FROM `versions` ORDER BY sort_order;

I created the https://github.com/recipe/mysql-sermver project, to implement this idea in two MySQL stored functions:

1. VERSION_COMPARE(version1, version2)

This function supports the Semantic Versioning 2 as well as the most of the real life versions. It returns

  • 0 if the both versions are equal,
  • 1 if the version1 is greater than the version2,
  • -1 if the version2 is greater than the version1.
SELECT VERSION_COMPARE('1.0.0', '1.0.0');
0

SELECT VERSION_COMPARE('1.0.2-alpha', '1.0.2');
-1

SELECT VERSION_COMPARE('1.0.2-beta', '1.0.2-alpha');
1

2. GET_SORT_ORDER(version)

This function calculates the sort_order value of a newly added version, and can be used inside the BEFORE INSERT database trigger on the versions DB table.

DELIMITER //
CREATE TRIGGER bi_versions_set_sort_order BEFORE INSERT ON versions
FOR EACH ROW
BEGIN
    DECLARE v BIGINT UNSIGNED;

    IF NEW.sort_order = 0 THEN
        SELECT GET_SORT_ORDER(NEW.version) INTO v;
        SET NEW.sort_order = v;
    END IF;
END //

How it works for the question above?

You need to install these two stored functions from the mysql-semver project to your MySQL database. Then you can add new versions and select them ordered.

INSERT versions (version, sort_order) 
VALUES
('1.1.2', 0), 
('9.1', 0), 
('2.2', 0), 
('4', 0), 
('1.2.3.4', 0), 
('3.2.14', 0), 
('3.2.1.4.2', 0);

SELECT `version` FROM `versions` ORDER BY sort_order;
+---------+
|version  |
+---------+
|1.1.2    |
|1.2.3.4  |
|2.2      |
|3.2.1.4.2|
|3.2.14   |
|4        |
|9.1      |
+---------+

Upvotes: 1

Dávid Horváth
Dávid Horváth

Reputation: 4320

Use regular expressions. First normalize the value:

SELECT REGEXP_REPLACE(
    REGEXP_REPLACE(
        REGEXP_REPLACE('v1.22.333', '^v', ''),
        '(^|\\.)(\\d+)',
        '\\100000\\2'
    ),
    '0+(\\d{5})(\\.|$)',
    '\\1\\2'
)

Output:

00001.00022.00333

Then you can sort normally.

This solution works with any number of components. You can scale component length from 5 to any fixed length.

Upvotes: 5

vovafeldman
vovafeldman

Reputation: 595

If you'd like to support versions like 1.1-beta or using old MySql versions without INTE_ATON, you can get the same sort by splitting the version and sorting each part as an integer and string:

SELECT
    version,
    REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 1), LENGTH(SUBSTRING_INDEX(version, '.', 1 - 1)) + 1), '.', '') v1,
    REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 2), LENGTH(SUBSTRING_INDEX(version, '.', 2 - 1)) + 1), '.', '') v2,
    REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 3), LENGTH(SUBSTRING_INDEX(version, '.', 3 - 1)) + 1), '.', '') v3,
    REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 4), LENGTH(SUBSTRING_INDEX(version, '.', 4 - 1)) + 1), '.', '') v4
FROM 
    versions_table
ORDER BY
    0+v1, v1 DESC, 0+v2, v2 DESC, 0+v3, v3 DESC, 0+v4, v4 DESC;

Upvotes: 4

TrinitronX
TrinitronX

Reputation: 5203

Try abusing the INET_ATON function to do the sorting like so:

SELECT version_number FROM table ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version_number,'.0.0.0'),'.',4))

This trick was originally posted on the mysql mailing list, so many thanks to the original poster, Michael Stassen!

Here's what he had to say:

If each part is no larger than 255, you can leverage INET_ATON() to do what you want (up to the 4th part). The trick is making each of these look like an IP first by using CONCAT to add '0.0.0' to make sure every row has at least 4 parts, then SUBSTRING_INDEX to pull out just the first 4 parts.

Now, I must point out that because we are sorting on a function of the column, rather than on the column itself, we cannot use an index on the column to help with the sort. In other words, the sorting will be relatively slow.

In the latter case, he recommends a solution similar to the one posted by @spanky (separate columns).

Upvotes: 59

spanky
spanky

Reputation: 1499

I would store it in three separate columns, one for each part of the version number.

Make each column a TINYINT and even create an index across the 3 columns. That should make things simple.

Then you can do: select CONCAT(v1,'.',v2,'.',v3) AS version_number FROM table ORDER BY v1 asc, v2 asc, v3 asc

Upvotes: 7

Related Questions