如何利用SQL窗口函数生成全局唯一自增序号_ROW

row_number()不能生成全局唯一自增序号，因其是查询时动态计算的窗口函数，依赖order by排序且不持久、不原子、不保证连续；真正需全局唯一序号应使用serial、auto_increment或序列等写入级机制。

如何利用sql窗口函数生成全局唯一自增序号_row_number实战

为什么 `ROW_NUMBER()` 不能直接生成“全局唯一自增序号”

ROW_NUMBER() 本身是窗口函数，必须配合 OVER 子句使用，而 OVER 必须指定排序依据（ORDER BY），否则语法报错。它生成的是“按某规则排序后的局部序号”，不是数据库级的、插入即固定的自增 ID。如果你期望像 serial 或 AUTO_INCREMENT 那样每次 INSERT 就加 1、且不重复、不跳号、不回滚重用——ROW_NUMBER() 做不到。

常见误用场景：在无稳定排序列的表上写 ROW_NUMBER() OVER ()，结果报错或被数据库拒绝（如 PostgreSQL 明确不允许空 ORDER BY）；或用 ORDER BY id 但 id 本身是 UUID 或乱序主键，导致序号“看似自增”实则不可靠。

它不持久：每次查询都重新计算，不保存到表中
它不原子：并发查询可能看到不同快照下的序号（尤其在未提交事务隔离下）
它不保证连续：若 WHERE 过滤掉中间行，序号就跳了

如何用 `ROW_NUMBER()` 模拟“全局有序编号”（仅限查询时）

适用场景：导出报表、分页展示、临时标注行序（如“第1条、第2条…”），且业务接受该序号仅反映当前查询结果的逻辑顺序。

关键点是提供一个**确定性、可复现、全集可排序**的 ORDER BY 表达式。最稳妥的是组合主键或时间戳+唯一键：

SELECT 
  ROW_NUMBER() OVER (ORDER BY created_at, id) AS seq,
  id, name, created_at
FROM users
WHERE status = 'active';

避免单用 ORDER BY id：如果 id 是 UUID 或雪花 ID，排序后序号与插入顺序无关
慎用 ORDER BY ctid（PostgreSQL）或 %%physloc%%（SQL Server）：它们依赖物理存储位置，VACUUM/REORG 后会变，不可靠
如果表无时间字段也无单调主键，可加 ORDER BY (SELECT NULL) 强制稳定（PostgreSQL 支持，但语义模糊；MySQL 8.0+ 不允许）

真正需要“全局唯一自增序号”时，该用什么

如果业务逻辑强依赖一个永不重复、严格递增、写入即定的序号（例如对账流水号、凭证编号、审计日志序），必须放弃 ROW_NUMBER()，改用底层机制：

PostgreSQL：用 serial 或 IDENTITY 列，或独立序列 NEXTVAL('seq_name') 插入时取值
MySQL：用 AUTO_INCREMENT 主键，或 INSERT ... SELECT LAST_INSERT_ID() 配合事务
SQL Server：用 IDENTITY 或 SEQUENCE 对象
通用方案：建一张专用序列表，用 UPDATE ... OUTPUT（SQL Server）或 SELECT ... FOR UPDATE + UPDATE（PostgreSQL/MySQL）做原子递增

注意：这些方案都涉及写操作和锁，性能低于纯查询的 ROW_NUMBER()，但换来的是数据一致性保障。

常见错误：把 `ROW_NUMBER()` 当成 ID 用于关联或更新

典型翻车现场：用 ROW_NUMBER() 生成的 seq 去 JOIN 另一张表，或写 UPDATE t SET rank = ROW_NUMBER()... 试图批量更新序号列——这会导致行为不可预测。

ROW_NUMBER() 在 UPDATE 中属于非确定性表达式，某些数据库（如 MySQL）会报错，PostgreSQL 允许但结果依赖执行计划，不保证每行对应关系稳定
多表 JOIN 时，ROW_NUMBER() 的 OVER 窗口范围受 JOIN 后结果集影响，同一行在不同 JOIN 条件下序号可能不同
想“固化”序号？必须显式 INSERT INTO ... SELECT ROW_NUMBER()... 写入新表，或 ALTER TABLE ADD COLUMN seq INT; UPDATE ... SET seq = ...（但需确保更新顺序可控）

真正难的不是写出 ROW_NUMBER()，而是判断此刻你到底要的是“一次性的逻辑序号”，还是“必须写入存储的业务序号”——后者永远不该交给窗口函数扛。