如何比较MySQL中的版本字符串（x.y.z）？

Question

我的表中有固件版本字符串（如“4.2.2”或“4.2.16”）

如何比较、选择或排序？

我不能使用标准字符串比较：“4.2.2”是SQL看到的大于“4.2.16”的字符串

作为版本字符串，我希望4.2.16大于4.2.2

我想考虑固件版本可以有字符：4.24a1，4.25b3。。。为此，通常，带字符的子字段具有固定的长度。

如何进行？

Answer 1

最后，我找到了另一种排序版本字符串的方法。

在以可排序的方式存储到de database之前，我只是调整字符串的大小。 当我使用python Django框架时，我刚刚创建了一个VersionField，它在存储版本字符串时对其进行“编码”，在读取时对其进行“解码”，因此它对应用程序是完全透明的：

这里是我的代码：

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(' ',''))

Answer 2

如果您的所有版本号都类似于以下任何一个：

X
X.X
X.X.X
X.X.X.X

其中X是0到255之间的整数（包括0到255），然后可以使用INET_ATON()函数将字符串转换为适合比较的整数。

不过，在应用该函数之前，您需要确保函数的参数是X.X.X.X形式的，方法是向其追加必要数量的'.0'。要做到这一点，首先需要找出字符串已经包含了多少个.，可以这样做：

CHAR_LENGTH(ver) - CHAR_LENGTH(REPLACE(ver, '.', '')

也就是说，字符串中的句点数量是字符串的长度减去去掉句点后的长度。

然后从3中减去得到的结果，并与'.0'一起传递给REPEAT()函数：

REPEAT('.0', 3 - CHAR_LENGTH(ver) + CHAR_LENGTH(REPLACE(ver, '.', ''))

这将为我们提供必须附加到原始ver值的子字符串，以符合X.X.X.X格式。因此，它将依次与ver一起传递给CONCAT()函数。这个结果现在可以直接传递给CONCAT()。我们最终得到的是：

INET_ATON(
  CONCAT(
    ver,
    REPEAT(
      '.0',
      3 - CHAR_LENGTH(ver) + CHAR_LENGTH(REPLACE(ver, '.', ''))
    )
  )
)

这只值一个值！：）应该为另一个字符串构造一个类似的表达式，然后可以比较结果。

参考文献：

• ^{}

• ^{}

• ^{}

• ^{}

• ^{}

Answer 3

假设组数为3或更少，则可以将版本号视为两个十进制数，并对其进行相应排序。以下是方法：

SELECT 
ver,
CAST(
    SUBSTRING_INDEX(ver, '.', 2)
    AS DECIMAL(6,3)
) AS ver1, -- ver1 = the string before 2nd dot
CAST(
    CASE
        WHEN LOCATE('.', ver) = 0 THEN NULL
        WHEN LOCATE('.', ver, LOCATE('.', ver)+1) = 0 THEN SUBSTRING_INDEX(ver, '.', -1)
        ELSE SUBSTRING_INDEX(ver, '.', -2)
    END
    AS DECIMAL(6,3)
) AS ver2  -- ver2 = if there is no dot then 0.0
           --        else if there is no 2nd dot then the string after 1st dot
           --        else the string after 1st dot
FROM
(
SELECT '1' AS ver UNION
SELECT '1.1' UNION
SELECT '1.01' UNION
SELECT '1.01.03' UNION
SELECT '1.01.04' UNION
SELECT '1.01.1' UNION
SELECT '1.11' UNION
SELECT '1.2' UNION
SELECT '1.2.0' UNION
SELECT '1.2.1' UNION
SELECT '1.2.11' UNION
SELECT '1.2.2' UNION
SELECT '2.0' UNION
SELECT '2.0.1' UNION
SELECT '11.1.1' 
) AS sample
ORDER BY ver1, ver2

输出：

ver     ver1    ver2
======= ======  ======
1        1.000  (NULL)
1.01     1.010   1.000
1.01.03  1.010   1.030
1.01.04  1.010   1.040
1.01.1   1.010   1.100
1.1      1.100   1.000
1.11     1.110  11.000
1.2.0    1.200   2.000
1.2      1.200   2.000
1.2.1    1.200   2.100
1.2.11   1.200   2.110
1.2.2    1.200   2.200
2.0      2.000   0.000
2.0.1    2.000   0.100
11.1.1  11.100   1.100

注：

1. 您可以将此示例扩展为最多4个组或更多，但字符串函数将变得越来越复杂。
2. 数据类型转换DECIMAL(6,3)用于说明。如果期望次要版本号中的数字超过3位，请相应地进行修改。