Mysql сортировка номеров версий

У меня есть значения как:

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

Мне нужно отсортировать эти значения с помощью mysql. Тип данных для этого является varbinary (300).

Желаемый результат будет выглядеть так:

1.1.2
1.2.3.4
2.2
3.2.1.4.2
3.2.14
4
9.1

Запрос:

select version_number from table order by version_number asc

он не дает правильного порядка сортировки.

Требуемый результат:

1.1.2
1.2.3.4
2.2
3.2.1.4.2
3.2.14 
4
9.1

Номера версий могут содержать до 20 цифр (например, 1.2.3.4.5.6.7.8.9.2.34) и многое другое. Нет особого максимального размера, и стандартная версия так же, как и выше.

Ответ 1

Попробуйте использовать функцию INET_ATON для сортировки следующим образом:

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

Этот трюк был первоначально размещен в списке рассылки mysql, поэтому большое спасибо оригинальному плакату, Майклу Стассену!

Вот что он должен был сказать:

Если каждая часть не превышает 255, вы можете использовать INET_ATON() для выполнения что вы хотите (до 4-й части). Трюк делает каждый из этих сначала взгляните на IP, используя CONCAT, чтобы добавить "0.0.0", чтобы убедиться, что каждый строка имеет не менее 4 частей, затем SUBSTRING_INDEX, чтобы вытащить только первые 4 части.

Теперь я должен указать, что, поскольку мы сортируем по функции столбца, а не на самом столбце, мы не можем использовать индекс на чтобы помочь с сортировкой. Другими словами, сортировка будет относительно медленно.

В последнем случае он рекомендует решение, аналогичное решению, которое отправляется @spanky (отдельные столбцы).

Ответ 2

Я бы сохранил его в трех отдельных столбцах, по одному для каждой части номера версии.

Сделайте каждый столбец TINYINT и даже создайте индекс в трех столбцах. Это должно сделать вещи простыми.

Тогда вы можете сделать: select CONCAT(v1,'.',v2,'.',v3) AS version_number FROM table ORDER BY v1 asc, v2 asc, v3 asc

Ответ 3

Если вы хотите поддерживать версии типа 1.1-beta или используя старые версии MySql без INTE_ATON, вы можете получить один и тот же вид, разделив версию и отсортировав каждую часть как целое число и строку:

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;