前言

本文將基于以下三種關系型資料庫，對 sequence (序列) 展開討論，

Oracle - 應用最廣泛的商用關系型資料庫

PostgreSQL - 功能最強大的開源關系型資料庫

MySQL - 應用最廣泛的開源關系型資料庫

sequence 適用場景

主鍵

用于整型主鍵資料的生成，一般一個 sequence 僅用于一張表的主鍵，這是最常用的用途，

本文討論的主要是此用途，

非主鍵

只使用 sequence 本身自增的功能，可多表共用一個 sequence，或整個資料庫共用一個 sequence，

sequence 不適用的場景

對于要求實際的值一定是連續的(如1,2,3,4,5)，sequence 則不適用，

首先，sequence 生成時是連續的，但由于其生成的值會丟失或被消耗掉等原因，從而導致實際使用時不一定是連續的，

sequence 用法一顯式呼叫

這種方式是單獨創建 sequence 和表，在 INSERT 等陳述句中顯式呼叫 sequence，

如下示例，

Oracle

SQL> CREATE SEQUENCE seq_test;

Sequence created.

SQL> CREATE TABLE tb_test (
    test_id NUMBER PRIMARY KEY
);  2    3  

Table created.

SQL> INSERT INTO tb_test (test_id) VALUES (seq_test.nextval);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM tb_test ORDER BY 1 DESC;

   TEST_ID
----------
	 1

PostgreSQL

如下示例，PostgreSQL 的 SQL 與 Oracle 的 SQL 很類似，

$ psql -U alvin -d alvindb
psql (11.9)
Type "help" for help.

