暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / 三大数据库 sequence 之华山论剑

三大数据库 sequence 之华山论剑

原创 DBADaily 2022-12-20
507

前言

本文将基于以下三种关系型数据库，对 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 部分。

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"
   Column   |  Type   | Collation | Nullable |                  Default                  | Storage | Stats target | Description 
------------+---------+-----------+----------+-------------------------------------------+---------+--------------+-------------
 test_id    | integer |           | not null | nextval('tb_test4_test_id_seq'::regclass) | plain   |              | 
 test_order | integer |           |          |                                           | plain   |              | 
Indexes:
    "tb_test4_pkey" PRIMARY KEY, btree (test_id)

查看 sequence 定义看的 ‘Owned by’，可以看到，自动生成的 sequence 与对应列也是绑定的。

即 ‘Owned by’ 也代表删除表或列的时候，对应的 sequence 也会被删除。

alvindb=> \d+ tb_test4_test_id_seq
                Sequence "public.tb_test4_test_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.tb_test4.test_id

下面测试删除表后，自动生成的 sequence 也会被删除。

alvindb=> DROP TABLE tb_test4;
DROP TABLE
alvindb=> \d+ tb_test4_test_id_seq
Did not find any relation named "tb_test4_test_id_seq".
alvindb=>

从下面的例子中可以看出，删除 sequence 效果与用法三中一样，表列的 DEFAULT 也被删除了。

