Postgresql SERIAL的工作方式是否有所不同? [英] Does Postgresql SERIAL work differently?

问题描述

我有一个带有序列ID的postgres表.

I have a postgres table with a SERIAL id.

id (serial) name age

插入通常是通过Web应用程序完成的.

Insert usually happens from a web application.

我手动插入了两个新记录，将id设置为max(id)+1 ****

在这两次插入之后，当网络应用插入2条记录时，它会给出重复的键错误.

After these 2 insert when the web app inserts 2 record it gives duplicate key error.

只需2条记录.之后，一切正常.

Just for 2 records. After that everything works fine.

问题是-为什么我的手动插入没有增加序列号?

The question is - Why didn't my manual insert increment the serial?

自动增量和序列号不同吗?

Are auto increment and serial are different?

我在这里想念什么? MySQL或任何其他SQL都有相同的问题吗?

What am I missing here? Do MySQL or any other SQL have the same issue?

推荐答案

创建

When you create a serial or bigserial column, PostgreSQL actually does three things:

1. 创建一个int或bigint列.
2. 创建一个序列(由列拥有)以生成该列的值.
3. 将列的默认值设置为序列的nextval().

1. Creates an int or bigint column.
2. Creates a sequence (owned by the column) to generate values for the column.
3. Sets the column's default value to the sequence's nextval().

在不指定serial列的情况下插入值(或如果您明确指定DEFAULT作为其值)时，将在序列上调用nextval:

When you INSERT a value without specifying the serial column (or if you explicitly specify DEFAULT as its value), nextval will be called on the sequence to:

1. 返回该列的下一个可用值.
2. 增加序列的值.

如果您手动为serial列提供非默认值，则序列将不会更新，并且nextval可以返回您的serial列已使用的值.因此，如果您执行此类操作，则必须通过调用 或setval .

If you manually supply a non-default value for the serial column then the sequence won't be updated and nextval can return values that your serial column already uses. So if you do this sort of thing, you'll have to manually fix the sequence by calling nextval or setval.

还请记住，可以删除记录，因此可以预见serial列中的空白，因此即使没有并发问题，使用max(id) + 1也不是一个好主意.

Also keep in mind that records can be deleted so gaps in serial columns are to be expected so using max(id) + 1 isn't a good idea even if there weren't concurrency problems.

如果您使用的是serial或bigserial，最好的选择是让PostgreSQL为您分配值，并假装它们是不透明的数字，它们恰好以一定顺序出现:不要自己给他们分配东西，除了唯一性之外，不要对他们承担其他任何责任.此经验法则适用于所有数据库IMO.

If you're using serial or bigserial, your best bet is to let PostgreSQL take care of assigning the values for you and pretend that they're opaque numbers that just happen to come out in a certain order: don't assign them yourself and don't assume anything about them other than uniqueness. This rule of thumb applies to all database IMO.

我不确定MySQL的auto_increment如何与所有不同的数据库类型一起使用，但可能

I'm not certain how MySQL's auto_increment works with all the different database types but perhaps the fine manual will help.

这篇关于Postgresql SERIAL的工作方式是否有所不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！