MySQL 版本号排序

1、业务背景

版本检查接口返回版本号排序时出现如下图所示问题

普通的查询按数字值逐级比较，导致版本号高的排在了后面，这样版本检查根据版本号排序倒排取出来的不是最新的版本号，本文就此问题查询了诸多方法，在此做个总结。

本文线上测试地址为：SQL Fiddle

本文用到的SQL函数的具体说明：MySQL 常用函数一览_SunshineGGB的博客-CSDN博客

模拟测试的表字段和数据如下

create table AppVersion
( VersionNumber varchar(25));

insert into AppVersion (VersionNumber) values
('1.1.0'),
('2.0.34'),
('2.0.38'),
('2.1.0'),
('3.5.6'),
('3.5.8'),
('3.5.13');

2、具体方法

正常查询版本号并按版本号倒序

SELECT VersionNumber 
FROM AppVersion 
ORDER BY VersionNumber DESC

结果如本文首图所示，会出现3.5.13排在3.5.8和3.5.6下方

1）方法一

通过使用CONCAT添加'0.0.0'来确保每一行至少有4个部分拼接成IP地址的形式，然后使用利用IP处理函数INET_ATON()返回一个代表该地址数值的整数进行排序即可。

【前提】：如果你知道版本号总是有3个分量，并且每个分量总是小于256，那么你可以使用以下方法

执行代码：

SELECT VersionNumber 
FROM AppVersion 
ORDER BY INET_ATON(CONCAT(VersionNumber, '.0')) DESC

效果如图：

本例代码测试链接地址：SQL Fiddle

2）方法二

同样是利用IP地址函数INET_ATON()和字符串拼接函数CONCAT()。同时使用SUBSTRING_INDEX来拉出前4个部分，使每一个部分看起来像一个IP，然后通过IP处理函数INET_ATON()返回对应的整数值进行比较排序。

执行代码：

SELECT VersionNumber 
FROM AppVersion
ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(VersionNumber, '.0.0.0'), '.', 4)) DESC

效果如图：

本例代码测试链接地址：SQL Fiddle

3）方法三

获取版本范围：取每组版本号并向前补0至N位（比如5位、10位，下方代码为10位），最后拼接好再进行比较

执行代码：

SELECT VersionNumber
FROM AppVersion
ORDER BY CONCAT(
    LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(VersionNumber, '.', 1), '.', -
        1), 10, '0'),
    LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(VersionNumber, '.', 2), '.', -
        1), 10, '0'),
    LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(VersionNumber, '.', 3), '.', -
        1), 10, '0')
) DESC

效果如图：

本例代码测试链接地址：SQL Fiddle

4）方法四

获取版本顺序：如果只是排序，且版本号都是数字，可以采用每组版本号转数字（下方代码中的*1 、+0皆是转数字的小技巧），再进行排序

执行代码：

SELECT VersionNumber
FROM AppVersion
ORDER BY 
SUBSTRING_INDEX(VersionNumber, '.', 1 )*1 DESC,
SUBSTRING_INDEX(SUBSTRING_INDEX(VersionNumber, '.', 2 ),'.',-1)*1 DESC, 
SUBSTRING_INDEX(VersionNumber, '.', -1 )*1 DESC

效果如图：

本例代码测试链接地址：SQL Fiddle

类似，把*1改成+0一个意思

具体代码：

SELECT VersionNumber
FROM AppVersion
ORDER BY 
SUBSTRING_INDEX(VersionNumber, '.', 1 )+0 DESC,
SUBSTRING_INDEX(SUBSTRING_INDEX(VersionNumber, '.', 2 ),'.',-1)+0 DESC, 
SUBSTRING_INDEX(VersionNumber, '.', -1 )+0 DESC

本例代码测试链接地址：SQL Fiddle

5）方法五

版本号排序：思路是去掉小数点，右边补零防止错误填写，类型转换为数字然后再进行排序

执行代码：

SELECT
VersionNumber,
CONCAT(
    LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(VersionNumber, '.', 1), '.', -
        1), 3, '0'),
    LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(VersionNumber, '.', 2), '.', -
        1), 3, '0'),
    LPAD(CASE WHEN LENGTH(SUBSTRING_INDEX(VersionNumber, '.', 3)) =
        LENGTH(SUBSTRING_INDEX(VersionNumber, '.', 2)) THEN '000'
        ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(VersionNumber, '.', 3),
            '.', -1) END, 3, '0')
) AS VersionNumberSort
FROM
AppVersion
ORDER BY
VersionNumberSort DESC

效果如图：

6）方法六

通过拆分版本号并将每一部分排序为整数和字符串来获得同样的排序结果

执行代码：

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