如何比较MySQL中的版本字符串(“x.y.z”)? [英] How to compare version string ("x.y.z") in MySQL?
问题描述
我如何比较,选择或排序?
我不能使用标准字符串比较:4.2.2是SQL看到的超过4.2.16
作为版本字符串,我想要4.2.16大于4.2.2
我想认为固件版本可以在其中有字符: 4.24a1,4.25b3 ...为此,通常,带有字符的子字段具有固定长度。
如何继续?
最后,我发现了另一种方法来排序版本字符串。
以数据库的方式排序。
当我使用python Django框架时,我刚刚创建了一个VersionField,在读取时对其进行存储和解码时对其进行编码,从而对应用程序来说完全透明:
这里我的代码:
对齐函数:
def vjust(str,level = 5,delim ='。',bitsize = 6,fillchar =''):
1.12成为:12
1.1变为: 1
nb = str.count(delim)
如果nb<级别:
str + =(level-nb)* delim
返回delim.join([v.rjust(bitsize,fillchar)for v in str.split(delim)[:level + 1]] )
django VersionField:
class VersionField(models.CharField):
description ='存储版本字符串的字段(abcd)在某种程度上,它可以排序
__metaclass__ = models.SubfieldBase
def get_prep_value(self,value):
return vjust(value,fillchar ='')
def to_python(self,value):
return re.sub('\。+ $','',value.replace('',''))
I have firmware version strings into my table (like "4.2.2" or "4.2.16")
How can I compare, select or sort them ?
I cannot use standard strings comparison : "4.2.2" is a seen by SQL greater than "4.2.16"
As version strings, I would like 4.2.16 to be greater than 4.2.2
I would like to consider that firmware version can have chars in them : 4.24a1, 4.25b3 ... for this, usually, the subfield with chars has a fixed length.
how to proceed ?
Finally, I found another way to sort version strings.
I just justify the string before storing into de database in a way it is sortable. As I am using the python Django framework, I just have created a VersionField that 'encode' the version string while storing and 'decode' it while reading, so that it is totally transparent for the application :
Here my code :
The justify function :
def vjust(str,level=5,delim='.',bitsize=6,fillchar=' '):
"""
1.12 becomes : 1. 12
1.1 becomes : 1. 1
"""
nb = str.count(delim)
if nb < level:
str += (level-nb) * delim
return delim.join([ v.rjust(bitsize,fillchar) for v in str.split(delim)[:level+1] ])
The django VersionField :
class VersionField(models.CharField) :
description = 'Field to store version strings ("a.b.c.d") in a way it is sortable'
__metaclass__ = models.SubfieldBase
def get_prep_value(self, value):
return vjust(value,fillchar=' ')
def to_python(self, value):
return re.sub('\.+$','',value.replace(' ',''))
这篇关于如何比较MySQL中的版本字符串(“x.y.z”)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!