MySQL query - compare version numbers

Question

I have a field in one of my SQL tables which stores a version number, like '3.4.23' or '1.224.43'.

Is there a way to use a greater than condition for this field?

SELECT * FROM versions WHERE version_number > '2.1.27'

Answer 1

Thanks for the tips @symcbean and @gordon-linoff, my final query looks like this:

SELECT *
FROM versions WHERE CONCAT(
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 1), '.', -1), 10, '0'),
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 2), '.', -1), 10, '0'),
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 3), '.', -1), 10, '0') 
       ) > CONCAT(LPAD(2,10,'0'), LPAD(1,10,'0'), LPAD(27,10,'0'));

This allows each component to be up to 10 digits long.

It transforms this:

X.XX.XXX > 2.1.27

to this:

'000000000X00000000XX0000000XXX' > '000000000200000000010000000027'

Answer 2

While it would be possible to write a function which would compare version numbers, is this the right way to solve the problem? Comparing f(x) and f(y) cannot be indexed. If you know that any part of the version number will never exceed, say, 4 digits, then you could create an additional field holding the value padded with 0's (or on Mariadb, use a virtual column) which can be indexed, e.g. 2.1.27 would become '000200010027`.

It would be a lot simpler if you stopped trying to use such a numbering schema and just used integers or datetimes. If you must stick with this numbering, then consider splitting the data into 3 columns.

For a quick hack, if you know that the version number will always have 3 component and each component will always be less than 256, then you could...

SELECT * 
FROM versions 
WHERE INET_ATON(CONCAT(version_number, '.0')) > INET_ATON('2.1.27.0');