PostgreSQL 自然排序

PostgreSQL 实现自然排序（Natural Sort）算法详解

1. 什么是自然排序？

数据库默认的字符串排序是字典序（Lexicographical Order）。

例如：

file1
file10
file2

数据库排序结果：

file1
file10
file2

原因：

字符串比较时：

"file10" < "file2"

因为字符 '1' < '2'。

但是人类期望：

file1
file2
file10

这种符合数字大小逻辑的排序称为：

Natural Sort（自然排序）

常见场景：

• 文件名排序
• 版本号排序
• 编号排序
• 商品编号排序
• 章节排序

例如：

chapter1
chapter2
chapter10

---

2. 自然排序核心思想

自然排序的核心：

将字符串拆分成数字片段和非数字片段，然后分别编码，最后按照编码后的结果排序。

例如：

原字符串：

file20abc003

拆分：

file
20
abc
003

得到 Token：

[
  "file",
  "20",
  "abc",
  "003"
]

然后转换成排序键：

[
  "0file",
  "1000000000220",
  "0abc",
  "10000000003003"
]

数据库比较这个数组，而不是比较原字符串。

---

3. PostgreSQL 实现

完整 SQL

ARRAY(
    SELECT CASE
        WHEN tokens.token[1] ~ '^\d+$'
        THEN 
            '1'
            || LPAD(length(tokens.token[1])::text, 10, '0')
            || tokens.token[1]
        ELSE
            '0' || tokens.token[1]
    END

    FROM regexp_matches(
        lower(coalesce(column_name, '')),
        '\d+|\D+',
        'g'
    )
    WITH ORDINALITY AS tokens(token, ord)

    ORDER BY tokens.ord
)

生成一个自然排序 Key：

例如：

file20abc003

结果：

{
 "0file",
 "1000000000220",
 "0abc",
 "10000000003003"
}

---

4. 字符串拆分：regexp_matches()

核心函数：

regexp_matches(
    string,
    pattern,
    flags
)

例如：

SELECT regexp_matches(
    'file20abc003',
    '\d+|\D+',
    'g'
);

结果：

{file}
{20}
{abc}
{003}

---

正则含义

\d+|\D+

拆分规则：

表达式	含义
\d	数字
\D	非数字
+	连续多个
`	`	或者

所以：

abc123xyz

拆：

abc
123
xyz

---

5. WITH ORDINALITY 保留顺序

如果直接：

FROM regexp_matches(...)

结果：

token

增加：

WITH ORDINALITY

以后：

FROM regexp_matches(...)
WITH ORDINALITY

结果：

token	ord
	1
	2
	3

ord 表示原字符串中的顺序。

---

AS tokens(token, ord)

给临时表和字段命名：

AS tokens(token, ord)

等价：

表名:

tokens


字段:

token
ord

所以：

tokens.token

访问 token。

tokens.ord

访问顺序。

---

6. 为什么 token[1]？

注意：

regexp_matches() 返回：

text[]

例如：

{abc}

这是数组。

PostgreSQL 数组下标从 1 开始：

tokens.token[1]

得到：

abc

类似 JavaScript：

array[0]

但是 PostgreSQL：

array[1]

---

7. 判断是否数字

代码：

tokens.token[1] ~ '^\d+$'

其中：

~

PostgreSQL 正则匹配操作符。

例如：

'123' ~ '^\d+$'

结果：

true

'abc' ~ '^\d+$'

结果：

false

---

正则：

^\d+$

含义：

符号	含义
^	开始
\d	数字
+	一个或多个
`	符号
----	-----
^	开始
\d	数字
+	一个或多个
结束

表示：

整个字符串必须全部是数字

---

8. 数字段编码算法

数字：

20

执行：

'1'
||
LPAD(length('20')::text,10,'0')
||
'20'

分解：

长度

length('20')

结果：

2

---

补零

LPAD('2',10,'0')

结果：

0000000002

---

拼接

1
+
0000000002
+
20

结果：

1000000000220

---

最终格式：

[类型][数字长度][数字本身]

即：

1 + 0000000002 + 20

---

9. 非数字编码

例如：

abc

执行：

'0' || 'abc'

得到：

0abc

格式：

[类型][内容]

其中：

0 = 非数字
1 = 数字

---

10. 为什么数字需要长度编码？

如果直接：

2
10
100

字符串排序：

10
100
2

错误。

增加长度：

0000000001 + 2

0000000002 + 10

0000000003 + 100

变成：

0000000001
0000000002
0000000003

字符串排序：

2
10
100

正确。

---

11. 数组比较实现自然排序

PostgreSQL 支持数组比较：

例如：

ARRAY['0file','100000000012']

和：

ARRAY['0file','1000000000210']

比较：

第一项：

0file == 0file

继续比较第二项：

100000000012
<
1000000000210

所以：

file2 < file10

---

12. 完整示例

数据：

file1
file10
file2
file20

普通排序：

file1
file10
file2
file20

自然排序：

file1
file2
file10
file20

生成 Key：

文件	排序Key
file1	["0file","100000000011"]
file2	["0file","100000000012"]
file10	["0file","1000000000210"]
file20	["0file","1000000000220"]

---

13. JavaScript / SQL 中的转义注意

如果 SQL 在 JavaScript 字符串中：

const sql = `
regexp_matches(
    column,
    '\\d+|\\D+',
    'g'
)
`;

原因：

JavaScript：

\\

转义成：

\

最终 PostgreSQL 收到：

'\d+|\D+'

如果直接在 PostgreSQL 客户端：

'\d+|\D+'

即可。

---

14. 优缺点

优点

✅ 纯 SQL 实现
✅ 不需要额外扩展
✅ 支持混合字符串：

A12B3C100

✅ 逻辑透明，可控制

---

缺点

❌ 正则解析成本较高

❌ 无法很好利用普通索引

❌ 大数据量排序性能一般

如果数据量大：

建议：

提前生成：

natural_sort_key

字段：

例如：

name	sort_key
file10	xxx
file2	xxx

查询：

ORDER BY sort_key

---

总结

PostgreSQL 自然排序算法本质：

字符串
  |
  ↓
regexp_matches 拆分
  |
  ↓
数字Token / 非数字Token
  |
  ↓
编码
  |
  ↓
数组
  |
  ↓
数组比较
  |
  ↓
自然排序

核心公式：

数字:
1 + 数字长度(补10位) + 数字

文本:
0 + 文本

通过把人类理解的数字顺序转换成数据库可以比较的字符串顺序，实现 Natural Sort。