ITPub博客

首页 > 数据库 > Oracle > Oracle12c新特性之自增列的实现

Oracle12c新特性之自增列的实现

原创 Oracle 作者:水逸冰 时间:2018-11-20 20:37:35 0 删除 编辑

一. Mysql 自增列

众所周知, mysql 有自增列功能。

Mysql 中每张表只能有一个自增列,并且自增列必须是主键或者唯一键。

mysql> create table test2(id int not   null auto_increment);

ERROR 1075 (42000): Incorrect table   definition; there can be only one auto column and it must be defined as a key

mysql> create table test2(id int not   null auto_increment primary key);

Query OK, 0 rows affected (0.01 sec)

 

Mysql 插入空值,可以看到是允许的,默认会用自增列值插入。

mysql> insert into test2   values();    

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test2;

+----+

| id |

+----+

|    1 |

+----+

1 row in set (0.00 sec)

 

Mysql 也是允许插入的值不连续

mysql> insert into test2 values(3);

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test2;       

+----+

| id |

+----+

|    1 |

|    3 |

+----+

2 rows in set (0.00 sec)

 

表加一列,显式插入新增列,自增列会隐式地从当前最大值自增。

mysql> insert into test2(b) values(4);

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test2;

+----+------+

| id | b    |

+----+------+

|    1 | NULL |

|    3 | NULL |

|    4 |    4 |

+----+------+

3 rows in set (0.00 sec)

 

二. Oracle 自增列

Oracle 12c 之前的版本,自增列功能只能通过序列 + 触发器的方式实现。 12c 版本终于迎来了这个新功能。

 

自增列语法

 

建表

SQL> CREATE TABLE test1 (

    2  id int GENERATED ALWAYS AS IDENTITY

    3  );

 

Table created.

SQL> desc test1

 Name                                        Null?    Type

 -----------------------------------------   -------- ----------------------------

 ID                                        NOT   NULL NUMBER(38)

自增列会自动加上 not null 约束。

 

此时会自动生成一个序列

SQL> select   SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CACHE_SIZE from dba_sequences   where SEQUENCE_OWNER='MING';

 

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE

------------------------------ ----------   ---------- ------------ ----------

ISEQ$$_45410                            1   1.0000E+28            1         20

SQL> set line 150

SQL> select   TABLE_NAME,COLUMN_NAME,DATA_DEFAULT from dba_tab_columns where   TABLE_NAME='TEST1' and COLUMN_NAME='ID';

 

TABLE_NAME                     COLUMN_NAME                    DATA_DEFAULT

------------------------------   ------------------------------   --------------------------------------------------

TEST1                          ID                               "MING"."ISEQ$$_45410".nextval

注意下面这两个动作

SQL> select ISEQ$$_45410.nextval from   dual;

 

     NEXTVAL

----------

           1

 

SQL> select ISEQ$$_45410.nextval from   dual;

 

     NEXTVAL

----------

           2

 

 

插入一个值

 

SQL> insert into test1 values(1);

insert into test1 values(1)

*

ERROR at line 1:

ORA-32795: cannot insert into a generated   always identity column

不能向自增列插入值。

 

SQL> alter table test1 add b int;

 

Table altered.

 

SQL> insert into test1(b) values(1);

 

1 row created.

 

SQL> select * from test1;

 

          ID          B

---------- ----------

           3          1

可以看到 ID 列插入了值,但是不是 1 ,而是 3. 因为之前手动 select 从序列中取过值。

 

经过上面实验不难猜测,下面语句会报错

SQL> insert into test1(id,b) values(4,1);

insert into test1(id,b) values(4,1)

                  *

ERROR at line 1:

ORA-32795: cannot insert into a generated   always identity column

 

Update delete

SQL> update test1 set id=5;

update test1 set id=5

                 *

ERROR at line 1:

ORA-32796: cannot update a generated   always identity column

 

SQL> delete from test1;

 

1 row deleted.

 

SQL> commit;

 

Commit complete.

