专业北京网站建设公司哪家好,网站登录慢,凯里公司网站建设,上海网络推广软件db2设置主键自增 一、方式一#xff1a;IDENTITY设置主键自增1.1、语法一#xff1a;GENERATED BY DEFAULT AS IDENTITY1.1.1、使用1.1.2、注意事项或坑 1.2、语法二#xff1a;GENERATED ALWAYS AS IDENTITY 二、方式二#xff1a;Sequence(了解)2.1、insert时使用Sequenc… db2设置主键自增 一、方式一IDENTITY设置主键自增1.1、语法一GENERATED BY DEFAULT AS IDENTITY1.1.1、使用1.1.2、注意事项或坑 1.2、语法二GENERATED ALWAYS AS IDENTITY 二、方式二Sequence(了解)2.1、insert时使用Sequence2.2、查询下一个自增值2.3、查询上一个自增值或当前自增值2.4、查看Sequence信息 三、扩展3.1、修改列为自增长3.2、修改列自增长起始值3.3、修改SEQUENCE起始值3.4、修改SEQUENCE序列信息3.5、删除SEQUENCE 四、IDENTITY与Sequence的区别五、Sequence的原理 每种数据都有自己独特的自增列的声明方式如 Oracle 的 Sequence, SQL Server 的 Identity, MySQL 的 auto_increment, PostgreSQL 的 Sequence 或 Serial。和 PostgreSQL 类似DB2 也提供两种自增列的声明方式它们是 Sequence 和 Identity。而本文主要着墨于 DB2 的 Identity 字段并讲述它与 Sequence 的某种联系以及它对数据表的导入的影响。 一、方式一IDENTITY设置主键自增 说明IDENTITY的原理其实是一个匿名的Sequence底层被包装成了Sequence在处理。所以想要详细了解IDENTITY原理的人也可以看一下下面的Sequence内容。 identity更多内容参见官网 https://www.ibm.com/docs/en/db2/11.5?topicstatements-create-table#sdx-synid_identity-options DB2中有两种方式指定字段自增长 1.GENERATED BY DEFAULT AS IDENTITY --插入数据时允许指定自增字段的值只要不重复即可,数据库会自动设置下一个值2.GENERATED ALWAYS AS IDENTITY --不允许指定只能由数据库自动分配并插入相同点 1、即使插入语句失败标识列仍然自增(即插入失败也会自增一次)。 2、都可以重置起始值语句alter table [table_name] alter [col_name] restart with [x]
不同点 1、generated by default可以修改自增列的值,手工指定标识列的值。 2、generated always 不可修改自增列的值只能由系统生成。
1.1、语法一GENERATED BY DEFAULT AS IDENTITY
CREATE TABLE t1(id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) primary key,name VARCHAR(400),age INTEGER
);
-- start with 1id从1开始
-- increment by 1 每次自增1参数简介如下
start with指定序列的起始值默认情况下对于升序的序列是当前指定数据类型的最小值对于降序序列的是当前指定类型的最大值。increment by指定增长的值默认值为1正数表示此序列为增长升序的负数表示此序列为降序的。minvalue最小值若降序序列no cycle的话到此值的时候就不再生成序列值了no minvalue对于升序序列来说此值为start with的值如果start with值未指定的话就是1. 对于降序序列来说此值就是指定数据类型的最小值。maxvalue指定生产序列的最大值no maxvalue对于升序序列来说此值为指定数据类型的最大值 对于降序序列来说此值为start with值若未指定start with值的话就是-1.cycle循环使用数据值对于升序序列来说当达到了最大值之后下一个值将会是其最小值 对于降序序列来说达到最小之后下一个序列值为其最大值。no cycle当达到序列的边界值之后就不再产生序列值默认选项。cache缓存序列值表示每次应用此序列的时候预先生产并存放在内存中的序列值。 其作用是有效的降低了写日志的I/O操作。 若在使用的过程中出现系统错误的话那么所有这些缓存值将会丢失。 最小值为2默认为20 no cache当指定此选项的时候内存中不会存储任何序列值无论出现什么异常现象都不会影响到此序列 每次生存新的序列值都会导致写日志的I/O操作。order按照请求的顺序生成值。no order不会按照请求的顺序生成值默认情况。
1.1.1、使用
用法一不指定id值让数据库自增
insert into t1 (name,age) values(张三,11);
用法二手动指定id值
insert into t1(id,name,age) values(2,李四,12)1.1.2、注意事项或坑
注意若手动指定自增id值当id自增到该值时会报主键重复
案例描述 以上述示例为参考先插入一条数据id自增为1然后手动插入一条数据指定id为2 此时再插入一条数据不手动指定id使用数据库自增此时会报主键重复
问题分析 猜测自增的id,数据库内存中会存储当前最大值。 手动插入的不会维护在内存中导致继续按内存中的最大值进行自增所以会有主键冲突问题。
案例示例
CREATE TABLE t1(id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),name VARCHAR(400),age INTEGER,PRIMARY KEY (id)
);insert into t1 (name,age) values(张三,11);
insert into t1(id,name,age) values(2,李四,12);
insert into t1 (name,age) values(王五,13);select * from t1;报错内容
[23505][-803] One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key,
unique constraint or unique index identified by 1 constrains table TEST.T1 from having duplicate values for the index key.. SQLCODE-803, SQLSTATE23505, DRIVER4.23.42解决方法
方法一设置t1表id从指定值之后开始自增
ALTER TABLE t1 ALTER COLUMN id RESTART WITH 18;方法二重新执行一下插入语句即可。
原理第一次插入失败后自增值也会自增1所以再执行一次就跳过那个重复值了。1.2、语法二GENERATED ALWAYS AS IDENTITY
CREATE TABLE t2(id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),name VARCHAR(400),age INTEGER,PRIMARY KEY (id)
);
-- start with 1id从1开始
-- increment by 1 每次自增1使用示例
注意insert语句中不能包含id字段否则就报错。id只能由数据库自增
insert into t2 (name,age) values(张三,11);加了id后就会报以下错误
二、方式二Sequence(了解)
IBM 官方的相关文档 CREATE SEQUENCE statement Sequence的方式比Identity要稍微麻烦一些所以可以作为了解主要还是使用IDENTITY。 IDENTITY的原理其实是一个匿名的Sequence底层被包装成了Sequence在处理。 在 DB2 中声明一个 Sequence 与表的 Identity 字段的参数差不多我们可以看作 Identity 是一个内联的 Sequence。先来看如何创建一个序列
语法CREATE SEQUENCE sequence-nameAS data-type 默认 As IntegerSTART WITH numeric-constant INCREMENT BY numeric-constant 默认 INCREMENT BY 1MINVALUE numeric-constant | NO MINVALUE 默认 NO MINVALUEMAXVALUE numeric-constant | NO MAXVALUE 默认 NO MAXVALUENO CYCLE | CYCLE 默认 NO CYCLECACHE numeric-constant | NO CACHE 默认 CACHE 20NO ORDER | ORDER 默认 NO ORDER示例
CREATE SEQUENCE ORDER_SEQAS data-type BIGINTSTART WITH 1 INCREMENT BY 1 CACHE 24;参数简介如下
as datatype指定数据类型这里说明下序列的数据类型只能为数值型 如smallintintegerbigint不带小数点的decimal类型。
2.1、insert时使用Sequence
insert into t1(id,name,age) values(NEXT VALUE for ORDER_SEQ,scott,30);使用的话比如 insert, update 记录时用它的 NEXT VALUE FOR ORDER_SEQ 值我们也可以查询到它的下一个值
SELECT NEXT VALUE FOR ORDER_SEQ FROM SYSIBM.SYSDUMMY1;注意每调用一次next value 就会使用掉一个自增id值
2.2、查询下一个自增值
NEXTVAL FOR 序列
或
NEXT VALUE FOR 序列
或
select 序列名.nextval from SYSIBM.SYSDUMMY1;示例
SELECT NEXTVAL FOR ORDER_SEQ FROM SYSIBM.SYSDUMMY1;
SELECT NEXT VALUE FOR ORDER_SEQ FROM SYSIBM.SYSDUMMY1;
select ORDER_SEQ.nextval from SYSIBM.SYSDUMMY1;2.3、查询上一个自增值或当前自增值
SELECT PREVIOUS VALUE FOR ORDER_SEQ FROM SYSIBM.SYSDUMMY1;2.4、查看Sequence信息
SELECT * FROM sysibm.syssequences三、扩展
3.1、修改列为自增长 当想将表中一列修改为自动增长时可用下面命令 语法
alter table table name alter column column name set generated always as identity (start with 1,increment by 1);示例设置user表id字段为自增长
alter table user alter column id set generated always as identity (start with 1,increment by 1);3.2、修改列自增长起始值 当修改表中一列自动增长的开始值时可用下面的命令 语法
ALTER TABLE talbe_name ALTER COLUMN column name RESTART WITH 起始值;示例设置user表id字段从18开始自增
ALTER TABLE user ALTER COLUMN id RESTART WITH 18;3.3、修改SEQUENCE起始值
ALTER SEQUENCE 序列名 RESTART WITH 下一值示例
alter sequence order_seq restart with 10;3.4、修改SEQUENCE序列信息
修改最大值 ALTER SEQUENCE sequence_name MAX VALUE numeric-constant | NO MAXVALUE
修改最小值 ALTER SEQUENCE sequence_name MIN VALUE numeric-constant | NO MINVALUE 此值需要比当前值小
修改步长 ALTER SEQUENCE sequence_name INCREMENT BY numeric-constant;
修改CACHE值 ALTER SEQUENCE sequence_name CACHE numeric-constant | NO CACHE
修改循环属性: ALTER SEQUENCE sequence_name CYCLE | NO CYCLE
修改排序属性 ALTER SEQUENCE sequence_name ORDER | NO ORDER
从新计数 ALTER SEQUENCE sequence_name RESTART | RESTART WITH numeric-constant注意 1、序列的数据类型不能修改若要修改只能删除当前序列重建时指定想要的数据类型。 2、当修改的时候所有的缓存值将会丢失。 3、当将序列修改为cycle之后序列将会产生重复的值。
3.5、删除SEQUENCE
DROP SEQUENCE sequence_name;四、IDENTITY与Sequence的区别
Identity字段与sequence的最大不同在于 identity只能在一个表中使用多个表不可以共享identity;而sequence可以通过命名方式在同一个数据库内部的多个表中共享序列号发生器
五、Sequence的原理 待补充可见参考文章 参考文章 https://blog.csdn.net/davidmeng10/article/details/46362997 https://blog.itpub.net/13165828/viewspace-609640/
