三大資料庫 sequence 之華山論劍 (中篇)

2022 年 3 月 3 日
筆記
MySQL, Oracle, PostgreSQL

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)