MySQL 查询 - 比较版本号

2021-09-17 00:00:00 sql versioning mysql

我的其中一个 SQL 表中有一个字段用于存储版本号，例如 '3.4.23' 或 '1.224.43'.

I have a field in one of my SQL tables which stores a version number, like '3.4.23' or '1.224.43'.

有没有办法为这个字段使用大于条件?

Is there a way to use a greater than condition for this field?

SELECT * FROM versions WHERE version_number >'2.1.27'

推荐答案

感谢@symcbean 和@gordon-linoff 的提示，我的最终查询如下所示:

Thanks for the tips @symcbean and @gordon-linoff, my final query looks like this: 

SELECT *
FROM versions WHERE CONCAT(
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 1), '.', -1), 10, '0'),
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 2), '.', -1), 10, '0'),
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(version_number, '.', 3), '.', -1), 10, '0') 
       ) > CONCAT(LPAD(2,10,'0'), LPAD(1,10,'0'), LPAD(27,10,'0'));

这允许每个组件的长度最多为 10 位.

This allows each component to be up to 10 digits long.

它改变了这个:

X.XX.XXX > 2.1.27

为此:

'000000000X00000000XX0000000XXX' > '000000000200000000010000000027'

相关文章