PostgreSQL：对含数字及范围的字符串列表进行排序

核心思路 #

针对dimension字段的混合格式（纯数字、数字范围、带单位数字、纯字符串），提取每个值的起始数字部分作为排序依据，非数字开头的字符串默认排到末尾，同时保证相同起始数字的条目按原字符串逻辑排序（如纯数字10优先于范围10-14）。

直接SQL排序方案（适用于小数据量） #

MySQL版本 #

SELECT *
FROM inventory i
JOIN item_definition def ON i.def_id = def.id
ORDER BY
  -- 提取开头的数字（含小数）并转为数值，非数字开头返回NULL（默认排最后）
  CAST(REGEXP_SUBSTR(def.dimension, '^[0-9]+(\\.[0-9]+)?') AS DECIMAL) ASC,
  -- 起始数字相同时，按原字符串字典序排序
  def.dimension ASC;

PostgreSQL版本 #

SELECT *
FROM inventory i
JOIN item_definition def ON i.def_id = def.id
ORDER BY
  -- 提取开头数字并转数值，明确指定NULL排最后
  CAST(SUBSTRING(def.dimension FROM '^[0-9]+(\.[0-9]+)?') AS NUMERIC) ASC NULLS LAST,
  def.dimension ASC;

高性能优化方案（适用于大数据量） #

如果数据量较大，实时正则提取和类型转换会影响查询效率，建议添加生成列并创建索引：

MySQL示例 #

1. 添加存储生成列：

ALTER TABLE item_definition 
ADD COLUMN dimension_start DECIMAL 
GENERATED ALWAYS AS (CAST(REGEXP_SUBSTR(dimension, '^[0-9]+(\\.[0-9]+)?') AS DECIMAL)) 
STORED;

2. 创建复合索引：

CREATE INDEX idx_dimension_sort ON item_definition(dimension_start, dimension);

3. 使用生成列排序：

SELECT *
FROM inventory i
JOIN item_definition def ON i.def_id = def.id
ORDER BY def.dimension_start ASC, def.dimension ASC;

说明 #

• 正则表达式^[0-9]+(\.[0-9]+)?会匹配字符串开头的整数或小数，可覆盖8、10-14、93.5in这类格式；
• 纯字符串（如ABC）因无法提取数字，会被转为NULL，默认排在所有数值型条目之后；
• 若需支持负数尺寸，可将正则调整为^-?[0-9]+(\.[0-9]+)?。

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