MySQL Postgresql SERIAL 的工作方式不同吗？

Does Postgresql SERIAL work differently?

提问by zod

I have a postgres table with a SERIAL id.

我有一个带有 SERIAL id 的 postgres 表。

id (serial) name age

Insert usually happens from a web application.

插入通常发生在 Web 应用程序中。

I inserted manually two new records setting the id as max (id)+1****

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

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

在这 2 次插入之后，当 Web 应用程序插入 2 条记录时，它会出现重复键错误。

Just for 2 records. After that everything works fine.

仅用于 2 个记录。之后一切正常。

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

问题是 - 为什么我的手动插入不增加序列？

Are auto increment and serial are different?

自动增量和串行是不同的吗？

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

我在这里缺少什么？MySQL 或任何其他 SQL 是否有相同的问题？

回答by mu is too short

When you create a serialor bigserialcolumn, PostgreSQL actually does three things:

当你创建一个serialorbigserial列时，PostgreSQL 实际上做了三件事：

1. Creates an intor bigintcolumn.
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().

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

When you INSERT a value without specifying the serialcolumn (or if you explicitly specify DEFAULTas its value), nextvalwill be called on the sequence to:

当您插入一个值而不指定serial列（或者如果您明确指定DEFAULT为其值）时，nextval将在序列上调用：

1. Return the next available value for the column.
2. Increment the sequence's value.

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

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

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

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

还要记住，记录可以被删除，因此serial列中的间隙是可以预料的，因此max(id) + 1即使没有并发问题，使用也不是一个好主意。

If you're using serialor 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.

如果您使用serialor bigserial，最好的办法是让 PostgreSQL 为您分配值，并假装它们是不透明的数字，只是碰巧按特定顺序出现：不要自己分配它们，也不要“除了独特性之外，不要对它们进行任何假设。此经验法则适用于所有数据库 IMO。

---

---

I'm not certain how MySQL's auto_incrementworks with all the different database types but perhaps the fine manualwill help.

我不确定 MySQL 是如何auto_increment处理所有不同数据库类型的，但也许精美的手册会有所帮助。

回答by Igor Romanchenko

If you want to insert a record into the table with serialcolumn - just ommit it from the query - it will be automaticaly generated.

如果您想将一条记录插入到带有serial列的表中- 只需从查询中省略它 - 它将自动生成。

Or you can insert its defaul value with something like:

或者您可以使用以下内容插入其默认值：

insert into your_table(id, val)
values (default, '123');

Third option is to malually take values from serial sequence directly:

第三种选择是直接从串行序列中恶意获取值：

insert into your_table(id, val)
values (nextval(pg_get_serial_sequence('your_table','id')), '123');

回答by Craig Ringer

I inserted manually two new records setting the id as max (id)+1**

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

This approach is totally wrong and won't work in anydatabase. It's only worked for you so far in MySQL by sheer luck.

这种方法是完全错误的，在任何数据库中都不起作用。到目前为止，它仅在 MySQL 中对您有用，这完全是运气。

If two connections simultaneously run this, they'll get the same ID. It can only work reliably if you lock the table against concurrent reads so that only one connection can be getting an ID at a time.

如果两个连接同时运行它，它们将获得相同的 ID。只有当您锁定表以防止并发读取时，它才能可靠地工作，以便一次只有一个连接可以获取 ID。

It's also terribly inefficient.

这也是非常低效的。

This is why sequences exist, so that you can reliably get IDs in the presence of concurrent inserters.

这就是序列存在的原因，以便您可以在并发插入器存在的情况下可靠地获取 ID。

Just use:

只需使用：

INSERT INTO my_table(data1, data2) VALUES ('a','b') RETURNING id;

or:

或者：

INSERT INTO my_table(id, data1, data2) VALUES (DEFAULT, 'a','b') RETURNING id;

DEFAULTis a special place-holder that tell the database to get the default for that column from the table definition. The default is nextval('my_table_id_seq'), so the next sequence value will get inserted.

DEFAULT是一个特殊的占位符，它告诉数据库从表定义中获取该列的默认值。默认值为nextval('my_table_id_seq')，因此将插入下一个序列值。

Since you're asking basic questions about sequences, I recommend you also consider that sequences are not gapless. It's normal for sequences to have "holes", where the table values go 1, 3, 4, 5, 9, 10, ... .

由于您问的是关于序列的基本问题，因此我建议您还考虑序列不是无间隙的。序列有“洞”是正常的，其中表值是 1, 3, 4, 5, 9, 10, ... 。