kingbaseES数据库自增列

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

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

对象

sequence    serial    identity
显示插入    是    是    是
显示插入后更新最大值    否    否    否
清空表后是否重置    否    否    否
是否跟事务一起回滚    否    否    否
多对象共享    是    否    否
支持重置    是    是    是
出现错误后序列值是否增长    是    是    是
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;