@shanyy 2017-08-02T09:36:16.000000Z 字数 3193 阅读 2716

在Postgres里用Sequence

翻译

在twitter(也可能是其他地方)上很多人在问关于如何在PostgreSql中使用sequence。为了避免重复回答这个问题，我觉得在这里总结一下在Postgresql中使用sequence的基本步骤还是很有意义的。

什么是Sequence?

Sequence是数据库中一类特殊的对象，其用于生成唯一数字标识符。一个典型的应用场景就是手动生成一系列主键。Sequence和Mysql中的AUTO_INCREMENT的概念很像，但又不完全相同。

在表中怎么使用Sequence?

Sequence最常见的场景就是生成Serial这个伪类型，Serial类型主要有下面几个特征：

Serial的值就是Sequence生成的。
每次调用Sequence会生成一个新值（Serial类型的）。
由于Sequence生成的值都是非空的，所以它会在这一列数据上加一个NOT NULL的标志。
Sequence是自动生成的，Postgresql假设Sequence只用于生成Series列（唯一），所以如果删除了这一列，数据库会自动删除这个Sequence。

例如：下面的命令会新建一个表和一个Sequence对象，并且把这个Sequence对象关联到这个表上。

test=# CREATE TABLE users (
test(#     id    SERIAL, -- assign each user a numeric ID
test(#     name  TEXT,
test(#     age   INT4
test(# );
NOTICE:  CREATE TABLE will create implicit sequence
"users_id_seq" for serial column "users.id"
CREATE TABLE

在这个例子里，自动生成的Sequence对象被自动命名为user_id_seq。如果不想让数据库自动命名的话，可以调用下面介绍的pg_get_serial_sequence()这个函数。

需要注意的是使用Series列并不会自动创建索引，也不会把这一列标注为主键。解决这个问题也很简单，只需要手动加上PRIMARY KEY这个标示即可。

CREATE TABLE users (
    -- make the "id" column a primary key; this also creates
    -- a UNIQUE constraint and a b+-tree index on the column
    id    SERIAL PRIMARY KEY,
    name  TEXT,
    age   INT4
);

怎么把Sequence的值赋给新插入的数据?

如果你用了Serial变量，其默认值就是Sequence下一次生成的值。为了让插入的时候取到这个默认值，要么忽略插入对象中Serial这一列的值，要么在这个位置写上DEFAULT这个关键字。
下面是例子，二者完全等价：

-- 忽略id
INSERT INTO users (name, age) VALUES ('Mozart', 20);
-- 使用DEFAULT关键字
INSERT INTO users (name, age, id) VALUES ('Mozart', 20, DEFAULT);

怎么获取Sequence最新生成的数据?

你可以使用currval()这个函数来获取Sequence最新生成的值，需要注意的是这里获取的是本次session中的值，这么设计是有目的的。currval()需要一个参数：Sequence的名字。可以调用pg_get_serial_sequence()来找到和指定列关联的Sequence的名字。
下面是例子：

SELECT currval(pg_get_serial_sequence('users', 'id'));

需要注意的是，如果该Sequence在本次session中从来没有生成过新的数据，则currval()会报错。

会不会有竞争存在?

假如一个数据库客户端插入了一个Sequence生成的值，与此同时另一个客户端又插入了一个值，这样currval()获取的值岂不是有可能取到一个错误的值？

事实上并不会这样，Sequence通过优雅的设计避免了这个问题。currval()这回返回当前session的新值，所以其他用户的插入并不会改变currval()返回的值，只有该用户的插入操作才会更新该值。

插入并获取插入的数据ID岂不是需要两个Query语句?

使用上文中所说的currval()我们需要执行两次Query语句：一个用于插入数据，另一个用于获取新插入的ID。为了减少和数据库连接的次数，我们可以把插入和获取ID的两条指令连起来一起丢到数据库执行（译者注：不熟悉PHP，不过个人不推荐这么做，毕竟不是原子操作，高并发长连接的情况下出错的可能性应该还是有的）。下面是例子（PHP，在nodejs和python里可以有类似的写法)：

pg_exec("INSERT INTO users (name, age) VALUES ('Bach', 15);
         SELECT currval(pg_get_serial_sequence('users', 'id'));")

这条指令虽有两条命令，但是只需要和数据库通信一次即可，所以基本可以忽略第二条query的额外耗时。

另外，对于Postgresql 8.2以后的版本，可以直接采用RETURNING语法解决这个问题。（推荐这种方式）

INSERT INTO users (name, age) VALUES ('Liszt', 10) RETURNING id;

该语句会返回新插入的行的ID。

Sequence生成的数据的范围?

Sequence生成的是64位的整形数据，我们在上面用的Serial类型是一个32位的整形，如果需要使用64位的Serial则需要使用Serial8类型。

Sequence生成的数据会不连续吗?

当然会，Sequence是用于生成唯一的数据标识，并不需要严格连续。比如：如果两个客户端同时插入不同的数据（会调用nextval())的时候，每个客户端会得到一个新的Sequence值。如果其中一个在事务中失败或者其他原因回滚了，这个时候就会出现Sequence数据（即ID）不连续的现象，这只是其中一个例子。
修复这个问题也很简单，具体参考下面的链接解决ID不连续的问题

事务中的Sequence

Sequence操作是基于session的，与事务无关。nextval()会递增Sequence的值，但是即便是事务回滚了Sequence也不会撤销，而不论是在事务内外，currval()都会返回Sequence最新的值。

两个表共享一个Sequence?

实现这个功能的最简单做法就是手动生成一个Sequence，然后不要使用Serial类型，而是手动把Sequence绑定到对应的列。
下面是具体例子：

CREATE SEQUENCE common_fruit_id_seq;
CREATE TABLE apples (
    id      INT4 DEFAULT nextval('common_fruit_id_seq') NOT NULL,
    price   NUMERIC
);
CREATE TABLE oranges (
    id      INT4 DEFAULT nextval('common_fruit_id_seq') NOT NULL,
    weight  NUMERIC
);

nextval() 会生成一系列新的值。需要注意的是这个手动生成的Sequence并不会随着表被自动删除，而且你也没法调用 pg_get_serial_sequence()这个函数。