序列与自增列

1. 序列（SEQUENCE）#

-- 创建序列
CREATE SEQUENCE order_seq START 1000 INCREMENT 1;

-- 使用序列
SELECT nextval('order_seq');   -- 获取下一个值
SELECT currval('order_seq');   -- 获取当前会话的当前值
SELECT setval('order_seq', 5000);  -- 设置值

-- 查看序列信息
SELECT * FROM information_schema.sequences WHERE sequence_name = 'order_seq';

2. SERIAL 类型#

-- SERIAL 是序列+默认值的语法糖
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    amount DECIMAL(10,2)
);

-- 等价于
CREATE SEQUENCE orders_id_seq;
CREATE TABLE orders (
    id INTEGER NOT NULL DEFAULT nextval('orders_id_seq') PRIMARY KEY,
    amount DECIMAL(10,2)
);
ALTER SEQUENCE orders_id_seq OWNED BY orders.id;

3. IDENTITY 列（SQL 标准）#

-- PostgreSQL 10+ 推荐 IDENTITY
CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

-- GENERATED ALWAYS：不允许手动插入ID
-- GENERATED BY DEFAULT：允许手动插入

CREATE TABLE products (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

-- 手动插入后重置序列
ALTER TABLE users ALTER COLUMN id RESTART WITH 1000;

4. ID 生成策略#

-- 自增ID（简单、有序）
id BIGINT GENERATED ALWAYS AS IDENTITY

-- UUID（全局唯一、无序）
id UUID DEFAULT gen_random_uuid() PRIMARY KEY

-- ULID（有序UUID）
-- 需要扩展

SERIAL 与 IDENTITY#

换行写法：使用 SERIAL 创建自增列 <列名> SERIAL [PRIMARY KEY]

-- 使用 SERIAL 创建自增主键
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

换行写法：使用 BIGSERIAL 创建自增列 <列名> BIGSERIAL [PRIMARY KEY]

-- 使用 BIGSERIAL 创建大范围自增主键
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL
);

换行写法：使用 IDENTITY 创建自增列 <列名> INT GENERATED ALWAYS AS IDENTITY [PRIMARY KEY]

-- 使用 IDENTITY 创建自增主键
CREATE TABLE products (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

换行写法：使用 BY DEFAULT IDENTITY <列名> INT GENERATED BY DEFAULT AS IDENTITY

-- 使用 BY DEFAULT 允许手动指定值
CREATE TABLE products (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

序列创建与使用#

换行写法：创建序列 CREATE SEQUENCE <序列名> [START WITH <起始值>] [INCREMENT BY <步长>]

-- 创建从 1000 开始的序列
CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;

单行写法：获取序列当前值 SELECT currval('<序列名>')

-- 获取当前会话的序列当前值
SELECT currval('order_seq');

单行写法：获取序列下一个值 SELECT nextval('<序列名>')

-- 获取并递增序列值
SELECT nextval('order_seq');

单行写法：设置序列值 SELECT setval('<序列名>', <值>)

-- 设置序列的当前值
SELECT setval('order_seq', 2000);

单行写法：设置序列值且不允许递增 SELECT setval('<序列名>', <值>, false)

-- 设置序列值且下一次调用会递增
SELECT setval('order_seq', 2000, false);

换行写法：INSERT 中使用序列 INSERT INTO <表名> (<列名>) VALUES (nextval('<序列名>'))

-- 插入时使用序列生成值
INSERT INTO orders (id, order_no) VALUES (nextval('order_seq'), 'ORD001');

序列操作函数#

单行写法：lastval 获取最后值 SELECT lastval()

-- 获取当前会话最后使用的序列值
SELECT lastval();

序列修改#

单行写法：修改序列起始值 ALTER SEQUENCE <序列名> START WITH <值>

-- 修改序列起始值
ALTER SEQUENCE order_seq START WITH 100;

单行写法：修改序列步长 ALTER SEQUENCE <序列名> INCREMENT BY <步长>

-- 修改序列步长为 2
ALTER SEQUENCE order_seq INCREMENT BY 2;

单行写法：修改序列最小值 ALTER SEQUENCE <序列名> MINVALUE <值>

-- 修改序列最小值
ALTER SEQUENCE order_seq MINVALUE 1;

单行写法：修改序列最大值 ALTER SEQUENCE <序列名> MAXVALUE <值>

-- 修改序列最大值
ALTER SEQUENCE order_seq MAXVALUE 999999;

单行写法：设置序列循环 ALTER SEQUENCE <序列名> CYCLE

-- 设置序列循环
ALTER SEQUENCE order_seq CYCLE;

单行写法：设置序列不循环 ALTER SEQUENCE <序列名> NO CYCLE

-- 设置序列不循环
ALTER SEQUENCE order_seq NO CYCLE;

单行写法：重置序列当前值 ALTER SEQUENCE <序列名> RESTART WITH <值>

-- 重置序列从 1 开始
ALTER SEQUENCE order_seq RESTART WITH 1;

单行写法：重置序列归属 ALTER SEQUENCE <序列名> OWNED BY <表名>.<列名>

-- 将序列绑定到表的列
ALTER SEQUENCE order_seq OWNED BY orders.id;

序列删除#

单行写法：删除序列 DROP SEQUENCE [IF EXISTS] <序列名>

-- 删除序列
DROP SEQUENCE IF EXISTS order_seq;

单行写法：查看序列信息 SELECT <列名> FROM information_schema.sequences WHERE <条件>

-- 查看序列的详细信息
SELECT sequence_name, start_value, increment, minimum_value, maximum_value
FROM information_schema.sequences
WHERE sequence_name = 'order_seq';

单行写法：查看序列当前值 SELECT * FROM <序列名>

-- 查看序列的参数
SELECT * FROM order_seq;