如何在SQL中直接按semver规则正确排序语义化版本？

核心排序逻辑 #

语义化版本的排序优先级遵循以下规则：

• 优先级从高到低依次为：主版本号 > 次版本号 > 修订号
• 带有预发布后缀的版本优先级低于相同主/次/修订号的正式版本
• 预发布后缀按.分割为多个段逐位比较：数字段按数值大小比较，非数字段按ASCII字典序比较，段数更短的版本在前缀完全匹配的情况下优先级更低

不同数据库实现示例 #

MySQL 实现 #

通过字符串拆分函数把版本的核心字段、预发布字段拆分开排序，示例代码如下：

SELECT version
FROM your_table
ORDER BY
  -- 先按主、次、修订号数值排序
  CAST(SUBSTRING_INDEX(version, '.', 1) AS UNSIGNED) ASC,
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '.', 2), '.', -1) AS UNSIGNED) ASC,
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '-', 1), '.', -1) AS UNSIGNED) ASC,
  -- 正式版排在预发布版前面
  CASE WHEN version LIKE '%-%' THEN 1 ELSE 0 END ASC,
  -- 预发布版本按段逐位排序，可根据实际需求扩展支持的段数
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '-', -1), '.', 1), '+', 1) ASC,
  IF(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '-', -1), '.', 2), '.', -1) REGEXP '^[0-9]+$',
     CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '-', -1), '.', 2), '.', -1) AS UNSIGNED),
     SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '-', -1), '.', 2), '.', -1)
  ) ASC,
  IF(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '-', -1), '.', 3), '.', -1) REGEXP '^[0-9]+$',
     CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '-', -1), '.', 3), '.', -1) AS UNSIGNED),
     SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '-', -1), '.', 3), '.', -1)
  ) ASC,
  IF(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '-', -1), '.', 4), '.', -1) REGEXP '^[0-9]+$',
     CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '-', -1), '.', 4), '.', -1) AS UNSIGNED),
     SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(version, '-', -1), '.', 4), '.', -1)
  ) ASC;

对问题给出的示例版本执行上述排序后，输出顺序为：

1.0.0-0.3.7, 1.0.0-alpha, 1.0.0-alpha.1, 1.0.0-x.7.z.92, 1.0.0-x-y-z

PostgreSQL 实现 #

PostgreSQL 内置string_to_array函数，实现更简洁：

SELECT version
FROM your_table
ORDER BY
  -- 主、次、修订号排序
  (string_to_array(split_part(version, '-', 1), '.'))::int[] ASC,
  -- 正式版优先
  version NOT LIKE '%-%' DESC,
  -- 预发布版本排序，自动处理数字和字符串比较
  string_to_array(split_part(version, '-', 2), '.') ASC;

内容的提问来源于stack exchange，提问作者kotsky