我不明白 postgresql 的 nextval() 是如何工作的，有人可以解释一下吗? [英] I don't understand how postgresql's nextval() work, can someone explain?

问题描述

在我担任前端开发人员的头几个月后，我才刚刚开始涉足后端开发.我正在使用 postgreSQL，但似乎无法理解 nextval() 函数.我读过这个，但我不清楚.http://www.postgresql.org/docs/current/interactive/functions-sequence.htmlnexval() 有哪些好处/用例?

I'm just starting to wade into backend development after my first few months on the job as a front end dev. I'm working with postgreSQL and can't seem to wrap my head around the nextval() function. I read this, but it's not clear to me. http://www.postgresql.org/docs/current/interactive/functions-sequence.html what are the benefits/use cases for nexval()?

推荐答案

NEXTVAL 是一个从序列中获取下一个值的函数.

NEXTVAL is a function to get the next value from a sequence.

Sequence 是一个对象，它返回不断增加的数字，每次调用都不同，而不管交易等.

Sequence is an object which returns ever-increasing numbers, different for each call, regardless of transactions etc.

每次调用 NEXTVAL 时，都会得到一个不同的号码.

Each time you call NEXTVAL, you get a different number.

这主要用于为您的表生成代理主键.

This is mainly used to generate surrogate primary keys for you tables.

你可以像这样创建一个表:

You can create a table like this:

CREATE SEQUENCE mysequence;
CREATE TABLE mytable (id BIGINT NOT NULL PRIMARY KEY, value INT);

并插入这样的值:

INSERT
INTO    mytable (id, value)
VALUES
        (NEXTVAL('mysequence'), 1),
        (NEXTVAL('mysequence'), 2);

看看你得到了什么:

SELECT * FROM mytable;
 id | value
----+-------
  1 |     1
  2 |     2

PostgreSQL 为此提供了一个很好的语法糖:

PostgreSQL offers a nice syntax sugar for this:

CREATE TABLE mytable (id BIGSERIAL PRIMARY KEY, value INT);

相当于

CREATE SEQUENCE mytable_id_seq; -- table_column_'seq'
CREATE TABLE mytable (id BIGINT NOT NULL PRIMARY KEY DEFAULT NEXTVAL('mytable_id_seq'), value INT); -- it's not null and has a default value automatically

并且可以这样使用:

INSERT
INTO    mytable (value)
VALUES  (1),
        (2);  -- you can omit id, it will get filled for you.

请注意，即使您回滚插入语句或从两个不同的会话运行并发语句，返回的序列值也永远不会相同并且永远不会被重用(尽管在 CYCLE).

Note that even if you rollback your insert statement or run concurrent statements from two different sessions, the returned sequence values will never be the same and never get reused (read the fine print in the docs though under CYCLE).

因此您可以确保主键的所有值都将在表中生成唯一.

So you can be sure all the values of your primary keys will be generated unique within the table.

这篇关于我不明白 postgresql 的 nextval() 是如何工作的，有人可以解释一下吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！