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

本文将基于以下三种关系型数据库，对 sequence (序列) 展开讨论。

Oracle - 应用最广泛的商用关系型数据库

PostgreSQL - 功能最强大的开源关系型数据库

MySQL - 应用最广泛的开源关系型数据库

主键

用于整型主键数据的生成，一般一个 sequence 仅用于一张表的主键。这是最常用的用途。

本文讨论的主要是此用途。

非主键

只使用 sequence 本身自增的功能，可多表共用一个 sequence，或整个数据库共用一个 sequence。

对于要求实际的值一定是连续的(如1,2,3,4,5)，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 部分。

是否可以在 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！

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.cnblogs.com/dbadaily/archive/2022/03/02/sequence1.html