Update 不可以,但是 delete 是可以的。

 

可以用 default 指代

SQL> insert into test1 values( default ,1);

 

1 row created.

 

SQL> select * from test1;

 

          ID          B

---------- ----------

           5          1

 

 

SQL> commit;

 

Commit complete.

 

 

生成的序列其他用户也是可以用的

SQL> insert into tx(a)   values(ISEQ$$_45410.nextval);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

 

 

这个序列是不能删除的。

SQL> drop sequence ming.ISEQ$$_45410;

drop sequence ming.ISEQ$$_45410

                   *

ERROR at line 1:

ORA-32794: cannot drop a system-generated   sequence

 

SQL> drop table test1;

 

Table dropped.

 

SQL> select ISEQ$$_45410.nextval from   dual;

 

     NEXTVAL

----------

           6

 

Purge 回收站后就没有了。

SQL> purge recyclebin;

 

Recyclebin purged.

 

所以在删除表时可以加 purge ,加 cascade constraint 也不会马上删除序列。

 

 

SQL> CREATE TABLE test2 (

    2  id int GENERATED BY DEFAULT ON NULL AS IDENTITY

    3  );

 

Table created.

 

SQL> insert into test2 values('');

 

1 row created.

 

SQL> select * from test2;

 

          ID

----------

           1

 

Insert 可以指定值,但是指定的值不会被序列识别,换句话说,如果手动插入值 2 ,序列的下一个值是 2 ,那么在此插入空的时候,会插入 2 ,而不是 3.

 

SQL> insert into test2 values(4);

 

1 row created.

 

SQL> insert into test2 values(3);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select ISEQ$$_45418.nextval from   dual;

 

     NEXTVAL

----------

           3

 

SQL> select * from test2;

 

          ID

----------

           1

           2

           4

           3

 

SQL> insert into test2 values('');

 

1 row created.

 

SQL> select * from test2;

 

          ID

----------

           1

           2

           4

           3

           4

 

 

下面这个 Update 的动作有点意思,当前表上有 2 ID=4 的值,用 default 更新的时候会用序列赋予不同的值。

 

SQL> update test2 set id=default where   id=4;

 

2 rows updated.

 

SQL> select * from test2;

 

          ID

----------

           1

           2

           5

           3

           6

 

既然已经知道,自增列是用序列创建的,那么下面两种方式也是可以的

create sequence ming$seq01 minvalue 1   maxvalue 9999999 start with 1 increment by 2   cache 20 nocycle;

create table test3(id int default   ming$seq01.nextval);

 

create table test3(id number);

alter table test3 modify id number   default seq_1.nextval;

区别在于是插入 null 的时候不会变成数字,该列也没有 not null 约束。

 

SQL> insert into test3 values(null);

 

1 row created.

 

SQL> select * from test3;

 

          ID

----------

 

 

 

总结一下:

l   Oracle 自增列是利用序列做到的。自增列会自动加上非空约束;

l   表删除的时候,序列不会马上删除,这是因为删除的表会进入回收站,要关联删除需要加 purge

l   GENERATED ALWAYS AS IDENTITY 可以 delete ,不能显示 insert ,不能 update

l   GENERATED BY DEFAULT ON NULL AS IDENTITY 会自动将 null 值插入序列中的值,增删改都可以,相比 GENERATED ALWAYS AS IDENTITY 更加灵活,但是列的唯一性不能保证。

l   受自增列启发,可以自己创建序列,指定为表列的默认值。

l   系统自建序列的属性不能更改,可以在创建自增列的时候手动修改,否则较小 cache 默认值,会造成性能问题。 Like this:

CREATE TABLE test4 (id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10 cache 100 ));

l   其他用户如果要向带有自增列表中插入数据的话,那么需要序列权限


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31480688/viewspace-2220870/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
精通oracle,mysql和linux,热衷于研究数据库,擅长shell和Python自动化运维。VX:18302174682

注册时间:2017-08-05

  • 博文量
    104
  • 访问量
    119654