金仓数据库KingbaseES之自增列

您所在的位置：网站首页 › 怎么设置数据产生在列 › 金仓数据库KingbaseES之自增列

金仓数据库KingbaseES之自增列

2024-06-21 03:53| 来源: 网络整理| 查看: 265

关键字：自增列，sequence，serial，identity

KingbaseES中一共3种自增长类型sequence，serial，identity，他们的区别如下表：

对象

sequenceserialidentity显示插入是是是显示插入后更新最大值否否否清空表后是否重置否否否是否跟事务一起回滚否否否多对象共享是否否支持重置是是是出现错误后序列值是否增长是是是 Sequence

测试用例：

create sequence seq_1 INCREMENT BY 1 MINVALUE 1 START WITH 1; create table test_seq ( id int not null default nextval('seq_1') primary key, name varchar(10) );

隐式插入：

insert into test_seq (name) values ('aaa'); insert into test_seq (name) values ('bbb'); insert into test_seq (name) values ('ccc');

显式插入：

insert into test_seq (id,name) values (5,'ddd'); select * from test_seq;

查询结果

test1=# select * from test_seq; id | name ----+------ 1 | aaa 2 | bbb 3 | ccc 5 | ddd

再次隐式插入

--可以正常插入 insert into test_seq (name) values ('eee'); --插入失败，主键重复，因为序列自身是递增的，不会关心表中被显式插入的数据 insert into test_seq (name) values ('fff'); test1=# insert into test_seq (name) values ('eee'); INSERT 0 1 test1=# insert into test_seq (name) values ('fff'); 错误: 重复键违反唯一约束"test_seq_pkey" 描述: 键值"(id)=(5)" 已经存在 --再次执行语句可正常插入，序列因为之前的错误调用自动增加 test1=# insert into test_seq (name) values ('fff'); INSERT 0 1 test1=# select * from test_seq; id | name ----+------ 1 | aaa 2 | bbb 3 | ccc 5 | ddd 4 | eee 6 | fff --重置序列的最大值 select setval(' seq_1',(select max(id) from test_seq)::BIGINT); --事务回滚后，序列号并不会回滚 test1=# begin; BEGIN test1=# insert into test_seq (name) values ('ggg'); INSERT 0 1 test1=# rollback; ROLLBACK test1=# test1=# test1=# select * from test_seq; id | name ----+------ 1 | aaa 2 | bbb 3 | ccc 5 | ddd 4 | eee 6 | fff (6 行记录) test1=# insert into test_seq (name) values ('ggg'); INSERT 0 1 test1=# select * from test_seq; id | name ----+------ 1 | aaa 2 | bbb 3 | ccc 5 | ddd 4 | eee 6 | fff 8 | ggg (7 行记录) -- truncate 表之后，序列不受影响 test1=# truncate table test_seq; TRUNCATE TABLE test1=# insert into test_seq (name) values ('ggg'); INSERT 0 1 test1=# select * from test_seq; id | name ----+------ 9 | ggg (1 行记录) --重置序列 ALTER SEQUENCE seq_1 RESTART WITH 1; test1=# ALTER SEQUENCE seq_1 RESTART WITH 1; ALTER SEQUENCE test1=# insert into test_seq (name) values ('ggg'); INSERT 0 1 test1=# select * from test_seq; id | name ----+------ 9 | ggg 1 | ggg Serial

测试用例

create table test_serial ( id serial primary key, name varchar(100) );

隐式插入

insert into test_serial(name) values ('aaa'); insert into test_serial(name) values ('bbb'); insert into test_serial(name) values ('ccc');

显示插入

insert into test_serial(id,name) values (5,'ddd); select * from test_serial; --再次隐式插入,第二次会报错 insert into test_serial(name) values ('eee'); insert into test_serial(name) values ('fff'); test1=# insert into test_serial(id,name) values (5,'ddd); INSERT 0 1 test1=# insert into test_serial(name) values ('eee'); INSERT 0 1 test1=# insert into test_serial(name) values ('fff'); 错误: 重复键违反唯一约束"test_serial_pkey" 描述: 键值"(id)=(5)" 已经存在 --再次执行语句可正常插入，序列因为之前的错误调用自动增加 test1=# insert into test_serial(name) values ('fff'); INSERT 0 1 test1=# select * from test_serial; id | name ----+------ 1 | aaa 2 | bbb 3 | ccc 5 | ddd 4 | eee 6 | fff (6 行记录) --重置serial SELECT SETVAL((SELECT sys_get_serial_sequence(' test_serial', 'id')), 1, false); Identity

Identity是R6版本新增的语法，R3数据库不支持该语法。

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

identity定义成always as identity 不允许显示插入，但是加上overriding system value也可以显式插入。

测试用例1：

create table test_identiy_1 ( id int generated always as identity (START WITH 1 INCREMENT BY 1) primary key , name varchar(100) ); insert into test_identiy_1(name) values ('aaa'); insert into test_identiy_1(name) values ('bbb'); insert into test_identiy_1(name) values ('ccc');

显式插入值

如果定义为generated always as identity则不允许显式插入，除非增加overriding system value 提示。

insert into test_identiy_1(id,name) values (5,'ccc'); insert into test_identiy_1(id,name)overriding system value values (5,'ccc'); test1=# insert into test_identiy_1(id,name) values (5,'ccc'); 错误: 无法插入到列"id" 描述: 列"id"是定义为GENERATED ALWAYS的标识列. 提示: 使用OVERRIDING SYSTEM VALUE覆盖. test1=# insert into test_identiy_1(id,name)overriding system value values (5,'ccc'); INSERT 0 1

测试用例2：

create table test_identiy_2 ( id int generated by default as identity (START WITH 1 INCREMENT BY 1) primary key , name varchar(100) ); insert into test_identiy_2(name) values ('aaa'); insert into test_identiy_2(name) values ('bbb'); insert into test_identiy_2(name) values ('ccc'); test1=# insert into test_identiy_2(id,name) values (5,'ccc'); INSERT 0 1

重置Identity的方式有2种：

--方式1： ALTER TABLE test_identiy_1 ALTER COLUMN id RESTART WITH 100; --方式2： TRUNCATE table test_identiy_1 RESTART IDENTITY;

【本文地址】

金仓数据库KingbaseES之自增列

金仓数据库KingbaseES之自增列

今日新闻

推荐新闻