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

假设组数为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位，请相应地进行修改。