PostgreSQL教程FG028-PG序列与自增字段：实现与定制化

目录大纲

Part01-基础概念与理论知识
- 1.1 序列的概念与特点
- 1.2 自增字段的实现方式
Part02-生产环境规划与建议
- 2.1 序列的使用场景与注意事项
- 2.2 自增字段的设计原则
Part03-生产环境项目实施方案
- 3.1 序列的创建与管理
- 3.2 自增字段的实现与配置
Part04-生产案例与实战讲解
- 4.1 序列实战案例
- 4.2 自增字段实战案例
Part05-风哥经验总结与分享
- 5.1 序列与自增字段的性能优化
- 5.2 常见问题与解决方案

Part01-基础概念与理论知识

1.1 序列的概念与特点

序列是PostgreSQL中一种特殊的数据库对象，用于生成唯一的数字序列。序列的主要特点包括：

自动生成：可以自动生成递增或递减的数字
唯一性：确保生成的数字不重复
可定制性：可以设置起始值、步长、最大值等参数
独立性：序列是独立的数据库对象，可以被多个表使用

学习交流加群风哥微信: itpux-com

1.2 自增字段的实现方式

在PostgreSQL中，自增字段主要通过以下方式实现：

SERIAL类型：PostgreSQL提供的自动递增类型
BIGSERIAL类型：用于更大范围的自增字段
SMALLSERIAL类型：用于较小范围的自增字段
自定义序列：通过CREATE SEQUENCE语句创建

这些实现方式本质上都是通过序列来实现自增功能的。

更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 序列的使用场景与注意事项

序列适合以下场景：

主键生成：为表的主键字段生成唯一值
订单号生成：生成唯一的订单编号
序列号生成：生成其他需要唯一标识的序列号

注意事项：

序列值会被缓存，可能会出现跳跃
序列值不会自动回滚，即使事务回滚
多个表共享序列时需要注意冲突
序列可能会耗尽，需要监控

风哥提示：序列是生成唯一标识符的有效方式，但需要合理规划其使用方式和范围。

2.2 自增字段的设计原则

自增字段设计原则：

选择合适的类型：根据数据量选择SERIAL、BIGSERIAL或SMALLSERIAL
合理设置起始值：可以设置一个较大的起始值，避免与现有数据冲突
考虑步长：默认步长为1，特殊场景可以调整
设置合适的缓存大小：平衡性能和序列连续性

自增字段适合以下场景：

表的主键：确保每条记录有唯一标识
需要唯一标识的业务字段：如订单号、用户ID等
需要按顺序排列的字段：如序号、排名等

更多学习教程公众号风哥教程itpux_com

Part03-生产环境项目实施方案

3.1 序列的创建与管理

3.1.1 创建基本序列

— 创建基本序列
CREATE SEQUENCE fgedu_user_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

— 查看序列信息
SELECT * FROM pg_sequences WHERE sequencename = ‘fgedu_user_id_seq’;

— 使用序列
SELECT nextval(‘fgedu_user_id_seq’);
SELECT currval(‘fgedu_user_id_seq’);
SELECT lastval();

3.1.2 创建自定义序列

— 创建带有特殊参数的序列
CREATE SEQUENCE fgedu_order_id_seq
START WITH 10000
INCREMENT BY 1
MAXVALUE 999999999
MINVALUE 10000
CACHE 10
CYCLE;

— 查看序列当前值
SELECT currval(‘fgedu_order_id_seq’);

— 重置序列值
SELECT setval(‘fgedu_order_id_seq’, 20000);

— 查看重置后的序列值
SELECT nextval(‘fgedu_order_id_seq’);

from PostgreSQL视频:www.itpux.com

3.1.3 序列的管理

— 修改序列
ALTER SEQUENCE fgedu_user_id_seq
INCREMENT BY 2
MAXVALUE 1000000
CACHE 5;

— 重命名序列
ALTER SEQUENCE fgedu_user_id_seq RENAME TO fgedu_user_sequence;

— 删除序列
DROP SEQUENCE IF EXISTS fgedu_order_id_seq;

— 查看所有序列
SELECT * FROM pg_sequences;

3.2 自增字段的实现与配置

3.2.1 使用SERIAL类型

— 使用SERIAL类型创建自增字段
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE
);

— 插入数据（不需要指定id字段）
INSERT INTO fgedu_users (name, email) VALUES
(‘张三’, ‘zhangsan@fgedu.net.cn’),
(‘李四’, ‘lisi@fgedu.net.cn’),
(‘王五’, ‘wangwu@fgedu.net.cn’);

— 查询数据
SELECT * FROM fgedu_users;

3.2.2 使用BIGSERIAL和SMALLSERIAL

— 使用BIGSERIAL类型（适合大数据量）
CREATE TABLE fgedu_orders (
id BIGSERIAL PRIMARY KEY,
order_date DATE NOT NULL,
total_amount DECIMAL(10, 2)
);

— 使用SMALLSERIAL类型（适合小数据量）
CREATE TABLE fgedu_categories (
id SMALLSERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL
);

— 插入数据
INSERT INTO fgedu_orders (order_date, total_amount) VALUES
(‘2026-04-01’, 1000.00),
(‘2026-04-02’, 2000.00);

INSERT INTO fgedu_categories (name) VALUES
(‘电子产品’),
(‘服装’),
(‘食品’);

