Postgresql SERIAL 的工作方式不同吗?

2021-11-30 00:00:00 postgresql sql mysql auto-increment

我有一个带有 SERIAL 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 次插入后，当 Web 应用程序插入 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?

自增和串行有区别吗?

我在这里错过了什么?MySQL 或任何其他 SQL 是否有相同的问题?

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

推荐答案

当你创建一个 serial 或 bigserial 列，PostgreSQL 实际上做了三件事:

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 列已使用.因此，如果您执行此类操作，则必须通过调用 nextval 或 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.

相关文章