三大数据库 sequence 之华山论剑 (四)

sequence 用法四 AUTO INCREMENT

通过 DEFAULT 还是需要手动创建 sequence。有没有更简单的用法呢？

当然，就是通过 AUTO INCREMENT 方式，自动创建 sequence，并且自动在 DEFAULT 中调用！

Oracle

同样，Oracle 也是 12c 开始支持 AUTO INCREMENT。

以下测试是在 Oracle Database 12c Release 12.2.0.1.0 中进行的。

SQL> CREATE TABLE tb_test4 (
    test_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    test_order NUMBER
);  2    3    4  

Table created.

插入测试数据，可以看到预期的结果。

SQL> INSERT INTO tb_test4 (test_id,test_order) VALUES (DEFAULT,1);

1 row created.

SQL> INSERT INTO tb_test4 (test_order) VALUES (2);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

   TEST_ID TEST_ORDER
---------- ------------
	 2	      2
	 1	      1

通过查询可以看到，系统自动创建了 一个名为 ISEQ$$_254835（Oracle 自动生成的名字一般都不太友好） 的 sequence，并将其设置为了 DEFAULT。

SQL> COL object_name FOR a30
COL object_type FOR a30SQL> 
SQL> SELECT object_name,object_type FROM user_objects;

OBJECT_NAME		       OBJECT_TYPE
------------------------------ ------------------------------
TB_TEST4		       TABLE
ISEQ$$_254835		       SEQUENCE
SQL> SET linesize 100
COL table_name FOR a30
COL column_name FOR a30
COL data_default FOR a30
SQL> SELECT table_name,column_name,data_default FROM user_tab_columns WHERE table_name = 'TB_TEST4';

TABLE_NAME		       COLUMN_NAME		      DATA_DEFAULT
------------------------------ ------------------------------ ------------------------------
TB_TEST4		       TEST_ID			      "TEST"."ISEQ$$_254857".nextval
TB_TEST4		       TEST_ORDER

通过以下数据字典可以看出，自动生成的 sequence 是与列对应的。

SQL> SET linesize 200
COL table_name FOR a30
COL column_name FOR a30
COL generation FOR a30
COL sequence_name FOR a30SQL> SQL> SQL> SQL> 
SQL> SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols;

TABLE_NAME		       COLUMN_NAME		      SEQUENCE_NAME
------------------------------ ------------------------------ ------------------------------
TB_TEST4		       TEST_ID			      ISEQ$$_254835

自动生成的 sequence 是否可以删除呢？

通过如下实验，可以看到，Oracle 中系统自动生成的 sequence 不能单独删除。

SQL> DROP SEQUENCE ISEQ$$_254835;
DROP SEQUENCE ISEQ$$_254835
              *
ERROR at line 1:
ORA-32794: cannot drop a system-generated sequence

删除表会删除对应的 sequence 吗？会。表以 BIN 开头，代表 table 已被删除，但 PURGE RECYCLEBIN 后才会被彻底删除。

SQL> DROP TABLE tb_test4;

Table dropped.

SQL> SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols;

TABLE_NAME		       COLUMN_NAME		      SEQUENCE_NAME
------------------------------ ------------------------------ ------------------------------
BIN$vXiAW/32gIHgU5KhXwowkg==$0 TEST_ID			      ISEQ$$_254835

SQL> SELECT object_name,object_type FROM USER_OBJECTS;

OBJECT_NAME		       OBJECT_TYPE
------------------------------ ------------------------------
ISEQ$$_254835		       SEQUENCE

SQL> PURGE RECYCLEBIN;

Recyclebin purged.

SQL> SELECT object_name,object_type FROM USER_OBJECTS;

no rows selected

SQL> SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols;

no rows selected

下面测试表明，当在 INSERT 中指定列 test_id 为 NULL 时，会从 sequence 中取值。

这是 Oracle 中 GENERATED BY DEFAULT ON NULL 中的 ON NULL 决定的。

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

   TEST_ID TEST_ORDER
---------- ----------
	 6	    8

SQL> INSERT INTO tb_test4 (test_id,test_order) VALUES (NULL,9);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

   TEST_ID TEST_ORDER
---------- ----------
	 7	    9
	 6	    8

以上示例中使用的是 GENERATED BY DEFAULT ON NULL，其他还有 GENERATED BY DEFAULT 和 GENERATED ALWAYS 。

GENERATED BY DEFAULT 与 GENERATED BY DEFAULT ON NULL 区别是，当主键插入 NULL 值时，GENERATED BY DEFAULT 会报错，如下：

SQL> CREATE TABLE tb_test5 (
    test_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    test_order NUMBER
);   2    3    4  

Table created.

SQL> INSERT INTO tb_test5 (test_id,test_order) VALUES (NULL,1);
INSERT INTO tb_test5 (test_id,test_order) VALUES (NULL,1)
                                                  *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."TB_TEST5"."TEST_ID")

GENERATED ALWAYS 是插入时，

若主键指定值会报错：

SQL> CREATE TABLE tb_test6 (
    test_id NUMBER GENERATED ALWAYS AS IDENTITY,
    test_order NUMBER
);   2    3    4  

Table created.

SQL> INSERT INTO tb_test6 (test_id,test_order) VALUES (1,1);
INSERT INTO tb_test6 (test_id,test_order) VALUES (1,1)
                      *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

SQL> INSERT INTO tb_test6 (test_id,test_order) VALUES (NULL,2);
INSERT INTO tb_test6 (test_id,test_order) VALUES (NULL,2)
                      *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

但指定 DEFAULT 可以：

SQL> INSERT INTO tb_test6 (test_id,test_order) VALUES (DEFAULT,3);

1 row created.
SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM tb_test6 ORDER BY 2 DESC;

   TEST_ID TEST_ORDER
---------- ----------
	 1	    3

PostgreSQL

在 PostgreSQL 中同样可以，甚至更简单，SERIAL 一个单词就够了。

创建表

alvindb=> CREATE TABLE tb_test4 (
alvindb(>     test_id SERIAL PRIMARY KEY,
alvindb(>     test_order INTEGER
alvindb(> );
CREATE TABLE

插入测试数据，结果符合预期。

alvindb=> INSERT INTO tb_test4 (test_id,test_order) VALUES (DEFAULT,1);
INSERT 0 1
alvindb=> INSERT INTO tb_test4 (test_order) VALUES (2);
INSERT 0 1
alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC;
 test_id | test_order 
---------+--------------
       2 |            2
       1 |            1
(2 rows)

通过如下方式查看表结构，可以看到，加了 SERIAL 以后，自动创建了 sequence tb_test4_test_id_seq（PostgreSQL 自动生成的名字一般比较友好），并将其设置为了 DEFAULT。

alvindb=> \d+ tb_test4
                                                    Table "public.tb_test4"
   C