alvindb=> CREATE SEQUENCE seq_test;
CREATE SEQUENCE
alvindb=> CREATE TABLE tb_test (
alvindb(>     test_id INTEGER PRIMARY KEY
alvindb(> );
CREATE TABLE
alvindb=> INSERT INTO tb_test (test_id) VALUES (nextval('seq_test'));
INSERT 0 1
alvindb=> SELECT * FROM tb_test ORDER BY 1 DESC;
 test_id 
---------
       1
(1 row)

MySQL

MySQL 不支持單獨創建sequence，參考用法四 AUTO INCREMENT 中 MySQL 部分，

sequence 用法二觸發器中呼叫

是否可以在 INSERT 陳述句中不顯式呼叫 sequence，而使其自動呼叫呢？

當然可以！通常有三種方法，一是通過觸發器實作，二是在 DEFAULT 中呼叫sequence，三是通過 AUTO INCREMENT 方式，

我們先來看一下如何在觸發器中實作，

可以在表的 BEFORE INSERT 觸發器中，呼叫 sequence，從而達到在插入前自動給主鍵賦值，這樣，在 INSERT 中就不需要顯式呼叫 sequence 了，

Oracle

SQL> CREATE SEQUENCE seq_test2;

Sequence created.

SQL> CREATE TABLE tb_test2 (
    test_id NUMBER PRIMARY KEY,
    test_order NUMBER
);  2    3    4  

Table created.

SQL> CREATE OR REPLACE TRIGGER trg_b_ins_tb_test2
  BEFORE INSERT ON tb_test2
  FOR EACH ROW
BEGIN
  SELECT seq_test2.nextval
  INTO :new.test_id
  FROM dual;
END;  2    3    4    5    6    7    8  
  9  /

Trigger created.

SQL> INSERT INTO tb_test2 (test_order) VALUES (1);                

1 row created.

SQL> SELECT * FROM tb_test2 ORDER BY 2 DESC;

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

下面測驗表明，當在 INSERT 中指定列 test_id 為 NULL 時，會從 sequence 中取值，但這是 trigger 的原理決定的，與傳入的值是否為 NULL 無關，

SQL> INSERT INTO tb_test2 (test_id,test_order) VALUES (NULL,2);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM tb_test2 ORDER BY 2 DESC;

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

PostgreSQL

如下示例，PostgreSQL 的 SQL 與 Oracle 的 SQL 也很類似，觸發器的創建方式略有差異，

alvindb=> CREATE SEQUENCE seq_test2;
CREATE SEQUENCE
alvindb=> CREATE TABLE tb_test2 (
alvindb(>     test_id INTEGER PRIMARY KEY,
alvindb(>     test_order INTEGER
alvindb(> );
CREATE TABLE
alvindb=> CREATE OR REPLACE FUNCTION trgf_b_ins_tb_test2()
alvindb-> RETURNS TRIGGER AS
alvindb-> $$
alvindb$> BEGIN
alvindb$>     NEW.test_id := nextval('seq_test2');
alvindb$>     RETURN NEW;
alvindb$> END;
alvindb$> $$
alvindb-> LANGUAGE 'plpgsql';
CREATE FUNCTION
alvindb=> CREATE TRIGGER trg_b_ins_tb_test2
alvindb->     BEFORE INSERT ON tb_test2
alvindb->     FOR EACH ROW
alvindb->     EXECUTE PROCEDURE trgf_b_ins_tb_test2();
CREATE TRIGGER
alvindb=> \d+ tb_test2
                                   Table "public.tb_test2"
   Column   |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
------------+---------+-----------+----------+---------+---------+--------------+-------------
 test_id    | integer |           | not null |         | plain   |              | 
 test_order | integer |           |          |         | plain   |              | 
Indexes:
    "tb_test2_pkey" PRIMARY KEY, btree (test_id)
Triggers:
    trg_b_ins_tb_test2 BEFORE INSERT ON tb_test2 FOR EACH ROW EXECUTE PROCEDURE trgf_b_ins_tb_test2()
alvindb=> INSERT INTO tb_test2 (test_order) VALUES (1);
INSERT 0 1
alvindb=> SELECT * FROM tb_test2 ORDER BY 2 DESC;
 test_id | test_order 
---------+--------------
       1 |            1
(1 row)

下面測驗表明，同 Oracle 中一樣，當在 INSERT 中指定列 test_id 為 NULL 時，同樣，這也是 trigger 的原理決定的，與傳入的值是否為 NULL 無關，

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

MySQL

MySQL 不支持單獨創建sequence，參考用法四 AUTO INCREMENT 中 MySQL 部分，

sequence 用法三 DEFAULT 中呼叫

看完上面的用法，我們不禁感覺，創建觸發器有有點麻煩，

有沒有簡單用法呢，手動創建完 sequence 后，一句話就可以呼叫的那種？

當然，就是在 DEFAULT 呼叫 sequence！

Oracle

以下為 Oracle 中代碼示例，

Oracle Database 11g Release 11.2.0.4.0

先在 Oracle 11g 中試一下，

SQL> CREATE SEQUENCE seq_test3;                              

Sequence created.

SQL> CREATE TABLE tb_test3 (
    test_id NUMBER DEFAULT seq_test3.nextval PRIMARY KEY,
    test_order NUMBER
);  2    3    4  
    test_id NUMBER DEFAULT seq_test3.nextval PRIMARY KEY,
                            *
ERROR at line 2:
ORA-00984: column not allowed here

什么？報錯！這是為什么呢？

根據 Oracle 官方檔案，原來在 Oracle 11g 中這種用法不支持，想要實作類似功能，只能用 trigger 了，

Restriction on Default Column Values
A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

Oracle Database 12c Release 12.2.0.1.0

在 Oracle 12c 中 DEFAULT 中呼叫 sequence 是可以的，

SQL> CREATE SEQUENCE seq_test3;

Sequence created.

SQL> CREATE TABLE tb_test3 (
    test_id NUMBER DEFAULT seq_test3.nextval PRIMARY KEY,
    test_order NUMBER
);  2    3    4  

Table created.

SQL> INSERT INTO tb_test3 (test_id,test_order) VALUES (seq_test3.nextval,1);

1 row created.

SQL> INSERT INTO tb_test3 (test_id,test_order) VALUES (DEFAULT,2);

1 row created.

SQL> INSERT INTO tb_test3 (test_order) VALUES (3);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM tb_test3 ORDER BY 2 DESC;

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

通過如下 SQL 可查詢資料字典中表列的 DEFAULT

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_TEST3';

TABLE_NAME		       COLUMN_NAME		      DATA_DEFAULT
------------------------------ ------------------------------ ------------------------------
TB_TEST3		       TEST_ID			      "TEST"."SEQ_TEST3"."NEXTVAL"
TB_TEST3		       TEST_ORDER

那么在表列的 DEFAULT 中呼叫了 sequence 后，sequence 可以被洗掉嗎？

SQL> DROP SEQUENCE seq_test3;

Sequence dropped.

可以看到，DEFAULT 中的 sequence 可以被洗掉，

那么洗掉 sequence 后表列的 DEFAULT 變不變呢？再插入資料會怎么樣呢？

如下示例，洗掉 sequence 后再插入資料，洗掉 sequence 后表列的 DEFAULT 不變！但再插入資料時會報錯，

SQL> SELECT table_name,column_name,data_default FROM user_tab_columns WHERE table_name = 'TB_TEST3';

TABLE_NAME		       COLUMN_NAME		      DATA_DEFAULT
------------------------------ ------------------------------ ------------------------------
TB_TEST3		       TEST_ID			      "TEST"."SEQ_TEST3"."NEXTVAL"
TB_TEST3		       TEST_ORDER

SQL> 
SQL> INSERT INTO tb_test3 (test_order) VALUES (5);
INSERT INTO tb_test3 (test_order) VALUES (5)
       *
ERROR at line 1:
ORA-02289: sequence does not exist

PostgreSQL

在 PostgreSQL 中同樣可以，PostgreSQL 的 SQL 與 Oracle 的 SQL 依然很類似，

alvindb=> CREATE SEQUENCE seq_test3;
CREATE SEQUENCE
alvindb=> CREATE TABLE tb_test3 (
alvindb(>     test_id INTEGER DEFAULT nextval('seq_test3') PRIMARY KEY,
alvindb(>     test_order INTEGER
alvindb(> );
CREATE TABLE
alvindb=> INSERT INTO tb_test3 (test_id,test_order) VALUES (nextval('seq_test3'),1);
INSERT 0 1
alvindb=> INSERT INTO tb_test3 (test_id,test_order) VALUES (DEFAULT,2);
INSERT 0 1
alvindb=> INSERT INTO tb_test3 (test_order) VALUES (3);
INSERT 0 1
alvindb=> SELECT * FROM tb_test3 ORDER BY 2 DESC;
 test_id | test_order 
---------+--------------
       3 |            3
       2 |            2
       1 |            1
(3 rows)

我們嘗試 DROP 一下 sequence，

從下面的示例中可以看出，DEFAULT 中的 sequence 可以洗掉，同時也會提示，表列的 DEFAULT 也被洗掉了，這個是十分友好的，

alvindb=> CREATE SEQUENCE seq_test3;
CREATE SEQUENCE
alvindb=> CREATE TABLE tb_test3 (
alvindb(>     test_id INTEGER DEFAULT nextval('seq_test3') PRIMARY KEY,
alvindb(>     test_order INTEGER
alvindb(> );
CREATE TABLE
alvindb=> \d+ tb_test3
                                               Table "public.tb_test3"
   Column   |  Type   | Collation | Nullable |            Default             | Storage | Stats target | Description 
------------+---------+-----------+----------+--------------------------------+---------+--------------+-------------
 test_id    | integer |           | not null | nextval('seq_test3'::regclass) | plain   |              | 
 test_order | integer |           |          |                                | plain   |              | 
Indexes:
    "tb_test3_pkey" PRIMARY KEY, btree (test_id)
alvindb=> DROP SEQUENCE seq_test3;
ERROR:  cannot drop sequence seq_test3 because other objects depend on it
DETAIL:  default value for column test_id of table tb_test3 depends on sequence seq_test3
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
alvindb=> DROP SEQUENCE tb_test4_test_id_seq CASCADE;
NOTICE:  drop cascades to default value for column test_id of table tb_test4
DROP SEQUENCE

剛才提到，在 Oracle 中，這個用法是從 Oracle 12c 中才開始支持的，

那么 PostgreSQL 是哪個版本開始支持的呢？

PostgreSQL 官網檔案中列出的最早的版本是 PostgreSQL 7.1（7.1 之前的檔案官網中未列出），在這個檔案中，已支持這種用法，

這就 PostgreSQL 7.1 檔案中的例子

CREATE TABLE distributors (
    name     VARCHAR(40) DEFAULT 'luso films',
    did      INTEGER  DEFAULT NEXTVAL('distributors_serial'),
    modtime  TIMESTAMP DEFAULT now()
);

Oracle 和 PostgreSQL 這些版本是什么時候發布的呢？

根據 PostgreSQL 官網， PostgreSQL Release 7.1.3 是 2001-08-15，

根據 Wikipedia， Oracle Database 12c Release 1 是 July 2014 發布的，

即 PostgreSQL 2001 年已支持 sequence 的 DEFAULT nextval 用法，十三年后，Oracle 也支持了，

MySQL

MySQL 不支持單獨創建sequence，參考用法四 AUTO INCREMENT 中 MySQL 部分，

公眾號

關注 DBA Daily 公眾號，第一時間收到文章的更新，
通過一線 DBA 的日常作業，學習實用資料庫技術干貨！

公眾號優質文章推薦

PostgreSQL VACUUM 之深入淺出

華山論劍之 PostgreSQL sequence

[PG Upgrade Series] Extract Epoch Trap

[PG Upgrade Series] Toast Dump Error

GitLab supports only PostgreSQL now

MySQL or PostgreSQL?

PostgreSQL hstore Insight

ReIndex 失敗原因調查

PG 資料匯入 Hive 亂碼問題調查

PostGIS 擴展創建失敗原因調查

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/436375.html

標籤：其他

上一篇：高性能圖計算系統 Plato 在 Nebula Graph 中的實踐

下一篇：Clickhouse 分布式表&本地表 &ClickHouse實作時序資料管理和挖掘

三大資料庫 sequence 之華山論劍 (上篇)

前言

sequence 適用場景

主鍵

非主鍵

sequence 不適用的場景

sequence 用法一 顯式呼叫

Oracle

PostgreSQL

MySQL

sequence 用法二 觸發器中呼叫

Oracle

PostgreSQL

MySQL

sequence 用法三 DEFAULT 中呼叫

Oracle

Oracle Database 11g Release 11.2.0.4.0

Oracle Database 12c Release 12.2.0.1.0

PostgreSQL

MySQL

公眾號

公眾號優質文章推薦

sequence 用法一顯式呼叫

sequence 用法二觸發器中呼叫