本文将基于以下三种关系型数据库，对 sequence (序列) 展开讨论。
Oracle - 应用最广泛的商用关系型数据库
PostgreSQL - 功能最强大的开源关系型数据库
MySQL - 应用最广泛的开源关系型数据库
sequence 适用场景 主键用于整型主键数据的生成，一般一个 sequence 仅用于一张表的主键。这是最常用的用途。
本文讨论的主要是此用途。
非主键只使用 sequence 本身自增的功能，可多表共用一个 sequence，或整个数据库共用一个 sequence。
sequence 不适用的场景对于要求实际的值一定是连续的(如1,2,3,4,5)，sequence 则不适用。
首先，sequence 生成时是连续的，但由于其生成的值会丢失或被消耗掉等原因，从而导致实际使用时不一定是连续的。
sequence 用法一 显式调用这种方式是单独创建 sequence 和表，在 INSERT 等语句中显式调用 sequence。
如下示例。
OracleSQL> 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 的 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 不支持单独创建sequence。 参考 用法四 AUTO INCREMENT 中 MySQL 部分。
sequence 用法二 触发器中调用是否可以在 INSERT 语句中不显式调用 sequence，而使其自动调用呢？
当然可以！通常有三种方法。一是通过触发器实现，二是在 DEFAULT 中调用sequence，三是通过 AUTO INCREMENT 方式。
我们先来看一下如何在触发器中实现。
可以在表的 BEFORE INSERT 触发器中，调用 sequence，从而达到在插入前自动给主键赋值。这样，在 INSERT 中就不需要显式调用 sequence 了。
OracleSQL> 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 的 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 不支持单独创建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 了。
Oracle Database 12c Release 12.2.0.1.0Restriction 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 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 的 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 也支持了。
MySQLMySQL 不支持单独创建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 扩展创建失败原因调查