MySQL 版本号排序

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: # 1、业务背景版本检查接口返回版本号排序时出现如下图所示问题![请在此添加图片描述](https://developer-private-1258344699.cos.ap-guangzhou.myqcloud.com/column/article/5877188/20231031-262125b3.png?x-cos-security-token=kcWkaWALSQ5t0gKzZRkVwYOOBJMLQ8Raf156e68c75de0329d6cf550fc2caafed2ct0qMmH-Si3jPfLmVc91k0roP8yZ3q-WASMT6cTBs1TsQOj5XI9PYK_A

1、业务背景

版本检查接口返回版本号排序时出现如下图所示问题
请在此添加图片描述

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

本文线上测试地址为: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

效果如图:
请在此添加图片描述

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

效果如图:
请在此添加图片描述

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

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
2月前
|
存储 关系型数据库 MySQL
MySQL中实施排序(sorting)及分组(grouping)操作的技巧。
使用这些技巧时,需要根据实际的数据量、表的设计和服务器性能等因素来确定最合适的做法。通过反复测试和优化,可以得到最佳的查询性能。
182 0
|
9月前
|
SQL 关系型数据库 MySQL
vb6读取mysql,用odbc mysql 5.3版本驱动
通过以上步骤,您可以在VB6中使用ODBC MySQL 5.3驱动连接MySQL数据库并读取数据。配置ODBC数据源、编写VB6代码
229 32
|
10月前
|
关系型数据库 MySQL Linux
MySQL版本升级(8.0.31->8.0.37)
本次升级将MySQL从8.0.31升级到8.0.37,采用就地升级方式。具体步骤包括:停止MySQL服务、备份数据目录、下载并解压新版本的RPM包,使用`yum update`命令更新已安装的MySQL组件,最后启动MySQL服务并验证版本。整个过程需确保所有相关RPM包一同升级,避免部分包遗漏导致的问题。官方文档提供了详细指导,确保升级顺利进行。
1000 16
|
11月前
|
关系型数据库 MySQL
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
239 5
|
11月前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(5)作者——LJS[含MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
|
11月前
|
关系型数据库 MySQL
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
182 1
|
算法 关系型数据库 MySQL
MySQL高级篇——排序、分组、分页优化
排序优化建议、案例验证、范围查询时索引字段选择、filesort调优、双路排序和单路排序、分组优化、带排序的深分页优化
MySQL高级篇——排序、分组、分页优化
|
关系型数据库 MySQL 数据库
MySQL高级篇——MVCC多版本并发控制
什么是MVCC、快照读与当前读、隐藏字段、Undo Log版本链、ReadView、举例说明、InnoDB 解决幻读问题
MySQL高级篇——MVCC多版本并发控制
|
12月前
|
Java 关系型数据库 MySQL
【编程基础知识】Eclipse连接MySQL 8.0时的JDK版本和驱动问题全解析
本文详细解析了在使用Eclipse连接MySQL 8.0时常见的JDK版本不兼容、驱动类错误和时区设置问题,并提供了清晰的解决方案。通过正确配置JDK版本、选择合适的驱动类和设置时区,确保Java应用能够顺利连接MySQL 8.0。
968 1
|
12月前
|
SQL JSON 关系型数据库
MySQL是一个广泛使用的开源关系型数据库管理系统,它有许多不同的版本
【10月更文挑战第3天】MySQL是一个广泛使用的开源关系型数据库管理系统,它有许多不同的版本
589 5

热门文章

最新文章

推荐镜像

更多