【华山论剑】Oracle PostgreSQL MySQL中 Sequence 的使用

Qunar技术沙龙 2021-09-06

545

作者介绍

李振中

2020年8月加入去哪儿网，主要负责公司PostgreSQL和Oracle的运维管理工作。

一、引言

Oracle PostgreSQL MySQL 3 种 RDBMS (关系型数据库) 在 Qunar 均有使用，Sequence (序列) 是数据库中的一种常用 Object (对象)，其在 3 种数据库中实现各有异同，所以本文尝试简要探讨一下，希望对开发同学有所帮助。

二、Sequence 适用场景

主键

用于整型主键数据的生成，一般 1 个 Sequence 仅用于 1 张表的主键，当然也可以多个表的主键共用 1 个 Sequence (比如分区表)，这是最常见的用法。本文讨论的主要是此用途。

非主键

只使用 Sequence 本身自增 (也可自减，但不常用) 的功能，可多表共用一个 Sequence，或整个数据库的应用共用一个全局 Sequence。

需要注意的一点是：无论 Sequence 是否作为表的主键，Sequence 生成时虽然一般是连续的，但由于其值可能随着其所在事务被回滚，或 PostgreSQL 实例某种方式下重启产生跳变等原因，从而导致实际值不一定是连续的，所以应用不应该对 Sequence 的值是否连续作为强依赖。

三、Sequence 用法 1 显式调用

这种方式是单独创建 Sequence 和表，在 INSERT 等语句中显式调用 Sequence 。

3 种数据库中使用情况如下示例。

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 中 Sequence 的创建与 Oracle 很类似。

$ 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，详见下文用法 4 中所述。

是否可以在 INSERT 语句中不显式调用 Sequence，而使其被自动调用呢？

当然可以！

通常有 3 种方法：

通过触发器
通过 DEFAULT
通过 AUTO INCREMENT

下面我们逐个探讨一下。

四、Sequence 用法 2 隐式调用之触发器

可以在表的 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 中触发器调用 Sequence 与 Oracle 也很类似，只是触发器的创建方式略有差异。

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 时，会从 Sequence 中取值。同样，这也是 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，详见下文用法 4 中所述。

五、Sequence 用法 3 隐式调用之 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 中 DEFAULT 调用 Sequence 与 Oracle 依然很类似。

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 可以删除。在使用 Cascade 关键字时，表列的 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 2013 发布的。

即 PostgreSQL 2001 年已支持 Sequence 的 DEFAULT nextval 用法，十二年后，Oracle 也支持了。

MySQL

MySQL 不支持单独创建 Sequence。参考用法 4 AUTO INCREMENT 中 MySQL 部分。

六、Sequence 用法 4 隐式调用之 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/bigserial 一个关键字就可以搞定。

创建表

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 效果与用法 3 中一样，表列的 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 只是将上述 3个 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 2013 发布的。

即 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)
复制

下面通过以下一组SQL测试用例，比较一下 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);
复制

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)
复制

通过上述详细的测试，可得出如下结论：

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 无影响。

七、总结

Sequence 调用方式对比

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

AUTO INCREMENT 方式对比

AUTO INCREMENT 主键创建方式对比如下：

AUTO INCREMENT 方式中，INSERT 大于 Sequence 的值/UPDATE/DELETE/TRUNCATE 是否会重置 Sequence 对比如下：

可以看出，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 对各数据库的兼容性考虑，推荐这种省略列名的方式。

INSERT NULL

SQL 如下

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

下表对比 INSERT NULL 时，数据库是否能如期插入 Sequence 的下一个值。

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

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

INSERT DEFAULT

SQL 如下

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

使用DEFAULT 关键字INSERT 时都能将 Sequence 的下一个值插入到表中。

但在触发器调用 Sequence 的方式中，DEFAULT 并不是专门用来插入 Sequence 的下一个值的，此时就没必要使用 DEFAULT 了。

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

具体对比如下表所示。

招募贴：

Qunar 拥有中国互联网企业中最大规模的PostgreSQL DB集群之一, 是使用PostgreSQL 历史最悠久的互联网公司，当前诚聘高级PostgreSQL DBA，详见下图！

END

文章转载自Qunar技术沙龙，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

【华山论剑】Oracle PostgreSQL MySQL中 Sequence 的使用

六、Sequence 用法 4 隐式调用之 AUTO INCREMENT

PostgreSQL 11

Sequence 调用方式对比

INSERT WITH SEQUENCE

评论