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

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 a30COL object_type FOR a30SQL> SQL> SELECT object_name,object_type FROM user_objects;OBJECT_NAME		       OBJECT_TYPE------------------------------ ------------------------------TB_TEST4		       TABLEISEQ$$_254835		       SEQUENCESQL> SET linesize 100COL table_name FOR a30COL column_name FOR a30COL data_default FOR a30SQL> 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".nextvalTB_TEST4		       TEST_ORDER

通过以下数据字典可以看出，自动生成的 sequence 是与列对应的。

SQL> SET linesize 200COL table_name FOR a30COL column_name FOR a30COL generation FOR a30COL 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$$_254835SQL> 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 selectedSQL> 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	    8SQL> 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 columnSQL> 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 INTEGERalvindb(> );CREATE TABLE

插入测试数据，结果符合预期。

alvindb=> INSERT INTO tb_test4 (test_id,test_order) VALUES (DEFAULT,1);INSERT 0 1alvindb=> INSERT INTO tb_test4 (test_order) VALUES (2);INSERT 0 1alvindb=> 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      |     1Owned by: public.tb_test4.test_id

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

alvindb=> DROP TABLE tb_test4;DROP TABLEalvindb=> \d+ tb_test4_test_id_seqDid 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 INTEGERalvindb(> );CREATE TABLEalvindb=> DROP SEQUENCE tb_test4_test_id_seq;ERROR:  cannot drop sequence tb_test4_test_id_seq because other objects depend on itDETAIL:  default value for column test_id of table tb_test4 depends on sequence tb_test4_test_id_seqHINT:  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_test4DROP SEQUENCEalvindb=> \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 constraintDETAIL:  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 constraintDETAIL:  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: 0mysql> 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)

公众号

关注 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 扩展创建失败原因调查