设为首页 收藏本站
查看: 1077|回复: 0

[经验分享] DB2关于标识列(自增列)的对比试验、使用示例

[复制链接]

尚未签到

发表于 2016-11-18 03:06:25 | 显示全部楼层 |阅读模式
试验环境:
         建表t1、t2分别含有不同类型的标识列id,对比不同点

D:/>db2 connect to test user cwgladm using cwglpass
   数据库连接信息
数据库服务器         = DB2/NT 8.2.9
SQL 授权标识         = CWGLADM
本地数据库别名       = TEST

D:/>db2 select char(TABSCHEMA,20),char(TABNAME,30),char(COLNAME,20),GENERATED from syscat.columns where IDENTITY='Y'
1                    2                              3                    GENERATED
-------------------- ------------------------------ -------------------- ---------
  0 条记录已选择。

D:/>db2 create table t1(id bigint not null generated by default as identity ^
More? (start with 1,increment by 1) primary key, ^
More? name varchar(10))
DB20000I  SQL 命令成功完成。
D:/>db2 create table t2(id bigint not null generated always as identity ^
More? (start with 1,increment by 1) primary key, ^
More? name varchar(10))
DB20000I  SQL 命令成功完成。
D:/>db2 select char(TABSCHEMA,20),char(TABNAME,30),char(COLNAME,20),GENERATED from syscat.columns where IDENTITY='Y'
1                    2                              3                    GENERATED
-------------------- ------------------------------ -------------------- ---------
CWGLADM              T1                             ID                   D
CWGLADM              T2                             ID                   A
  2 条记录已选择。

下面分别对这两种类型的标识列做试验,对于generated by default

D:/>
D:/>db2 insert into t1 (name) values('id1'),('id2') -- 可以自动生成标识列的值 注意此时标识列的下一个值为3
DB20000I  SQL 命令成功完成。
D:/>db2 select * from t1
ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
  2 条记录已选择。

D:/>db2 insert into t1 values(4,'id4'),(5,'id5')   -- 也可以手工输入标识列的值
DB20000I  SQL 命令成功完成。
D:/>db2 select * from t1
ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   4 id4
                   5 id5
  4 条记录已选择。

D:/>db2 insert into t1 (name) values('id3')       -- 再次使用标识列自动生成值(id=3),此时id将由3自动增长为4
DB20000I  SQL 命令成功完成。
D:/>db2 insert into t1 (name) values('test')      -- 再次使用标识列自动生成值时报错,因为主键已经有 id=4 的值了。
DB21034E  该命令被当作 SQL
语句来处理,因为它不是有效的“命令行处理器”命令。在 SQL 处理期间,它返回:
SQL0803N  INSERT 语句、UPDATE 语句或由 DELETE
语句导致的外键更新中的一个或多个值无效,因为由 "1"
标识的主键、唯一约束或者唯一索引将表
"CWGLADM.T1"的那些列限制为不能具有重复行。  SQLSTATE=23505
D:/>db2 insert into t1 (name) values('test')      -- 再次使用标识列自动生成值时报错,因为主键已经有 id=5 的值了。
DB21034E  该命令被当作 SQL
语句来处理,因为它不是有效的“命令行处理器”命令。在 SQL 处理期间,它返回:
SQL0803N  INSERT 语句、UPDATE 语句或由 DELETE
语句导致的外键更新中的一个或多个值无效,因为由 "1"
标识的主键、唯一约束或者唯一索引将表
"CWGLADM.T1"的那些列限制为不能具有重复行。  SQLSTATE=23505
D:/>db2 insert into t1 (name) values('test')     -- 再次使用标识列自动生成值时成功,说明即使插入语句失败序列也会自增。
DB20000I  SQL 命令成功完成。
D:/>db2 insert into t1 (name) values('ttt')
DB20000I  SQL 命令成功完成。
D:/>db2 select * from t1
ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   4 id4
                   5 id5
                   3 id3
                   6 test
                   7 ttt
  7 条记录已选择。

D:/>db2 insert into t1 values(9,'id9'),(10,'id10')  
DB20000I  SQL 命令成功完成。
D:/>db2 select * from t1
ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   4 id4
                   5 id5
                   3 id3
                   6 test
                   7 ttt
                   9 id9
                  10 id10
  9 条记录已选择。

D:/>db2 insert into t1 (name) values('id8')
DB20000I  SQL 命令成功完成。
D:/>db2 alter table t1 alter id restart with 11 --可以手工重置标识列的起始值,以免自增的值与已插入的主键(id=9,10)冲突  
DB20000I  SQL 命令成功完成。
D:/>db2 insert into t1 (name) values('id11')
DB20000I  SQL 命令成功完成。
D:/>db2 insert into t1 (name) values('id12')
DB20000I  SQL 命令成功完成。
D:/>db2 select * from t1
ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   4 id4
                   5 id5
                   3 id3
                   6 test
                   7 ttt
                   9 id9
                  10 id10
                   8 id8
                  11 id11
                  12 id12
  12 条记录已选择。

