dougongnan2167 2014-11-08 23:03
浏览 418
已采纳

MySQL按版本号+名称排序

I have a table of version numbers for a program in a MySQL database, organized like this:

versionnumber
----
1.4.0
1.5.0
1.6
1.6.1
1.6.2
1.7DevBuild1
1.7DevBuild2
1.7DevBuild3
1.7
1.8

I looked up how to order by version numbers, with dots, in MySQL queries, and found a pretty creative solution elsewhere on SO.

SELECT * 
FROM versions
ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(versionnumber,'.0.0.0'),'.',4))

This works by ordering the version number as if it were an IP Address in INET_ATON. It works. Sort of. It organizes the version numbers correctly, but it doesn't recognize the Dev builds, instead putting them to the top and ignoring them because they contain text, which IP Addresses should not contain.

1.7DevBuild1
1.7DevBuild2
1.7DevBuild3
1.4.0
1.5.0
1.6
1.6.1
1.6.2
1.7
1.8

so I had an idea: replace all the letters a-z with nothing, and that way it will contain only numbers and dots. But MySQL can't do RegExp in replace. Is there another way or I have to sort the returned array in PHP?

Now, someone might suggest to just change "DevBuild3" to ".3", but then users looking for that old version will see a ".3" instead of "DevBuild3".

  • 写回答

1条回答 默认 最新

  • duanbo2048 2014-11-08 23:13
    关注

    If you can split versionnumber into two columns, versionnumber and versionsuffix, you can use:

    ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(versionnumber,'.0.0.0'),'.',4)), versionsuffix
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥18 深度学习tensorflow1,ssdv1,coco数据集训练一个模型
  • ¥100 关于注册表摄像头和麦克风的问题
  • ¥30 代码本地运行正常,但是TOMCAT部署时闪退
  • ¥15 关于#python#的问题
  • ¥15 主机可以ping通路由器但是连不上网怎么办
  • ¥15 数据库一张以时间排好序的表中,找出多次相邻的那些行
  • ¥50 关于DynamoRIO处理多线程程序时候的问题
  • ¥15 kubeadm部署k8s出错
  • ¥15 Abaqus打不开cae文件怎么办?
  • ¥15 小程序准备上线,软件开发公司需要提供哪些资料给甲方