— 查询数据
SELECT * FROM fgedu_orders;
SELECT * FROM fgedu_categories;

学习交流加群风哥QQ113257174

3.2.3 自定义序列作为自增字段

— 创建自定义序列
CREATE SEQUENCE fgedu_product_id_seq
START WITH 1000
INCREMENT BY 1
NO MAXVALUE
CACHE 1;

— 创建表并使用自定义序列
CREATE TABLE fgedu_products (
id INTEGER PRIMARY KEY DEFAULT nextval(‘fgedu_product_id_seq’),
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2)
);

— 关联序列与表
ALTER SEQUENCE fgedu_product_id_seq OWNED BY fgedu_products.id;

— 插入数据
INSERT INTO fgedu_products (name, price) VALUES
(‘手机’, 5999.00),
(‘电脑’, 9999.00),
(‘平板’, 3999.00);

— 查询数据
SELECT * FROM fgedu_products;

Part04-生产案例与实战讲解

4.1 序列实战案例

4.1.1 订单号生成

— 创建订单号序列（带有前缀）
CREATE SEQUENCE fgedu_order_no_seq
START WITH 100000
INCREMENT BY 1
MAXVALUE 999999
CACHE 10;

— 创建订单表
CREATE TABLE fgedu_orders_full (
id BIGSERIAL PRIMARY KEY,
order_no VARCHAR(20) UNIQUE,
customer_id INTEGER,
order_date TIMESTAMP NOT NULL,
total_amount DECIMAL(10, 2)
);

— 创建生成订单号的函数
CREATE OR REPLACE FUNCTION fgedu_generate_order_no()
RETURNS VARCHAR AS $$
DECLARE
order_seq INTEGER;
order_no VARCHAR;
BEGIN
SELECT nextval(‘fgedu_order_no_seq’) INTO order_seq;
order_no := ‘ORD’ || TO_CHAR(CURRENT_DATE, ‘YYYYMMDD’) || LPAD(order_seq::TEXT, 6, ‘0’);
RETURN order_no;
END;
$$ LANGUAGE plpgsql;

— 插入订单数据
INSERT INTO fgedu_orders_full (order_no, customer_id, order_date, total_amount) VALUES
(fgedu_generate_order_no(), 1, NOW(), 1000.00),
(fgedu_generate_order_no(), 2, NOW(), 2000.00),
(fgedu_generate_order_no(), 1, NOW(), 1500.00);

— 查询订单数据
SELECT * FROM fgedu_orders_full;

4.1.2 多表共享序列

— 创建共享序列
CREATE SEQUENCE fgedu_reference_seq
START WITH 1000
INCREMENT BY 1
NO MAXVALUE
CACHE 5;

— 创建第一个表
CREATE TABLE fgedu_invoices (
id INTEGER PRIMARY KEY DEFAULT nextval(‘fgedu_reference_seq’),
invoice_date DATE NOT NULL,
amount DECIMAL(10, 2)
);

— 创建第二个表
CREATE TABLE fgedu_payments (
id INTEGER PRIMARY KEY DEFAULT nextval(‘fgedu_reference_seq’),
payment_date DATE NOT NULL,
amount DECIMAL(10, 2)
);

— 插入数据
INSERT INTO fgedu_invoices (invoice_date, amount) VALUES
(‘2026-04-01’, 1000.00),
(‘2026-04-02’, 2000.00);

INSERT INTO fgedu_payments (payment_date, amount) VALUES
(‘2026-04-01’, 1000.00),
(‘2026-04-03’, 1500.00);

— 查询数据
SELECT * FROM fgedu_invoices;
SELECT * FROM fgedu_payments;

风哥提示：多表共享序列可以确保所有表的ID值唯一，但需要注意序列的管理和监控。

4.2 自增字段实战案例

4.2.1 复合主键与自增字段

— 创建表，使用自增字段作为复合主键的一部分
CREATE TABLE fgedu_order_items (
order_id BIGSERIAL,
item_id SERIAL,
product_id INTEGER,
quantity INTEGER,
unit_price DECIMAL(10, 2),
PRIMARY KEY (order_id, item_id)
);

— 插入数据
INSERT INTO fgedu_order_items (product_id, quantity, unit_price) VALUES
(1, 2, 100.00),
(2, 1, 200.00),
(1, 3, 100.00);

— 查询数据
SELECT * FROM fgedu_order_items;

4.2.2 自增字段与业务逻辑

— 创建用户表
CREATE TABLE fgedu_customers (
id SERIAL PRIMARY KEY,
customer_no VARCHAR(10) UNIQUE,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE
);

— 创建生成客户编号的触发器
CREATE OR REPLACE FUNCTION fgedu_generate_customer_no()
RETURNS TRIGGER AS $$
BEGIN
NEW.customer_no := ‘C’ || LPAD(NEW.id::TEXT, 6, ‘0’);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fgedu_customer_no_trigger
BEFORE INSERT ON fgedu_customers
FOR EACH ROW
EXECUTE FUNCTION fgedu_generate_customer_no();

— 插入客户数据
INSERT INTO fgedu_customers (name, email) VALUES
(‘张三’, ‘zhangsan@fgedu.net.cn’),
(‘李四’, ‘lisi@fgedu.net.cn’),
(‘王五’, ‘wangwu@fgedu.net.cn’);

— 查询客户数据
SELECT * FROM fgedu_customers;