我有这样的值:
1.1.2
9.1
2.2
4
1.2.3.4
3.2.14
3.2.1.4.2
.....
我需要排序这些值使用mysql。此文件的数据类型为varbinary(300)。
期望的输出将是:
1.1.2
1.2.3.4
2.2
3.2.1.4.2
3.2.14
4
9.1
查询是:
select version_number from table order by version_number asc
它没有给出正确的排序顺序。
期望的输出是:
1.1.2
1.2.3.4
2.2
3.2.1.4.2
3.2.14
4
9.1
版本号最多为20位(如1.2.3.4.5.6.7.8.9.2.34)或更多。没有特别的最大尺寸,标准版本就像上面提到的那样。
尝试滥用INET_ATON
函数来进行排序,如下所示:
SELECT version_number FROM table ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version_number,'.0.0.0'),'.',4))
这个技巧最初是在mysql邮件列表上发布的,所以非常感谢原始的海报,Michael Stassen!
他是这么说的:
如果每个部分不大于255,则可以利用INET_ATON()来执行你想要的(直到第四部分)。诀窍是做每一个看起来像一个IP首先使用CONCAT添加'0.0.0',以确保每一个行至少有4个部分,然后SUBSTRING_INDEX只提取前4部分
现在,我必须指出,因为我们是在一个函数上排序列,而不是列本身,我们不能在列来帮助排序。换句话说,排序将是相对较慢。
对于后一种情况,他建议使用类似于@spanky发布的解决方案(单独列)。
我将把它存储在三个单独的列中,每个列对应版本号的一部分。
让每一列都是TINYINT,甚至在这3列之间创建一个索引。这会使事情变得简单。
那么你可以这样做:select CONCAT(v1,'.',v2,'.',v3) AS version_number FROM table ORDER BY v1 asc, v2 asc, v3 asc
如果您想支持1.1-beta
版本或使用没有INTE_ATON
的旧MySql版本,您可以通过拆分版本并将每个部分排序为整数和字符串来获得相同的排序:
SELECT
version,
REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 1), LENGTH(SUBSTRING_INDEX(version, '.', 1 - 1)) + 1), '.', '') v1,
REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 2), LENGTH(SUBSTRING_INDEX(version, '.', 2 - 1)) + 1), '.', '') v2,
REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 3), LENGTH(SUBSTRING_INDEX(version, '.', 3 - 1)) + 1), '.', '') v3,
REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 4), LENGTH(SUBSTRING_INDEX(version, '.', 4 - 1)) + 1), '.', '') v4
FROM
versions_table
ORDER BY
0+v1, v1 DESC, 0+v2, v2 DESC, 0+v3, v3 DESC, 0+v4, v4 DESC;
使用正则表达式。首先将值规范化:
SELECT REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE('v1.22.333', '^v', ''),
'(^|\.)(\d+)',
'\100000\2'
),
'0+(\d{5})(\.|$)',
'\1\2'
)
输出:00001.00022.00333
这样就可以正常排序了。
此解决方案适用于任意数量的组件。您可以将组件长度从5缩放到任意固定长度