达梦自增列的几种实现方式
DM 官方提供两种自增方式：IDENTITY 自增列和AUTO_INCREMENT自增列。另外我们还可以像Oracle那样使用序列方式实现列的自增。
1、IDENTITY 自增列
语法格式
IDENTITY [ (种子, 增量) ]
参数
1．种子：装载到表中的第一个行所使用的值；
2．增量：增量值，该值被添加到前一个已装载的行的标识值上。增量值可以为正数或负数，但不能为 0。
1.1、使用说明
1.IDENTITY 适用于 INT(-2147483648～+2147483647)、BIGINT(-263～+263-2)类型的列。每个表只能创建一个自增列；
2.不能对自增列使用 DEFAULT 约束；
3.必须同时指定种子和增量值，或者二者都不指定。如果二者都未指定，则取默认值(1,1)。若种子或增量为小数类型，报错；
4.最大值和最小值为该列的数据类型的边界；
5.建表种子和增量大于最大值或者种子和增量小于最小值时报错；
6.自增列一旦生成，无法更新，不允许用 UPDATE 语句进行修改；
7.自增列的值一旦生成，无法回滚。例如，数据表 T 中包含一个自增列，该列当前值为 n，增量为 1，对表 T 执行数据插入时，如果经约束检查发现待插入数据不满足约束条件，则会回滚数据插入操作，但此时自增列的值 n+1 已经生成，无法回滚，因此自增列的当前值变为 n+1；
8.临时表、列存储表、水平分区表不支持使用自增列。
下面我们对其做简单的测试：
SQL> CREATE TABLE IDENT_TABLE (
C1 INT IDENTITY(100, 100),
C2 INT
);
操作已执行
已用时间: 30.010(毫秒). 执行号:585.
SQL>
SQL> insert into IDENT_TABLE values (1);
影响行数 1
已用时间: 1.511(毫秒). 执行号:586.
SQL> select * from IDENT_TABLE;
行号 C1 C2
---------- ----------- -----------
1 100 1
已用时间: 5.876(毫秒). 执行号:587.
SQL> insert into IDENT_TABLE values (1);
影响行数 1
已用时间: 0.702(毫秒). 执行号:588.
SQL> select * from IDENT_TABLE;
行号 C1 C2
---------- ----------- -----------
1 100 1
2 200 1
已用时间: 0.310(毫秒). 执行号:589.
可以看到我们设置IDENTITY(100, 100)，自增列的初始值为100，自增的步长为100。
如果我们删除表的最后一条记录，或者全表删除时，新增数据的自增列值会从哪里开始呢？下面删除表的最后一条记录：
SQL> delete from IDENT_TABLE where C1=200;
影响行数 1
已用时间: 1.670(毫秒). 执行号:1209.
SQL> commit;
操作已执行
已用时间: 2.851(毫秒). 执行号:1210.
SQL> insert into IDENT_TABLE values (1);
影响行数 1
已用时间: 0.235(毫秒). 执行号:1212.
SQL> commit;
操作已执行
已用时间: 1.876(毫秒). 执行号:1213.
SQL> select * from IDENT_TABLE;
行号 C1 C2
---------- ----------- -----------
1 100 1
2 300 1
已用时间: 0.238(毫秒). 执行号:1214.
可见删除最后一条记录，新增的数据会从上次添加的最后一个自增值开始自增。然后对全表删除做测试：
SQL> delete from ident_table;
影响行数 2
已用时间: 1.533(毫秒). 执行号:1215.
SQL> commit;
操作已执行
已用时间: 2.052(毫秒). 执行号:1216.
SQL> insert into IDENT_TABLE values (1);
影响行数 1
已用时间: 0.292(毫秒). 执行号:1217.
SQL> select * from ident_table;
行号 C1 C2
---------- ----------- -----------
1 400 1
已用时间: 1.152(毫秒). 执行号:1218.
SQL> truncate table ident_table;
操作已执行
已用时间: 55.348(毫秒). 执行号:1219.
SQL> insert into IDENT_TABLE values (1);
影响行数 1
已用时间: 0.402(毫秒). 执行号:1221.
SQL> select * from ident_table;
行号 C1 C2
---------- ----------- -----------
1 100 1
已用时间: 0.220(毫秒). 执行号:1222.
全表删除分delete和truncate两种情况，全表delete删除时，新增的数据同样会从上次添加的最后一个自增值开始自增。truncate表时，自增列的值会进行初始化，重新开始编号。
SQL> update IDENT_TABLE set C1=180 where C1=200;
update IDENT_TABLE set C1=180 where C1=200;
第1 行附近出现错误[-2664]:试图修改自增列[C1].
已用时间: 0.742(毫秒). 执行号:0.
SQL> insert into IDENT_TABLE(C1,C2) values(220,1);
insert into IDENT_TABLE values(220,1);
第1 行附近出现错误[-2723]:仅当指定列列表，且SET IDENTITY_INSERT为ON时，才能对自增列赋值.
已用时间: 0.563(毫秒). 执行号:0.
可以看到，不允许对自增字段进行update操作，不能对自增字段进行手动插入。如何对自增列显示插入数值呢？下面我们介绍IDENTITY_INSERT属性。
1.2、SET IDENTITY_INSERT 属性
设置是否允许将显式值插入表的自增列中。ON 是，OFF 否。
语法格式：
SET IDENTITY_INSERT [<模式名>.]<表名> ON WITH REPLACE NULL;
SET IDENTITY_INSERT [<模式名>.]<表名> OFF;
参数
1．<模式名> 指明表所属的模式，缺省为当前模式；
2．<表名> 指明含有自增列的表名。
使用说明
1．IDENTITY_INSERT 属性的默认值为 OFF。SET IDENTITY_INSERT 的设置是在执行或运行时进行的。当一个连接结束，IDENTITY_INSERT 属性将被自动还原为 OFF；
2．DM 要求一个会话连接中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON，当设置一个新的表 IDENTITY_INSERT 属性设置为 ON 时，之前已经设置为 ON 的表会自动还原为 OFF。当一个表的 IDENTITY_INSERT 属性被设置为 ON 时，该表中的自动增量列的值由用户指定。如果插入值大于表的当前标识值(自增列当前值)，则 DM 自动将新插入值作为当前标识值使用，即改变该表的自增列当前值；否则，将不影响该自增列当前值；
3．当设置一个表的 IDENTITY_INSERT 属性为 OFF 时，新插入行中自增列的当前值由系统自动生成，用户将无法指定；
4．自增列一经插入，无法修改；
5．手动插入自增列，除了将 IDENTITY_INSERT 设置为 ON，还要求在插入列表中明确指定待插入的自增列列名。插入方式与非 IDENTITY 表是完全一样的。如果插入时，既不指定自增列名也不给自增列赋值，则新插入行中自增列的当前值由系统自动生成;
6．WITH REPLACE NULL 此模式下允许显式插入 NULL 值，同时，系统自动将 NULL 值替换为自增值。
SQL> SET IDENTITY_INSERT TEST.IDENT_TABLE ON WITH REPLACE NULL;
操作已执行
已用时间: 0.401(毫秒). 执行号:592.
SQL> insert into IDENT_TABLE(C1,C2) values(220,1);
影响行数 1
已用时间: 0.790(毫秒). 执行号:593.
SQL> select * from IDENT_TABLE;
行号 C1 C2
---------- ----------- -----------
1 100 1
2 200 1
3 220 1
已用时间: 1.177(毫秒). 执行号:596.
可以看到，当IDENTITY_INSERT属性为ON时，可以手动对自增列进行插入操作。这里对表插入数据时，如果手动插入的自增列值大于当前的标识值，则手动插入的值会成为新的标识值。
SQL> SET IDENTITY_INSERT TEST.IDENT_TABLE OFF;
操作已执行
已用时间: 1.170(毫秒). 执行号:597.
SQL> insert into IDENT_TABLE values (1);
影响行数 1
已用时间: 0.680(毫秒). 执行号:598.
SQL> select * from IDENT_TABLE;
行号 C1 C2
---------- ----------- -----------
1 100 1
2 200 1
3 220 1
4 320 1
已用时间: 0.389(毫秒). 执行号:599.
当我们把IDENTITY_INSERT重新设为OFF，对表插入数据时，刚才手动插入的自增列值成为新的标识值。
如果手动插入的自增列值小于当前的标识值，则新的标识值依然是上次添加的最后一个自增值。
SQL> SET IDENTITY_INSERT TEST.IDENT_TABLE ON WITH REPLACE NULL;
操作已执行
已用时间: 0.417(毫秒). 执行号:1231.
SQL> insert into IDENT_TABLE(C1,C2) values(230,1);
影响行数 1
已用时间: 0.937(毫秒). 执行号:1232.
SQL> select * from IDENT_TABLE;
行号 C1 C2
---------- ----------- -----------
1 100 1
2 200 1
3 220 1
4 320 1
5 230 1
已用时间: 0.391(毫秒). 执行号:1233.
SQL> insert into IDENT_TABLE values (1);
影响行数 1
已用时间: 0.562(毫秒). 执行号:1234.
SQL> select * from IDENT_TABLE;
行号 C1 C2
---------- ----------- -----------
1 100 1
2 200 1
3 220 1
4 320 1
5 230 1
6 420 1
6 rows got
已用时间: 0.380(毫秒). 执行号:1235.
2、AUTO_INCREMENT 自增列
2.1、使用说明
AUTO_INCREMEN 列必须为主键或主键的部分，只支持整数类型（支持 TINYINT/SMALLINT/INT/BIGINT，不支持 dec(N, 0)等），不能违反主键的唯一性约束。
AUTO_INCREMENT 关键字需要和 <AUTO_INCREMENT 子句 >、三个 AUTO_INCREMENT 相关 INI 参数（AUTO_INCREMENT_INCREMENT，AUTO_INCREMENT_OFFSET，NO_AUTO_VALUE_ON_ZERO）一起配合使用。
<AUTO_INCREMENT 子句 >：用于指定隐式插入值的起始边界值，若不指定，起始边界值默认为 1。
AUTO_INCREMENT_INCREMENT：动态会话级，表示 AUTO_INCREMENT 的步长。取值范围 1~65535。缺省值为 1。
AUTO_INCREMENT_OFFSE：动态会话级，表示 AUTO_INCREMENT 的基准偏移。取值范围 1~65535。缺省值为 1。
NO_AUTO_VALUE_ON_ZERO：动态会话级，表示 AUTO_INCREMENT 列插入 0 时，是否自动插入自增的下一个值。
SQL> show parameter increment
行号 PARA_NAME PARA_VALUE
---------- ------------------------ ----------
1 AUTO_INCREMENT_INCREMENT 1
2 AUTO_INCREMENT_OFFSET 1
SQL> CREATE TABLE "TABLE_1"
(
"ID" INT PRIMARY KEY AUTO_INCREMENT ,
"NAME" CHAR(10)
);2 3 4 5
操作已执行
SQL> insert into TABLE_1(name) values('test');
影响行数 1
已用时间: 0.728(毫秒). 执行号:526.
SQL> select * from table_1;
行号 ID NAME
---------- ----------- ----------
1 1 test
已用时间: 0.457(毫秒). 执行号:529.
SQL> insert into TABLE_1(name) values('test');
影响行数 1
已用时间: 0.418(毫秒). 执行号:530.
SQL> select * from table_1;
行号 ID NAME
---------- ----------- ----------
1 1 test
2 2 test
已用时间: 0.301(毫秒). 执行号:532.
可以看到，对主键设置AUTO_INCREMENT属性后，默认初始值为1，自增步长为1。
我们同样对删除表的最后一条记录，或者全表删除时的情况做测试。
SQL> delete from table_1 where id=2;
影响行数 1
已用时间: 1.043(毫秒). 执行号:1245.
SQL> commit;
操作已执行
已用时间: 2.470(毫秒). 执行号:1246.
SQL> insert into TABLE_1(name) values('test');
影响行数 1
已用时间: 0.354(毫秒). 执行号:1247.
SQL> commit;
操作已执行
已用时间: 2.736(毫秒). 执行号:1248.
SQL> select * from table_1;
行号 ID NAME
---------- ----------- ----------
1 1 test
2 3 test
已用时间: 0.234(毫秒). 执行号:1249.
删除最后一条记录，新增的数据会从上次添加的最后一个自增值开始自增。
SQL> delete from table_1;
影响行数 2
已用时间: 1.608(毫秒). 执行号:1250.
SQL> commit;
操作已执行
已用时间: 2.961(毫秒). 执行号:1251.
SQL> insert into TABLE_1(name) values('test');
影响行数 1
已用时间: 0.300(毫秒). 执行号:1252.
SQL> commit;
操作已执行
已用时间: 2.388(毫秒). 执行号:1253.
SQL> select * from table_1;
行号 ID NAME
---------- ----------- ----------
1 4 test
已用时间: 0.334(毫秒). 执行号:1254.
SQL> truncate table table_1;
操作已执行
已用时间: 10.098(毫秒). 执行号:1255.
SQL> insert into TABLE_1(name) values('test');
影响行数 1
已用时间: 1.053(毫秒). 执行号:1256.
SQL> commit;
操作已执行
已用时间: 2.294(毫秒). 执行号:1257.
SQL> select * from table_1;
行号 ID NAME
---------- ----------- ----------
1 1 test
已用时间: 0.322(毫秒). 执行号:1258.
全表delete删除时，新增的数据同样会从上次添加的最后一个自增值开始自增。truncate表时，自增列的值会进行初始化，重新开始编号。
可以使用alter table table_name AUTO_INCREMENT=n;语句更改auto_increment自增列的起始值 。
SQL> select * from table_1;
行号 ID NAME
---------- ----------- ----------
1 1 test
2 2 test
已用时间: 0.508(毫秒). 执行号:1261.
SQL> delete from table_1 where id=2;
影响行数 1
已用时间: 0.358(毫秒). 执行号:1262.
SQL> commit;
操作已执行
已用时间: 1.542(毫秒). 执行号:1263.
SQL> alter table table_1 AUTO_INCREMENT=2;
操作已执行
已用时间: 50.047(毫秒). 执行号:1264.
SQL> insert into TABLE_1(name) values('test');
影响行数 1
已用时间: 1.302(毫秒). 执行号:1265.
SQL> commit;
操作已执行
已用时间: 1.950(毫秒). 执行号:1266.
SQL> select * from table_1;
行号 ID NAME
---------- ----------- ----------
1 1 test
2 2 test
已用时间: 0.661(毫秒). 执行号:1267.
2.2、自增列值X的计算
隐式生成的自增列值X 由系统根据AUTO_INCREMENT_OFFSET 、AUTO_INCREMENT_INCREMENT 等因子自动计算得出。计算公式 X=AUTO_INCREMENT_OFFSET+n*AUTO_INCREMENT_INCREMENT。X 满足大于等于起始值且大于当前自增列值中最大值，n取最小值。
修改参数AUTO_INCREMENT_INCREMENT进行测试：
SQL> alter system set 'AUTO_INCREMENT_INCREMENT'=10;
DMSQL 过程已成功完成
已用时间: 4.947(毫秒). 执行号:610.
SQL> show parameter increment
行号 PARA_NAME PARA_VALUE
---------- ------------------------ ----------
1 AUTO_INCREMENT_INCREMENT 10
2 AUTO_INCREMENT_OFFSET 1
SQL> insert into IDENT_TABLE_1(name) values('test');
影响行数 1
已用时间: 0.464(毫秒). 执行号:612.
SQL> select * from IDENT_TABLE_1;
行号 ID NAME
---------- ----------- ----------
1 1 test
2 2 test
3 11 test
已用时间: 0.368(毫秒). 执行号:613.
SQL> insert into IDENT_TABLE_1(name) values('test');
影响行数 1
已用时间: 0.950(毫秒). 执行号:614.
SQL> select * from IDENT_TABLE_1;
行号 ID NAME
---------- ----------- ----------
1 1 test
2 2 test
3 11 test
4 21 test
已用时间: 0.250(毫秒). 执行号:615.
可以看到AUTO_INCREMENT_INCREMENT设置为10后，下一个自增列值为1+1×10=11，1+2×10=21...
3、序列方式自增
DM 官方只提供IDENTITY和AUTO_INCREMENT两种自增方式，那么我们是否可以像Oracle那样，用序列的方式实现自增呢？
3.1、使用说明
序列是一个数据库实体，通过它多个用户可以产生唯一整数值，可以用序列来自动地生成主关键字值。
语法格式：
CREATE SEQUENCE [IF NOT EXISTS] [<模式名>.]<序列名> [<序列选项列表>];
<序列选项列表> ::= <序列选项>{<序列选项>}
<序列选项> ::=
INCREMENT BY <增量值>|
START WITH <初值>|
MAXVALUE <最大值>|
NOMAXVALUE|
MINVALUE <最小值>|
NOMINVALUE|
CYCLE|
NOCYCLE|
CACHE <缓存值>|
NOCACHE|
ORDER |
NOORDER |
GLOBAL |
LOCAL
1.<模式名> 指明被创建的序列属于哪个模式，缺省为当前模式；
2.<序列名> 指明被创建的序列的名称，序列名称最大长度 128 字节；
3.<增量值> 指定序列数之间的间隔，这个值可以是[-9223372036854775808，9223372036854775807]之间任意的 DM 正整数或负整数，但不能为 0。如果此值为负，序列是下降的，如果此值为正，序列是上升的。如果忽略 INCREMENT BY 子句，则间隔缺省为 1。增量值的绝对值必须小于等于(<最大值> - <最小值>)；
4.<初值> 指定被生成的第一个序列数，可以用这个选项来从比最小值大的一个值开始升序序列或比最大值小的一个值开始降序序列。对于升序序列，缺省值为序列的最小值，对于降序序列，缺省值为序列的最大值；
5.<最大值> 指定序列能生成的最大值，如果忽略 MAXVALUE 子句，则降序序列的最大值缺省为-1，升序序列的最大值缺省为 9223372036854775807(0x7FFFFFFFFFFFFFFF)，若指定的最大值超出缺省最大值，则 DM 自动将最大值置为缺省最大值。非循环序列在到达最大值之后，将不能继续生成序列数；
6.<最小值> 指定序列能生成的最小值，如果忽略 MINVALUE 子句，则升序序列的最小值缺省为 1，降序序列的最小值缺省为-9223372036854775808(0x8000000000000000)，若指定的最小值超出缺省最小值，则 DM 自动将最小值置为缺省最小值。循环序列在到达最小值之后，将不能继续生成序列数。最小值必须小于最大值；
7.CYCLE 该关键字指定序列为循环序列：当序列的值达到最大值/最小值时，序列将从最小值/最大值计数；
8.NOCYCLE 该关键字指定序列为非循环序列：当序列的值达到最大值/最小值时，序列将不再产生新值；
9.CACHE 该关键字表示序列的值是预先分配，并保持在内存中，以便更快地访问；<缓存值>
10.NOCACHE 该关键字表示序列的值是不预先分配；
11.ORDER 该关键字表示以保证请求顺序生成序列号；
12.NOORDER 该关键字表示不保证请求顺序生成序列号；
13.GLOBAL 该关键字表示 MPP 环境下序列为全局序列，缺省为 GLOBAL；
14.LOCAL 该关键字表示 MPP 环境下序列为本地序列。
3.2、举例测试
对DM中序列进行测试，新建一个序列：
SQL> create sequence SEQ_TEST increment by 10;
操作已执行
已用时间: 5.170(毫秒). 执行号:624.
将序列作为列的默认值：
SQL> create table IDENT_TABLE_2(id number default SEQ_TEST.nextval,name varchar2(20));
操作已执行
已用时间: 7.018(毫秒). 执行号:625.
插入数据：
SQL> insert into IDENT_TABLE_2(name) values('test');
影响行数 1
已用时间: 0.948(毫秒). 执行号:626.
SQL> select * from IDENT_TABLE_2;
行号 ID NAME
---------- -- ----
1 1 test
已用时间: 0.635(毫秒). 执行号:627.
SQL> insert into IDENT_TABLE_2(name) values('test');
影响行数 1
已用时间: 0.569(毫秒). 执行号:628.
SQL> select * from IDENT_TABLE_2;
行号 ID NAME
---------- -- ----
1 1 test
2 11 test
已用时间: 0.215(毫秒). 执行号:629.
可以看到，DM中可以用序列实现自增列。
对于序列方式实现的自增，自增列的值取决于序列下一个值，对表数据的删除，不影响序列的nextval值。
SQL> delete from IDENT_TABLE_2 where id=11;
影响行数 1
已用时间: 37.399(毫秒). 执行号:1278.
SQL> select seq_test.nextval;
行号 NEXTVAL
---------- --------------------
1 21
已用时间: 0.661(毫秒). 执行号:1279.
SQL> insert into IDENT_TABLE_2(name) values('test');
影响行数 1
已用时间: 0.235(毫秒). 执行号:1280.
SQL> commit;
操作已执行
已用时间: 1.980(毫秒). 执行号:1281.
SQL> select * from IDENT_TABLE_2;
行号 ID NAME
---------- -- ----
1 1 test
2 31 test
已用时间: 0.728(毫秒). 执行号:1282.
SQL> delete from IDENT_TABLE_2;
影响行数 2
已用时间: 0.808(毫秒). 执行号:1283.
SQL> insert into IDENT_TABLE_2(name) values('test');
影响行数 1
已用时间: 0.384(毫秒). 执行号:1284.
SQL> select * from IDENT_TABLE_2;
行号 ID NAME
---------- -- ----
1 41 test
已用时间: 0.272(毫秒). 执行号:1285.
这里需要注意的是，与前面介绍的不同，当我们truncate表时，自增列的起始值不会进行初始化。
SQL> truncate table IDENT_TABLE_2;
操作已执行
已用时间: 9.940(毫秒). 执行号:1286.
SQL> insert into IDENT_TABLE_2(name) values('test');
影响行数 1
已用时间: 0.610(毫秒). 执行号:1287.
SQL> commit;
操作已执行
已用时间: 2.503(毫秒). 执行号:1288.
SQL> select * from IDENT_TABLE_2;
行号 ID NAME
---------- -- ----
1 51 test
已用时间: 0.287(毫秒). 执行号:1289.
对自增列的手动更改取决于序列的步长和当前值的修改，这里不再做详细测试。
作者
文章
阅读量
获赞
联系客服
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1