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

• Z时代
• 2024-01-10
• 分类：综合

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)

公众号

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

以上是 三大数据库sequence之华山论剑(中篇) 的全部内容， 来源链接： utcz.com/z/536315.html