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

我有固件版本字符串到我的表(如"4.2.2"或"4.2.16")

如何比较、选择或排序它们?

我不能使用标准字符串比较:"4.2.2"是一个SQL大于"4.2.16"

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

我想考虑固件版本可以有字符:4.24a1, 4.25b3…对于这种情况，通常，包含字符的子字段具有固定长度。

如何继续

如果你所有的版本号看起来像以下任何一个:

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

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

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

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

假设组的数量为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

指出:

你可以扩展这个例子最多4组或更多，但字符串函数会变得越来越复杂。
数据类型转换DECIMAL(6,3)用于说明。如果您希望次要版本号超过3位数字，请相应地修改。

我只使用以下适用于255以下所有版本号:

比较的例子:

SELECT * FROM versions
WHERE INET_ATON(SUBSTRING_INDEX(CONCAT(version, '.0.0.0'), '.', 4)) > INET_ATON(SUBSTRING_INDEX(CONCAT('2.1.27', '.0.0.0'), '.', 4));

命令示例:

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

也许你可以使用INET6_ATON覆盖版本，十六进制字符(a-f)?

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

在以可排序的方式存储到数据库之前，我只是对字符串进行了校验。当我使用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(' ',''))

这是一个相当复杂的问题，因为SQL不是设计为从单个字段中分离出多个值的——这违反了第一范式。假设你不会有超过三组数字，每组长度不超过三位数，试试:

cast(substring_index(concat(X,'.0.0.'), '.', 1) as float) * 1000000 +
cast(substring_index(substring_index(concat(X,'.0.0.'), '.', 2), '.', -1) as float) * 1000 +
cast(substring_index(substring_index(concat(X,'.0.0.'), '.', 3), '.', -1) as float)

Python可以按照您希望的方式逐个元素比较列表，因此您可以简单地在"."上分割，对每个元素调用int(x)(使用列表推导式)将字符串转换为int，然后比较

    >>> v1_3 = [ int(x) for x in "1.3".split(".") ]
    >>> v1_2 = [ int(x) for x in "1.2".split(".") ]
    >>> v1_12 = [ int(x) for x in "1.12".split(".") ]
    >>> v1_3_0 = [ int(x) for x in "1.3.0".split(".") ]
    >>> v1_3_1 = [ int(x) for x in "1.3.1".split(".") ]
    >>> v1_3
    [1, 3]
    >>> v1_2
    [1, 2]
    >>> v1_12
    [1, 12]
    >>> v1_3_0
    [1, 3, 0]
    >>> v1_3_1
    [1, 3, 1]
    >>> v1_2 < v1_3
    True
    >>> v1_12 > v1_3
    True
    >>> v1_12 > v1_3_0
    True
    >>> v1_12 > v1_3_1
    True
    >>> v1_3_1 < v1_3
    False
    >>> v1_3_1 < v1_3_0
    False
    >>> v1_3_1 > v1_3_0
    True
    >>> v1_3_1 > v1_12
    False
    >>> v1_3_1 < v1_12
    True
    >>>

我正在寻找同样的事情，而不是最终这样做-但留在mysql:

安装这个udf库到mysql，因为我想要PCRE的功能。

使用这个语句

case when version is null then null
when '' then 0
else
preg_replace( '/[^.]*([^.]{10})[.]+/', '$1', 
    preg_replace('/([^".,\/_ ()-]+)([".,\/_ ()-]*)/','000000000$1.',
        preg_replace('/(?<=[0-9])([^".,\/_ ()0-9-]+)/','.!$1',version
))) 
end

我来解释一下这是什么意思:

preg_replace是UDF库创建的函数。因为它是UDF你可以从任意用户或dbspace调用它，就像
^".,\/_ ()现在我正在考虑所有这些字符作为分隔符或传统的"点"在一个版本
preg_replace('/(?<=[0-9])([^".,\/_ ()0-9-]+)/','.!$1',version)表示将前面有数字的所有非"点"和非数字替换为前面有"点"和感叹号。
preg_replace('/([^".,\/_ ()-]+)([".,\/_ ()-]*)/','000000000$1.', ...)意味着将所有的"点"替换为实际的点，并用9个零填充所有的数字。此外，任何相邻的点将减少为1。
preg_replace( '/0*([^.]{10})[.]+/', '$1', ... )意味着额外地将所有数字块剥离到只有10位数字长，并根据需要保留尽可能多的块。我想强制6个块将其保持在64字节以下，但需要7个块是非常常见的，因此对于我的准确性是必要的。还需要10块，所以7块9块不是选项。但是可变长度对我来说很有效。——记住字符串是从左到右比较的

现在我可以处理如下版本:

1.2 < 1.10
1.2b < 1.2.0
1.2a < 1.2b
1.2 = 1.2.0
1.020 = 1.20
11.1.1.3.0.100806.0408.000  < 11.1.1.3.0.100806.0408.001
5.03.2600.2180 (xpsp_sp2_rtm.040803-2158)
A.B.C.D = a.B.C.D
A.A  <  A.B

