如何在MySQL中比较版本字符串("x.y.z")？

Question

我在表格里有一些固件版本的字符串，比如“4.2.2”或者“4.2.16”。

我该怎么比较、选择或者排序这些版本呢？

我不能用普通的字符串比较，因为在SQL里，“4.2.2”被认为比“4.2.16”要大。

作为版本字符串，我希望“4.2.16”比“4.2.2”要大。

我还想考虑固件版本中可能会有字母，比如“4.24a1”、“4.25b3”等等。通常情况下，带字母的部分长度是固定的。

我该怎么做呢？

Answer 1

假设你有最多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位数字，记得相应地调整。

Answer 2

如果你的版本号看起来像下面这些：

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

其中 X 是一个从 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()。所以最终我们得到的结果是：

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

这只是针对一个值的情况！ :) 对于另一个字符串，你也需要构造类似的表达式，之后就可以比较结果了。

参考资料：

• INET_ATON()

• CHAR_LENGTH()

• CONCAT()

• REPEAT()

• REPLACE()

Answer 3

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

我在把字符串存入数据库之前，先对它进行处理，使其可以排序。因为我使用的是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(' ',''))