alvindb=> CREATE TABLE tb_test4 (
alvindb(>     test_id SERIAL PRIMARY KEY,
alvindb(>     test_order INTEGER
alvindb(> );
CREATE TABLE
alvindb=> DROP SEQUENCE tb_test4_test_id_seq;
ERROR:  cannot drop sequence tb_test4_test_id_seq because other objects depend on it
DETAIL:  default value for column test_id of table tb_test4 depends on sequence tb_test4_test_id_seq
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
alvindb=> \d+ tb_test4
                                                    Table "public.tb_test4"
   Column   |  Type   | Collation | Nullable |                  Default                  | Storage | Stats target | Description 
------------+---------+-----------+----------+-------------------------------------------+---------+--------------+-------------
 test_id    | integer |           | not null | nextval('tb_test4_test_id_seq'::regclass) | plain   |              | 
 test_order | integer |           |          |                                           | plain   |              | 
Indexes:
    "tb_test4_pkey" PRIMARY KEY, btree (test_id)

与上述用法三中手动创建 sequence 不同的是，使用 SERIAL 的话：

a. 自动生成的 sequence 与对应列是绑定的

b. 删除表后，自动生成的 sequence 也会被删除，避免无用的 sequence 的存在

根据 PostgreSQL官方文档，

CREATE TABLE tablename (
    colname SERIAL
);

与如下三个 SQL 等价：

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

下面测试表明，当在 INSERT 中指定列 test_id 为 NULL 时，会报错。

从上面 SERIAL 等价的 SQL 中可以看出，PostgreSQL 只是将上述 三个 SQL 精简为 SERIAL 关键字，与 Oracle 中 AUTO INCREMENT 本质是不同的。

alvindb=> INSERT INTO tb_test4 (test_id,test_order) VALUES (NULL,9);
ERROR:  null value in column "test_id" violates not-null constraint
DETAIL:  Failing row contains (null, 9).
alvindb=> INSERT INTO tb_test3 (test_id,test_order) VALUES (NULL,9);
ERROR:  null value in column "test_id" violates not-null constraint
DETAIL:  Failing row contains (null, 9).

那么 PostgreSQL 是哪个版本开始支持 SERIAL 的呢？

PostgreSQL 官网文档中列出的最早的版本是 PostgreSQL 8.0，在这个文档中，已支持这种用法。

这是 PostgreSQL 8.0 文档中的例子

CREATE TABLE cinemas (
        id serial,
        name text,
        location text
) TABLESPACE diskvol1;

根据 PostgreSQL 官网， PostgreSQL Release 8.0 发布时间是 2005-01-19。

根据 Wikipedia， Oracle Database 12c Release 1 是 July 2014 发布的。

即 PostgreSQL 2005 年已支持 sequence 的 AUTO INCREMENT 用法，九年后，Oracle 也支持了。

MySQL

MySQL 用 AUTO_INCREMENT 关键字。

如下示例。

mysql> CREATE TABLE tb_test4 (
    ->     test_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     test_order INTEGER
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tb_test4 (test_id,test_order) VALUES (DEFAULT,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tb_test4 (test_order) VALUES (2);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;
+---------+--------------+
| test_id | test_order |
+---------+--------------+
|       2 |            2 |
|       1 |            1 |
+---------+--------------+
2 rows in set (0.00 sec)

从 SHOW CREATE TABLE 中可以看出，与 Oracle 和 PostgresSQL 不同的是，MySQL 并未创建单独的 sequence。

mysql> SHOW CREATE TABLE tb_test4;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                    |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_test4 | CREATE TABLE `tb_test4` (
  `test_id` int(11) NOT NULL AUTO_INCREMENT,
  `test_order` int(11) DEFAULT NULL,
  PRIMARY KEY (`test_id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

同 Oracle 中一样，当在 INSERT 中指定列 test_id 为 NULL 时，会正常自增。这是 MySQL AUTO INCREMENT 决定的。

mysql> INSERT INTO tb_test4 (test_id,test_order) VALUES (NULL,9);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|       1 |          8 |
+---------+------------+
1 row in set (0.00 sec)

下面通过以下测试用例，比较一下 INSERT/UPDATE/DELETE/TRUNCATE 对 sequence 的影响。

INSERT INTO tb_test4 (test_order) VALUES (1);
INSERT INTO tb_test4 (test_id,test_order) VALUES (100,2);
INSERT INTO tb_test4 (test_order) VALUES (3);
UPDATE tb_test4 SET test_id = 200 WHERE test_order = 3;
INSERT INTO tb_test4 (test_order) VALUES (5);
INSERT INTO tb_test4 (test_order) VALUES (6);
SELECT * FROM tb_test4 ORDER BY 2 DESC;
DELETE FROM tb_test4 WHERE test_order = 5;
DELETE FROM tb_test4 WHERE test_order = 6;
INSERT INTO tb_test4 (test_order) VALUES (7);
TRUNCATE TABLE tb_test4;
INSERT INTO tb_test4 (test_order) VALUES (8);

通过下面详细的测试，得出如下结论：

Oracle 和 PostgreSQL 测试结果相同，INSERT 比当前 sequence 大的值， 还有 UPDATE/DELETE/TRUNCATE，均 对其 sequence 无影响。

而在MySQL 5.7 InnoDB ENGINE 中，INSERT 比当前 sequence 大的值和 TRUNCATE 对其 sequence 有影响，而 UPDATE/DELETE 对其 sequence 无影响。

而在MySQL 5.7 MYISAM ENGINE 中，INSERT 比当前 sequence 大的值和 UPDATE/TRUNCATE 对其 sequence 有影响，而 DELETE 对其 sequence 无影响。

MySQL 5.7 InnoDB ENGINE

以下是 MySQL 5.7 InnoDB ENGINE 中的运行结果

mysql> INSERT INTO tb_test4 (test_order) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|       1 |          1 |
+---------+------------+
1 row in set (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tb_test4 (test_id,test_order) VALUES (100,2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tb_test4 (test_order) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              101 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|     101 |          3 |
|     100 |          2 |
|       1 |          1 |
+---------+------------+
3 rows in set (0.00 sec)
mysql> UPDATE tb_test4 SET test_id = 200 WHERE test_order = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              101 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tb_test4 (test_order) VALUES (5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              102 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|     102 |          5 |
|     200 |          3 |
|     100 |          2 |
|       1 |          1 |
+---------+------------+
4 rows in set (0.00 sec)
mysql> INSERT INTO tb_test4 (test_order) VALUES (6);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|     103 |          6 |
|     102 |          5 |
|     200 |          3 |
|     100 |          2 |
|       1 |          1 |
+---------+------------+
5 rows in set (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              103 |
+------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM tb_test4 WHERE test_order = 5;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              103 |
+------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM tb_test4 WHERE test_order = 6;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              103 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|     200 |          3 |
|     100 |          2 |
|       1 |          1 |
+---------+------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO tb_test4 (test_order) VALUES (7);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              104 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|     104 |          7 |
|     200 |          3 |
|     100 |          2 |
|       1 |          1 |
+---------+------------+
4 rows in set (0.00 sec)
mysql> TRUNCATE TABLE tb_test4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              104 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tb_test4 (test_order) VALUES (8);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|       1 |          8 |
+---------+------------+
1 row in set (0.00 sec)

MySQL 5.7 MYISAM ENGINE

以下是 MySQL 5.7 MYISAM ENGINE 中的运行结果

mysql> CREATE TABLE tb_test5 (
    ->     test_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     test_order INTEGER
    -> ) ENGINE = MYISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE tb_test5;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_test5 | CREATE TABLE `tb_test5` (
  `test_id` int(11) NOT NULL AUTO_INCREMENT,
  `test_order` int(11) DEFAULT NULL,
  PRIMARY KEY (`test_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tb_test5 (test_order) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tb_test5 (test_id,test_order) VALUES (100,2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tb_test5 (test_order) VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              101 |
+------------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|     101 |          3 |
|     100 |          2 |
|       1 |          1 |
+---------+------------+
3 rows in set (0.00 sec)

mysql> UPDATE tb_test5 SET test_id = 200 WHERE test_order = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              101 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|     200 |          3 |
|     100 |          2 |
|       1 |          1 |
+---------+------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO tb_test5 (test_order) VALUES (5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              201 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|     201 |          5 |
|     200 |          3 |
|     100 |          2 |
|       1 |          1 |
+---------+------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO tb_test5 (test_order) VALUES (5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tb_test5 (test_order) VALUES (6);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              203 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|     203 |          6 |
|     201 |          5 |
|     202 |          5 |
|     200 |          3 |
|     100 |          2 |
|       1 |          1 |
+---------+------------+
6 rows in set (0.00 sec)

mysql> DELETE FROM tb_test5 WHERE test_order = 5;
Query OK, 2 rows affected (0.00 sec)

mysql> DELETE FROM tb_test5 WHERE test_order = 6;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              203 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|     200 |          3 |
|     100 |          2 |
|       1 |          1 |
+---------+------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO tb_test5 (test_order) VALUES (7);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              204 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|     204 |          7 |
|     200 |          3 |
|     100 |          2 |
|       1 |          1 |
+---------+------------+
4 rows in set (0.00 sec)

mysql> TRUNCATE TABLE tb_test5;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              204 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tb_test5 (test_order) VALUES (8);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC;
+---------+------------+
| test_id | test_order |
+---------+------------+
|       1 |          8 |
+---------+------------+
1 row in set (0.00 sec)

mysql>

Oracle 12c

以下是 Oracle 12c(Release 12.2.0.1.0) 中的运行结果

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

1 row created.

SQL> INSERT INTO tb_test4 (test_id,test_order) VALUES (100,2);

1 row created.

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

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

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

SQL> COL table_name FOR a30
COL column_name FOR a30
COL generation FOR a30
COL sequence_name FOR a30
SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols;SQL> 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$$_254864

SQL>
SQL> SELECT ISEQ$$_254864.currval FROM dual;

   CURRVAL
----------
	 2
SQL> UPDATE tb_test4 SET test_id = 200 WHERE test_order = 3;

1 row updated.

SQL> SELECT ISEQ$$_254864.currval FROM dual;

   CURRVAL
----------
	 2

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

1 row created.

SQL> SELECT ISEQ$$_254864.currval FROM dual;

   CURRVAL
----------
	 3

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

   TEST_ID TEST_ORDER
---------- ----------
	 3	    5
       200	    3
       100	    2
	 1	    1

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

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT ISEQ$$_254864.currval FROM dual;

   CURRVAL
----------
	 4

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

   TEST_ID TEST_ORDER
---------- ----------
	 4	    6
	 3	    5
       200	    3
       100	    2
	 1	    1

SQL> 
SQL> DELETE FROM tb_test4 WHERE test_order = 5;

1 row deleted.

SQL> DELETE FROM tb_test4 WHERE test_order = 6;

1 row deleted.

SQL> COMMIT;

Commit complete.

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

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT ISEQ$$_254864.currval FROM dual;

   CURRVAL
----------
	 5

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

   TEST_ID TEST_ORDER
---------- ----------
	 5	    7
       200	    3
       100	    2
	 1	    1
SQL> TRUNCATE TABLE tb_test4;

Table truncated.

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

1 row created.

SQL> SELECT ISEQ$$_254864.currval FROM dual;

   CURRVAL
----------
	 6

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

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

PostgreSQL 11

以下是 PostgreSQL 11 中的运行结果

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

alvindb=> 
alvindb=> \d+ tb_test4
                                                    Table "public.tb_test4"
   Column   |  Type   | Collation | Nullable |                  Default                  | Storage | Stats target | Description 
------------+---------+-----------+----------+-------------------------------------------+---------+--------------+-------------
 test_id    | integer |           | not null | nextval('tb_test4_test_id_seq'::regclass) | plain   |              | 
 test_order | integer |           |          |                                           | plain   |              | 
Indexes:
    "tb_test4_pkey" PRIMARY KEY, btree (test_id)

alvindb=> SELECT currval('tb_test4_test_id_seq');
 currval 
---------
       2
(1 row)
alvindb=> UPDATE tb_test4 SET test_id = 200 WHERE test_order = 3;
UPDATE 1
alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC;
 test_id | test_order 
---------+------------
     200 |          3
     100 |          2
       1 |          1
(3 rows)

alvindb=> SELECT currval('tb_test4_test_id_seq');
 currval 
---------
       2
(1 row)

alvindb=> INSERT INTO tb_test4 (test_order) VALUES (5);
INSERT 0 1
alvindb=> SELECT currval('tb_test4_test_id_seq');
 currval 
---------
       3
(1 row)

alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC;
 test_id | test_order 
---------+------------
       3 |          5
     200 |          3
     100 |          2
       1 |          1
(4 rows)
alvindb=> INSERT INTO tb_test4 (test_order) VALUES (6);
INSERT 0 1
alvindb=> SELECT currval('tb_test4_test_id_seq');
 currval 
---------
       4
(1 row)

alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC;
 test_id | test_order 
---------+------------
       4 |          6
       3 |          5
     200 |          3
     100 |          2
       1 |          1
(5 rows)
alvindb=> DELETE FROM tb_test4 WHERE test_order = 5;
DELETE 1
alvindb=> DELETE FROM tb_test4 WHERE test_order = 6;
DELETE 1
alvindb=> INSERT INTO tb_test4 (test_order) VALUES (7);
INSERT 0 1
alvindb=> SELECT currval('tb_test4_test_id_seq');
 currval 
---------
       5
(1 row)

alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC;
 test_id | test_order 
---------+------------
       5 |          7
     200 |          3
     100 |          2
       1 |          1
(4 rows)

alvindb=> 
alvindb=> TRUNCATE TABLE tb_test4;
TRUNCATE TABLE
alvindb=> INSERT INTO tb_test4 (test_order) VALUES (8);
INSERT 0 1
alvindb=> SELECT currval('tb_test4_test_id_seq');
 currval 
---------
       6
(1 row)

alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC;
 test_id | test_order 
---------+------------
       6 |          8
(1 row)

总结

sequence 调用方式支持统计

从下表可以看出，Oracle 与 PostgreSQL 对以下sequence 的调用方式都支持。MySQL 仅支持 AUTO INCREMENT 方式。

Oracle PostgreSQL MySQL
显示调用 sequence YES YES NO
触发器中调用 sequence YES YES NO
DEFAULT 中调用 sequence YES YES NO
AUTO INCREMENT YES YES YES

AUTO INCREMENT 方式统计

AUTO INCREMENT 主键创建方式统计如下：

Database AUTO INCREMENT 主键创建方式
Oracle test_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
PostgreSQL test_id SERIAL PRIMARY KEY
MySQL test_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY

AUTO INCREMENT 方式中，INSERT 大于 sequence 的值/UPDATE/DELETE/TRUNCATE 是否会重置 sequence 统计如下：

Oracle PostgreSQL MySQL 5.7 InnoDB MySQL 5.7 MYISAM
INSERT 大于 sequence 的值 NO NO YES YES
UPDATE NO NO NO YES
DELETE NO NO NO NO
TRUNCATE NO NO YES YES

可以看出，AUTO INCREMENT 方式下，

Oracle 和 PostgreSQL 中，sequence 与 UPDATE/DELETE/TRUNCATE 相对独立的，仅会在 INSERT 时自增，且在INSERT 大于 当前sequence 的值时，并不会重置 sequence。

在 MySQL 中，sequence 的重置与否，不但与 MySQL DML/DDL 有关，还与表使用的 ENGINE有关，使用时需要特别注意。

INSERT 方式统计

INSERT WITH SEQUENCE

以下方式在 SQL 中指明了 sequence。

这种使用方式灵活多变，基本适用各种场景，尤其是大型复杂数据库应用中。

如果使用的数据库是 Oracle 或 PostgreSQL，推荐这种方式。

--Oracle
INSERT INTO tb_test (test_id) VALUES (seq_test.nextval);
--PostgreSQL
INSERT INTO tb_test (test_id) VALUES (nextval('seq_test'));

INSERT WITHOUT COLUMN NAME

SQL 如下

 INSERT INTO tb_test (test_order) VALUES (1);

下表统计 INSERT WITHOUT COLUMN NAME 时，数据库是否能如期插入 sequence 的下一个值。

可以看出，这种 INSERT 方式对以下三种数据库支持良好，且好记好理解。

从 SQL 对各数据库的兼容性考虑，推荐这种省略列名的方式。

Oracle PostgreSQL MySQL
触发器中调用 sequence YES YES
DEFAULT 中调用 sequence YES YES -
AUTO INCREMENT YES YES YES

INSERT NULL

SQL 如下

INSERT INTO tb_test (test_id,test_order) VALUES (NULL,1);

下表统计 INSERT NULL 时，数据库是否能如期插入 sequence 的下一个值。

从以下统计表格可以看出，支持不统一。

从 SQL 对各数据库的兼容性考虑，除非特意使用，一般不作推荐。

Oracle PostgreSQL MySQL
触发器中调用 sequence YES YES -
DEFAULT 中调用 sequence NO NO -
AUTO INCREMENT YES/NO NO YES

INSERT DEFAULT

SQL 如下

INSERT INTO tb_test (test_id,test_order) VALUES (DEFAULT,1);

从下表可以看出，INSERT DEFAULT 都能插入 sequence 的下一个值。

但在触发器调用 sequence 的方式中，DEFAULT 并不是专门用来插入 sequence 的下一个值的，此时用 DEFAULT 较奇怪。

DEFAULT 一般仅在定义了列的 DEFAULT 值时使用。

Oracle PostgreSQL MySQL
触发器中调用 sequence YES YES -
DEFAULT 中调用 sequence YES YES -
AUTO INCREMENT YES YES YES
数据库关系型数据库sequence
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论