D:/>
D:/>db2 update t1 set id=15 where id=12   -- 再次说明这种类型的标识列的值是可以修改的
DB20000I  SQL 命令成功完成。

再来看看generated always

D:/>
D:/>db2 insert into t2 (name) values('id1'),('id2')
DB20000I  SQL 命令成功完成。
D:/>db2 select * from t2
ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
  2 条记录已选择。

D:/>db2 insert into t2 values(4,'id4'),(5,'id5')   --  这种类型的标识列不能指定值,只能由系统生成。
DB21034E  该命令被当作 SQL
语句来处理,因为它不是有效的“命令行处理器”命令。在 SQL 处理期间,它返回:
SQL0798N  不能对定义为 GENERATED ALWAYS 的列 "ID" 指定值。  SQLSTATE=428C9
D:/>db2 alter table t2 alter id restart with 5     --  可以重置起始值
DB20000I  SQL 命令成功完成。
D:/>db2 insert into t2(name) values('id5'),('id6')
DB20000I  SQL 命令成功完成。
D:/>db2 select * from t2
ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   5 id5
                   6 id6
  4 条记录已选择。

D:/>db2 alter table t2 alter id restart with 3
DB20000I  SQL 命令成功完成。
D:/>db2 insert into t2(name) values('id3'),('id4')
DB20000I  SQL 命令成功完成。
D:/>db2 insert into t2(name) values('id5')    -- 自增值与已有主键值冲突
DB21034E  该命令被当作 SQL
语句来处理,因为它不是有效的“命令行处理器”命令。在 SQL 处理期间,它返回:
SQL0803N  INSERT 语句、UPDATE 语句或由 DELETE
语句导致的外键更新中的一个或多个值无效,因为由 "1"
标识的主键、唯一约束或者唯一索引将表
"CWGLADM.T2"的那些列限制为不能具有重复行。  SQLSTATE=23505
D:/>db2 select * from t2
ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   5 id5
                   6 id6
                   3 id3
                   4 id4
  6 条记录已选择。

D:/>db2 insert into t2(name) values('test')
DB21034E  该命令被当作 SQL
语句来处理,因为它不是有效的“命令行处理器”命令。在 SQL 处理期间,它返回:
SQL0803N  INSERT 语句、UPDATE 语句或由 DELETE
语句导致的外键更新中的一个或多个值无效,因为由 "1"
标识的主键、唯一约束或者唯一索引将表
"CWGLADM.T2"的那些列限制为不能具有重复行。  SQLSTATE=23505
D:/>db2 insert into t2(name) values('test')   -- 说明即使插入语句失败,标识列仍然自增
DB20000I  SQL 命令成功完成。
D:/>db2 select * from t2
ID                   NAME
-------------------- ----------
                   1 id1
                   2 id2
                   5 id5
                   6 id6
                   3 id3
                   4 id4
                   7 test
  7 条记录已选择。

D:/>db2 update t2 set id=9 where id=7          -- 再次确认这种类型标识列的值不可修改,只能有系统生成。
DB21034E  该命令被当作 SQL
语句来处理,因为它不是有效的“命令行处理器”命令。在 SQL 处理期间,它返回:
SQL0798N  不能对定义为 GENERATED ALWAYS 的列 "ID" 指定值。  SQLSTATE=428C9

D:/>

小结:
        自增列有两种类型:generated by default、generated always
相同点:1、即使插入语句失败,标识列仍然自增
        2、都可以重置起始值,语句:alter table [table_name] alter [col_name] restart with [x]
不同点:generated by default:可以修改,手工指定标识列的值。
        generated always    :不可修改,只能由系统生成。

注意事项:
        1、因上述标识列的特性在数据迁移时要特别小心处理!
           要仔细分析导入数据与目标库表里的标识列的当前值,以免主键冲突;
           如果改变导入数据的标识列,更要考虑导入表子表外键同步更新,所以要研究透彻再动手。
           generated by default ... ,该约束性弱,允许重置起始值,在导入数据时注意重新设置目标表的自增列的起始值,
           如自增列不连续,就麻烦了,要分段设置导数据,恶梦...

           generated always ...约束性强,其值只允许系统根据其定义自动生成,在导入数据时,可以根据实际情况重置起始值后,
           再指定"忽略"选项,即modified by IDENTITYIGNORE。
           补充:
           identityignore 选项还是由系统根据标识列的定义自动生成。应该用LOAD程序的identityoverride选项来导入文件里的内容,
           而不是有系统生成这些值,主要是外键关系的影响。


        2、在新项目的数据库设计过程,强烈建议用sequence代替标识列!

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-301711-1-1.html 上篇帖子: db2数据库导入导出及表结构导入导出(db2look、move) 下篇帖子: DB2 日常维护汇总
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表