MySQL 版本号排序

举报
GoodTimeGGB 发表于 2023/10/31 18:56:00 2023/10/31
【摘要】 1、业务背景版本检查接口返回版本号排序时出现如下图所示问题普通的查询按数字值逐级比较,导致版本号高的排在了后面,这样版本检查根据版本号排序倒排取出来的不是最新的版本号,本文就此问题查询了诸多方法,在此做个总结。本文线上测试地址为:SQL Fiddle本文用到的SQL函数的具体说明:MySQL 常用函数一览模拟测试的表字段和数据如下create table AppVersion( Versi...

1、业务背景

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

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

本文线上测试地址为:SQL Fiddle
本文用到的SQL函数的具体说明:MySQL 常用函数一览

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

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

效果如图:
image.png

本例代码测试链接地址: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
效果如图:

image.png

本例代码测试链接地址: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

效果如图:
image.png

本例代码测试链接地址: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

效果如图:
image.png

本例代码测试链接地址: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

效果如图:
image.png

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;

效果如图:
image.png

以上就是MySQL 版本号排序的介绍,做此记录,如有帮助,欢迎点赞关注收藏!

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。