我选择了感叹号，因为它在排序序列(我正在使用)0之前进行排序。它的相对排序到0允许像b和a这样的字母，当与上面的数字相邻时，被视为一个新的部分，并在0之前排序——这是我使用的填充。

我使用0作为填充，这样供应商的错误，如从一个固定的3位数块移动到一个可变的不会咬我。

如果你想处理像"2.11.0开发中(不稳定)(2010-03-09)"这样愚蠢的版本，你可以很容易地选择更多的填充。——字符串development是11字节。

你可以很容易地请求更多的块在最后的替换。

我本可以做得更多，但由于我有数百万条记录需要定期扫描，所以我试图在高精确度的情况下尽可能少地做几步。如果有人看到一个优化，请回复。

我选择将其保留为字符串而不强制转换为数字，因为强制转换有代价，而且正如我们所看到的，字母很重要。我正在考虑的一件事是对字符串进行测试并返回一个选项，该选项不是那么多的传递或更便宜的函数，用于更整洁的情况。像11.1.1.3是一个非常常见的格式

这里有很多好的解决方案，但我想要一个存储函数，将与ORDER BY

工作

CREATE FUNCTION standardize_version(version VARCHAR(255)) RETURNS varchar(255) CHARSET latin1 DETERMINISTIC NO SQL
BEGIN
  DECLARE tail VARCHAR(255) DEFAULT version;
  DECLARE head, ret VARCHAR(255) DEFAULT NULL;
  WHILE tail IS NOT NULL DO 
    SET head = SUBSTRING_INDEX(tail, '.', 1);
    SET tail = NULLIF(SUBSTRING(tail, LOCATE('.', tail) + 1), tail);
    SET ret = CONCAT_WS('.', ret, CONCAT(REPEAT('0', 3 - LENGTH(CAST(head AS UNSIGNED))), head));
  END WHILE;
  RETURN ret;
END|

test:

SELECT standardize_version(version) FROM (SELECT '1.2.33.444.5b' AS version UNION SELECT '1' UNION SELECT NULL) AS t;

呈现:

00001.00002.00033.00444.00005b
00001
(null)

和允许比较几乎任何一组版本，甚至是有字母的版本。

/**
function version_compare(version1, version2)
parameters
version1 first version number.
version2 second version number.
return values
-1: if version1 is less than version2;
1: if version1 is greater than version2,
0: if version1 equal version2.
example:
select version_compare('4.2.2','4.2.16') from dual;
version_compare('4.2.2','4.2.16')  
-----------------------------------
    -1 
*/
drop function if exists version_compare;
delimiter @@
create function version_compare(version1 varchar(100), version2 varchar(100))
  returns tinyint
  begin
    declare v_result tinyint;
    declare version1_sub_string varchar(100);
    declare version2_sub_string varchar(100);
    declare version1_sub_int int;
    declare version2_sub_int int;
    declare version1_sub_end tinyint;
    declare version2_sub_end tinyint;

    if version1 = version2 then
      set v_result = 0;
    else
      set version1_sub_string = version1;
      set version2_sub_string = version2;
      lp1 : loop
        set version1_sub_end = locate('.', version1_sub_string);
        set version2_sub_end = locate('.', version2_sub_string);
        if version1_sub_end <> 0 then
          set version1_sub_int = cast(substring(version1_sub_string, 1, version1_sub_end - 1) as signed);
          set version1_sub_string = substring(version1_sub_string, version1_sub_end +1 );
        else
          set version1_sub_int = cast(version1_sub_string as signed);
        end if;
        if version2_sub_end <> 0 then
          set version2_sub_int = cast(substring(version2_sub_string, 1, version2_sub_end - 1) as signed);
          set version2_sub_string = substring(version2_sub_string, version2_sub_end + 1);
        else
          set version2_sub_int = cast(version2_sub_string as signed);
        end if;

        if version1_sub_int > version2_sub_int then
          set v_result = 1;
          leave lp1;
        elseif version1_sub_int < version2_sub_int then
            set v_result = -1;
            leave lp1;
        else
          if version1_sub_end = 0 and version2_sub_end = 0 then
            set v_result = 0;
            leave lp1;
          elseif version1_sub_end = 0 then
              set v_result = -1;
              leave lp1;
          elseif version2_sub_end = 0 then
              set v_result = 1;
              leave lp1;
          end if;      
        end if;
      end loop;
    end if;
    return v_result;
 end@@
delimiter ;

这是我的解决方案。它不取决于subversion的数量。

例如:

select SF_OS_VERSION_COMPARE('2016.10.1712.58','2016.9.1712.58');

返回"高"

select SF_OS_VERSION_COMPARE('2016.10.1712.58','2016.10.1712.58');

返回"平等"

delimiter //
DROP FUNCTION IF EXISTS SF_OS_VERSION_COMPARE //
CREATE FUNCTION SF_OS_VERSION_COMPARE(ver_1 VARCHAR(50), ver_2 VARCHAR(50)) RETURNS VARCHAR(5)
    DETERMINISTIC
    COMMENT 'Return "HIGH", "LOW" OR "EQUAL" comparing VER_1 with VER_2'
