序列 #

序列类型
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   序列:                                                     │
│   ├── 生成唯一数字序列                                     │
│   ├── 通常用于自增主键                                     │
│   └── 支持并发安全获取                                     │
│                                                             │
│   CockroachDB 替代方案:                                     │
│   ├── UUID: gen_random_uuid()                              │
│   ├── unique_rowid(): 分布式唯一ID                         │
│   └── 序列: 兼容PostgreSQL                                 │
│                                                             │
│   注意事项:                                                 │
│   ├── 序列可能导致热点                                     │
│   ├── 推荐使用UUID                                         │
│   └── 序列主要用于兼容性                                   │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 创建简单序列
CREATE SEQUENCE user_seq;

-- 创建带参数的序列
CREATE SEQUENCE order_seq
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 999999
    NO CYCLE;

-- 创建序列选项
CREATE SEQUENCE my_seq
    START WITH 1        -- 起始值
    INCREMENT BY 1      -- 增量
    MINVALUE 1          -- 最小值
    MAXVALUE 1000000    -- 最大值
    CYCLE;              -- 达到最大值后循环

-- 指定数据类型
CREATE SEQUENCE small_seq AS SMALLINT;
CREATE SEQUENCE int_seq AS INT;
CREATE SEQUENCE big_seq AS BIGINT;

-- 获取下一个值
SELECT nextval('user_seq');

-- 获取当前值 (需要先调用 nextval)
SELECT currval('user_seq');

-- 设置序列值
SELECT setval('user_seq', 100);

-- 设置序列值但不推进
SELECT setval('user_seq', 100, false);

-- 创建带序列的表
CREATE SEQUENCE user_id_seq;

CREATE TABLE users (
    id BIGINT PRIMARY KEY DEFAULT nextval('user_id_seq'),
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 插入数据
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

SELECT * FROM users;
-- id 自动从序列获取

-- 创建带序列的表 (SERIAL 风格)
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_no VARCHAR(50),
    amount DECIMAL(10, 2)
);

-- SERIAL 自动创建序列
-- 等价于:
CREATE SEQUENCE orders_id_seq;
CREATE TABLE orders (
    id BIGINT PRIMARY KEY DEFAULT nextval('orders_id_seq'),
    order_no VARCHAR(50),
    amount DECIMAL(10, 2)
);
ALTER SEQUENCE orders_id_seq OWNED BY orders.id;

-- 修改序列参数
ALTER SEQUENCE user_seq
    INCREMENT BY 2
    RESTART WITH 100;

-- 重置序列
ALTER SEQUENCE user_seq RESTART;

-- 重置到特定值
ALTER SEQUENCE user_seq RESTART WITH 1;

-- 设置序列所有者
ALTER SEQUENCE user_seq OWNED BY users.id;

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

-- 查看序列当前值
SELECT last_value FROM user_seq;

-- 查看所有序列
SELECT 
    sequence_schema,
    sequence_name,
    data_type,
    start_value,
    minimum_value,
    maximum_value,
    increment
FROM information_schema.sequences;

-- 删除序列
DROP SEQUENCE user_seq;

-- 如果序列被表使用，需要先删除表或修改默认值
DROP SEQUENCE IF EXISTS user_seq;

-- 强制删除 (如果有依赖)
DROP SEQUENCE user_seq CASCADE;

-- 使用 UUID 作为主键 (推荐)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 优点:
-- - 分散写入，避免热点
-- - 全局唯一
-- - 无需序列管理

-- 使用 unique_rowid
CREATE TABLE users (
    id BIGINT PRIMARY KEY DEFAULT unique_rowid(),
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 特点:
-- - 基于时间戳和节点ID
-- - 大致有序
-- - 分布式友好

ID生成方案对比
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   序列:                                                     │
│   ├── 优点: 连续、有序                                     │
│   ├── 缺点: 热点问题                                       │
│   └── 适用: 兼容PostgreSQL                                 │
│                                                             │
│   UUID:                                                     │
│   ├── 优点: 分散写入、全局唯一                             │
│   ├── 缺点: 无序、较长                                     │
│   └── 适用: 推荐                                           │
│                                                             │
│   unique_rowid:                                             │
│   ├── 优点: 大致有序、分布式友好                           │
│   ├── 缺点: 可能冲突                                       │
│   └── 适用: 需要有序ID                                     │
│                                                             │
│   建议: 优先使用 UUID                                       │
│                                                             │
└─────────────────────────────────────────────────────────────┘

序列使用建议
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   1. 优先使用 UUID                                          │
│      ├── 避免热点问题                                      │
│      └── 分布式友好                                        │
│                                                             │
│   2. 序列用于兼容场景                                       │
│      ├── 迁移PostgreSQL应用                                │
│      └── 需要连续ID                                        │
│                                                             │
│   3. 合理设置缓存                                           │
│      ├── 减少协调开销                                      │
│      └── CACHE 值根据并发量设置                            │
│                                                             │
│   4. 监控序列使用                                           │
│      ├── 避免耗尽                                          │
│      └── 及时调整                                          │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 推荐: UUID
CREATE TABLE best_practice_users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 兼容场景: 序列
CREATE SEQUENCE legacy_id_seq CACHE 100;

CREATE TABLE legacy_users (
    id BIGINT PRIMARY KEY DEFAULT nextval('legacy_id_seq'),
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 混合使用: 有序UUID
CREATE TABLE ordered_users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMP DEFAULT NOW(),
    name VARCHAR(100)
);

CREATE INDEX idx_created ON ordered_users(created_at);