PostgreSQL三种自增列sequence,serial,identity的用法区别

痛定思痛。 2022-10-26 16:20 302阅读 0赞

这三个对象都可以实现自增，这里从如下几个维度来看看这几个对象有哪些不同，其中功能性上看，大部分特性都是一致的或者类似的。

1、sequence在所有数据库中的性质都一样，它是跟具体的字段不是强绑定的，其特点是支持多个对个对象之间共享。

sequence作为自增字段值的时候，对表的写入需要另外单独授权sequence（GRANT USAGE ON SEQUENCE test\_old\_id\_seq;）

sequence类型的字段表，在使用CREATE TABLE new\_table LIKE old\_table的时候，新表的自增字段会已久指向原始表的sequence

结论：  
对于自增字段，无特殊需求的情况下，sequence不适合作为“自增列”，作为最最次选。

2、identity本质是为了兼容标准sql中的语法而新加的，修复了一些serial的缺陷，比如无法通过alter table的方式实现增加或者删除serial字段

2.1 identity定义成generated by default as identity也允许显式插入，

2.2 identity定义成always as identity，加上overriding system value也可以显式不插入

结论：  
identity是serial的“增强版”，更适合作为“自增列”使用。

3、sequence，serial，identity共同的缺点是在显式插入之后，无法将自增值更新为表中的最大Id，这一点再显式插入的情况下是潜在自增字段Id冲突的

结论：  
自增列在显式插入之后，一定要手动重置为表的最大Id。

4、自增字段的update没有细看，相对来说自增列的显式插入是一种常规操作，那些对自增列的update操作，只要脑子没问题，一般是不会这么干的。

