PostgreSQL的SERIAL工作方式有所不同吗？

Question

24

我有一个带有SERIAL ID的Postgres表。

id (serial) name age

插入通常来自于一个网络应用程序。

我手动插入了两个新记录，并将id设置为最大值(id)+1。****

在这两次插入之后，当网络应用程序再次插入2条记录时，它会显示重复键错误。

只有这两条记录会出现这种情况。之后一切正常。

问题是——为什么我的手动插入操作没有递增序列？

自动递增和序列是否不同？

我错过了什么吗？MySQL或任何其他SQL是否有相同的问题？

- zod

1

在PostgreSQL中，Serial是一个自增的四字节整数，如果id是serial类型，你不应该生成自己的数字。而在MySQL中，则是AUTO_INCREMENT列或类似的东西。 - Raymond Nijland

3个回答

8

如果您想向具有“序列”列的表中插入记录 - 只需从查询中省略它 - 它将自动生成。

或者您可以使用类似以下内容的语句插入其默认值：

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

第三个选项是直接从串行序列中手动取值：

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

- Ihor Romanchenko

谢谢...所以它和MySQL不同..是吗？在Oracle和SQL Server中，序列的工作方式相同吗？ - zod

@zod 在Oracle中是一样的，但我对MS SQL Server没有经验。 - Ihor Romanchenko

关于SERIAL类型的默认值插入，当未指定列时，这是一个很好的答案。我一直在寻找Postgres中类似于MySQL中AUTO_INCREMENT的'null'插入的等效方法。谢谢。 - Morey

3

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

这种方法在任何数据库中都是完全错误的，只有在MySQL中因为幸运的原因才能够工作。

如果同时有两个连接运行此操作，则会得到相同的ID。只有在锁定表以防止并发读取的情况下，才能可靠地使用此方法，以便只有一个连接可以获取ID。

此外，这种方法效率极低。

这就是为什么序列存在的原因，因为它们可以在并发插入时可靠地获取ID。

请使用以下方法：

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

或者：

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

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

由于您正在询问关于序列的基本问题，建议您还要考虑到序列不是连续的。序列出现“间隙”是很正常的，例如表中的值为1、3、4、5、9、10等。

- Craig Ringer

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- mu is too short · Accepted Answer

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

当你插入一个没有指定serial列的值(或者显式地将其值指定为DEFAULT)时，nextval将会调用序列来：

如果您手动为serial列提供非默认值，则序列不会被更新，而nextval可以返回已使用您的serial列的值。因此，如果您这样做，您将不得不通过调用nextval或setval来手动修复序列。

此外，请记住，记录可能会被删除，因此在serial列中出现间隔是可以预期的，即使没有并发问题，也不要使用max(id) + 1，这不是一个好主意。

如果您正在使用serial或bigserial，最好让PostgreSQL为您分配值，并假装它们是一些以特定顺序出现的不透明数字：不要自己分配它们，也不要假设任何关于它们的东西，除了唯一性。我认为这个经验法则适用于所有数据库。

我不确定MySQL的auto_increment如何与所有不同类型的数据库一起使用，但也许精细手册可以帮助。