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