原始手稿，懒得整理了，不涉及原理性的东西，动手试一遍就明白了。

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
53  
54  
55  
56  
57  
58  
59  
60  
61  
62  
63  
64  
65  
66  
67  
68  
69  
70  
71  
72  
73  
74  
75  
76  
77  
78  
79  
80  
81  
82  
83  
84  
85  
86  
87  
88  
89  
90  
91  
92  
93  
94  
95  
96  
97  
98  
99  
100  
101  
102  
103  
\---------------------------------------------------------sequence-------------------------------------------------------------  
create sequence myschema.seq\_1 INCREMENT BY 1 MINVALUE 1 START WITH 1;  
create table myschema.test\_seq  
(  
id int not null default nextval('myschema.seq\_1') primary key,  
name varchar(10)  
);  
隐式插入  
insert into myschema.test\_seq (name) values ('aaa');  
insert into myschema.test\_seq (name) values ('bbb');  
insert into myschema.test\_seq (name) values ('ccc');  
select \* from myschema.test\_seq;  
显式插入  
insert into myschema.test\_seq (id,name) values (5,'ddd');  
select \* from test\_seq;  
再次隐式插入  
\--可以正常插入  
insert into myschema.test\_seq (name) values ('eee');  
\--插入失败，主键重复，因为序列自身是递增的，不会关心表中被显式插入的数据  
insert into myschema.test\_seq (name) values ('fff');  
\--重置序列的最大值  
select setval('myschema.seq\_1',(select max(id) from myschema.test\_seq)::BIGINT);  
\--事务回滚后，序列号并不会回滚  
begin;  
insert into myschema.test\_seq (name) values ('ggg');  
rollback;  
\-- truncate 表之后，序列不受影响  
truncate table myschema.test\_seq;  
\--重置序列  
ALTER SEQUENCE myschema.seq\_1 RESTART WITH 1;  
\---------------------------------------------------------serial-------------------------------------------------------------  
create table myschema.test\_serial  
(  
id serial primary key,  
name varchar(100)  
)  
select \* from test\_serial;  
insert into myschema.test\_serial(name) values ('aaa');  
insert into myschema.test\_serial(name) values ('bbb');  
insert into myschema.test\_serial(name) values ('ccc');  
select \* from myschema.test\_serial;  
\--显式插入,可以执行  
insert into myschema.test\_serial(id,name) values (5,'ccc');  
\--再次隐式插入,第二次会报错，因为隐式插入的话，serial会基于显式插入之前的Id做自增，serial无法意识到当前已经存在的最大值  
insert into myschema.test\_serial(name) values ('xxx');  
insert into myschema.test\_serial(name) values ('yyy');  
select \* from myschema.test\_serial;  
\--truncate table 后serial不会重置  
truncate table myschema.test\_serial;  
insert into myschema.test\_serial(name) values ('aaa');  
insert into myschema.test\_serial(name) values ('bbb');  
insert into myschema.test\_serial(name) values ('ccc');  
select \* from myschema.test\_serial;  
\--验证是否会随着事务一起回滚，结论：不会  
begin;  
insert into myschema.test\_serial(name) values ('yyy');  
rollback;  
\--重置serial，需要注意的是重置的Id必须要大于相关表的字段最大Id，否则会产生重号  
SELECT SETVAL((SELECT pg\_get\_serial\_sequence('myschema.test\_serial', 'id')), 1, false);  
\---------------------------------------------------------identity-------------------------------------------------------------  
drop table myschema.test\_identiy\_1  
create table myschema.test\_identiy\_1  
(  
id int generated always as identity (cache 100 START WITH 1 INCREMENT BY 1) primary key ,  
name varchar(100)  
);  
create table myschema.test\_identiy\_2  
(  
id int generated by default as identity (cache 100 START WITH 1 INCREMENT BY 1) primary key ,  
name varchar(100)  
);  
insert into myschema.test\_identiy\_1(name) values ('aaa');  
insert into myschema.test\_identiy\_1(name) values ('bbb');  
insert into myschema.test\_identiy\_1(name) values ('ccc');  
insert into myschema.test\_identiy\_2(name) values ('aaa');  
insert into myschema.test\_identiy\_2(name) values ('bbb');  
insert into myschema.test\_identiy\_2(name) values ('ccc');  
select \* from myschema.test\_identiy\_1;  
\--显式插入值,如果定义为generated always as identity则不允许显式插入，除非增加overriding system value 提示  
\--一旦提示了overriding system value，可以  
insert into myschema.test\_identiy\_1(id,name) values (5,'ccc');  
insert into myschema.test\_identiy\_1(id,name)overriding system value values (5,'ccc');  
select \* from myschema.test\_identiy\_2;  
\--显式插入值,如果定义为generated by default as identity则允许显式插入，  
insert into myschema.test\_identiy\_2(id,name) values (5,'ccc');  
\--显式插入后，继续隐式插入,第二次插入会报错，identity已久是不识别表中显式插入后的最大值  
insert into myschema.test\_identiy\_2(name) values ('xxx');  
insert into myschema.test\_identiy\_2(name) values ('yyy');  
select \* from myschema.test\_identiy\_2;  
总之个identity很扯淡，你定义成always as identity，加上overriding system value可以显式不插入  
定义成generated by default as identity也允许显式插入  
不管怎么样，既然都允许显式插入，那扯什么淡的来个overriding system value  
\--truncate后再次插入，自增列不会重置  
truncate table myschema.test\_identiy\_1;  
select \* from myschema.test\_identiy\_1;  
begin;  
insert into myschema.test\_identiy\_1(name) values ('xxx');  
rollback;  
\--truncate并且RESTART IDENTITY后，会重置自增列  
TRUNCATE table myschema.test\_identiy\_1 RESTART IDENTITY;  
select \* from myschema.test\_identiy\_1  
\--identity自增列的重置表或者更改  
ALTER TABLE myschema.test\_identiy\_1 ALTER COLUMN id RESTART WITH 100;  
实际中更改identity自增长列的当前起始值（已有的最大值+1）：

补充：PostgreSQL不同的表使用不同的自增序列

hibernate 配置文件里面应该是这样的:

1  
2  
3  
4  
5  
<id name="id">  
<generator class="sequence">  
<param name="sequence">adminuser</param>  
</generator>  
</id>  
使用xdoclet时 类里面的配置应该是这样的:

1  
2  
3  
4  
5  
6  
/\*\*  
\* @hibernate.id generator-class="sequence"  
\* @hibernate.generator-param name="sequence" value="adminuser"  
\*/  
  
private int id;  
以上为个人经验，希望能给大家一个参考