BEGIN
    DECLARE v_ver1 VARCHAR(50);
    DECLARE v_ver2 VARCHAR(50);
    DECLARE v_ver1_num INT;
    DECLARE v_ver2_num INT;
    SET v_ver1 = ver_1;
    SET v_ver2 = ver_2;
    WHILE ( v_ver1 <> v_ver2 AND ( v_ver1 IS NOT NULL OR v_ver2 IS NOT NULL )) DO
    SET v_ver1_num = CAST(SUBSTRING_INDEX(v_ver1, '.', 1) AS UNSIGNED INTEGER);
    SET v_ver2_num = CAST(SUBSTRING_INDEX(v_ver2, '.', 1) AS UNSIGNED INTEGER);
    IF ( v_ver1_num > v_ver2_num )
    THEN
        return 'HIGH';
    ELSEIF ( v_ver1_num < v_ver2_num )
    THEN
        RETURN 'LOW';
    ELSE
        SET v_ver1 = SUBSTRING(v_ver1,LOCATE('.', v_ver1)+1);
        SET v_ver2 = SUBSTRING(v_ver2,LOCATE('.', v_ver2)+1);
    END IF;
    END WHILE;
    RETURN 'EQUAL';
END //

我根据上面Salman a的出色回答创建了一个灵活的sql解决方案:

在这个逻辑中，我比较了前4个版本段。当版本字符串有更多段时，后面的部分将被忽略。

代码从表中获取id和ver列，然后进行"清理"。ver值总是包含3个点-这个经过处理的版本由sane_ver字段返回。

然后将经过处理的版本拆分为4个整数值，每个整数值代表一个版本段。您可以根据这4个整数对结果进行比较或排序。

SELECT
    id,
    ver,
    SUBSTRING_INDEX(sane_ver, '.', 1) + 0 AS ver1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sane_ver, '.', 2), '.', -1) + 0 AS ver2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sane_ver, '.', 3), '.', -1) + 0 AS ver3,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sane_ver, '.', 4), '.', -1) + 0 AS ver4
FROM (
    SELECT
        id,
        ver,
        CONCAT(
            ver,
            REPEAT('.0', 3 - CHAR_LENGTH(ver) + CHAR_LENGTH(REPLACE(ver, '.', '')))
        ) AS sane_ver
    FROM (
        SELECT id, ver FROM some_table
    ) AS raw_data 
) AS sane_data

样本

下面是一个完整的查询，包含一些示例数据和一个过滤器，仅返回低于1.2.3.4的版本

SELECT
    id,
    ver,
    SUBSTRING_INDEX(sane_ver, '.', 1) + 0 AS ver1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sane_ver, '.', 2), '.', -1) + 0 AS ver2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sane_ver, '.', 3), '.', -1) + 0 AS ver3,
    SUBSTRING_INDEX(SUBSTRING_INDEX(sane_ver, '.', 4), '.', -1) + 0 AS ver4
FROM (
    SELECT
        id,
        ver,
        CONCAT(
            ver,
            REPEAT('.0', 3 - CHAR_LENGTH(ver) + CHAR_LENGTH(REPLACE(ver, '.', '')))
        ) AS sane_ver
    FROM (
        SELECT 1 AS id, '1' AS ver UNION
        SELECT 2,  '1.1' UNION
        SELECT 3,  '1.2.3.4.5' UNION
        SELECT 4,  '1.01' UNION
        SELECT 5,  '1.01.03' UNION
        SELECT 6,  '1.01.04a' UNION
        SELECT 7,  '1.01.04' UNION
        SELECT 8,  '1.01.04b' UNION
        SELECT 9,  '1.01.1.9.2.1.0' UNION
        SELECT 10, '1.11' UNION
        SELECT 11, '1.2' UNION
        SELECT 12, '1.2.0' UNION
        SELECT 13, '1.2.1' UNION
        SELECT 14, '1.2.11' UNION
        SELECT 15, '1.2.2' UNION
        SELECT 16, '2.0' UNION
        SELECT 17, '2.0.1' UNION
        SELECT 18, '11.1.1' UNION
        SELECT 19, '2020.11.18.11'
    ) AS raw_data 
) AS sane_data
HAVING 
    ver1 <= 1
    AND (ver2 <= 2 OR ver1 < 1) 
    AND (ver3 <= 3 OR ver2 < 2 OR ver1 < 1) 
    AND (ver4 <  4 OR ver3 < 3 OR ver2 < 2 OR ver1 < 1)

指出

请注意这个逻辑与Salman A的原始代码有何不同:

原始答案使用CAST AS DECIMAL()，将1.02转换为1.020，将1.1.0转换为1.100
→比较1.02.0低于1.1.0(这是错误的，在我的理解)

答案中的代码将1.02转换为整数1, 2，将1.1转换为整数1, 1
→1.1.0比1.02.0

而且，我们的两个解决方案都完全忽略任何非数字字符，将1.2-alpha视为等于1.2.0。

相关内容

最新更新

热门标签：