在MySQL/MariaDB中如何使用ORDER BY对版本号进行正确排序？

我有如下版本号数据：

2022.20.10
2022.24.6
2022.24.5
2022.24.1
2022.20.9
2022.20.8
2020.39.104.2.1
2022.3.103.0.1

尝试用IP地址相关函数排序无法得到想要的结果，期望排序后结果为：

2022.24.6
2022.24.5
2022.24.1
2022.20.10
2022.20.9
2022.20.8
2022.3.103.0.1
2020.39.104.2.1

以下是主流数据库的实现方案：

MySQL/MariaDB 实现方式 #

利用SUBSTRING_INDEX拆分版本号的各个分段，转换为整数后按降序排序：

SELECT version_column
FROM your_table
ORDER BY
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version_column, '.', 1), '.', -1) AS UNSIGNED) DESC,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version_column, '.', 2), '.', -1) AS UNSIGNED) DESC,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version_column, '.', 3), '.', -1) AS UNSIGNED) DESC,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version_column, '.', 4), '.', -1) AS UNSIGNED) DESC,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version_column, '.', 5), '.', -1) AS UNSIGNED) DESC;

通过嵌套SUBSTRING_INDEX提取每个版本分段，转成无符号整数后依次降序排列，可覆盖最多5段的版本号结构。

PostgreSQL 实现方式 #

PostgreSQL支持将字符串转为整数数组，直接对数组排序即可实现自然排序：

SELECT version_column
FROM your_table
ORDER BY string_to_array(version_column, '.')::int[] DESC;

string_to_array将版本号拆分为字符串数组，::int[]转为整数数组后，数组会按元素依次比较排序，降序后即可得到期望结果。

SQL Server 实现方式 #

使用PARSENAME函数拆分版本号（注意该函数从右往左分段，需调整顺序）：

SELECT version_column
FROM your_table
ORDER BY
    CAST(PARSENAME(version_column, 5) AS INT) DESC,
    CAST(PARSENAME(version_column, 4) AS INT) DESC,
    CAST(PARSENAME(version_column, 3) AS INT) DESC,
    CAST(PARSENAME(version_column, 2) AS INT) DESC,
    CAST(PARSENAME(version_column, 1) AS INT) DESC;

PARSENAME原本用于拆分数据库对象名，这里用来拆分版本号。对于不足5段的版本号，PARSENAME会返回NULL，排序时NULL会自动排在后面，符合需求。

内容的提问来源于stack exchange，提问作者Joe