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

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

MySQL 5.7 MYISAM ENGINE

以下是 MySQL 5.7 MYISAM ENGINE 中的運行結果

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

mysql>

Oracle 12c

以下是 Oracle 12c(Release 12.2.0.1.0) 中的運行結果

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

1 row created.

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

1 row created.

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

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

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

SQL> COL table_name FOR a30
COL column_name FOR a30
COL generation FOR a30
COL sequence_name FOR a30
SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols;SQL> SQL> SQL> SQL> 
SQL> SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols;

TABLE_NAME		       COLUMN_NAME		      SEQUENCE_NAME
------------------------------ ------------------------------ ------------------------------
TB_TEST4		       TEST_ID			      ISEQ$$_254864

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

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

1 row updated.

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

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

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

1 row created.

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

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

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

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

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

1 row created.

SQL> COMMIT;

Commit complete.

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

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

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

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

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

1 row deleted.

SQL> DELETE FROM tb_test4 WHERE test_order = 6;

1 row deleted.

SQL> COMMIT;

Commit complete.

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

1 row created.

SQL> COMMIT;

Commit complete.

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

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

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

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

Table truncated.

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

1 row created.

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

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

SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC;

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

PostgreSQL 11

以下是 PostgreSQL 11 中的運行結果

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

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

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

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

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

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

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

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

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

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

總結

sequence 調用方式支援統計

從下表可以看出，Oracle 與 PostgreSQL 對以下sequence 的調用方式都支援。MySQL 僅支援 AUTO INCREMENT 方式。

	Oracle	PostgreSQL	MySQL
顯示調用 sequence	YES	YES	NO
觸發器中調用 sequence	YES	YES	NO
DEFAULT 中調用 sequence	YES	YES	NO
AUTO INCREMENT	YES	YES	YES

AUTO INCREMENT 方式統計

AUTO INCREMENT 主鍵創建方式統計如下：

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

AUTO INCREMENT 方式中，INSERT 大於 sequence 的值/UPDATE/DELETE/TRUNCATE 是否會重置 sequence 統計如下：

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

可以看出，AUTO INCREMENT 方式下，

Oracle 和 PostgreSQL 中，sequence 與 UPDATE/DELETE/TRUNCATE 相對獨立的，僅會在 INSERT 時自增，且在INSERT 大於當前sequence 的值時，並不會重置 sequence。

在 MySQL 中，sequence 的重置與否，不但與 MySQL DML/DDL 有關，還與表使用的 ENGINE有關，使用時需要特別注意。

INSERT 方式統計

INSERT WITH SEQUENCE

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

這種使用方式靈活多變，基本適用各種場景，尤其是大型複雜資料庫應用中。

如果使用的資料庫是 Oracle 或 PostgreSQL，推薦這種方式。

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

INSERT WITHOUT COLUMN NAME

SQL 如下

 INSERT INTO tb_test (test_order) VALUES (1);

下表統計 INSERT WITHOUT COLUMN NAME 時，資料庫是否能如期插入 sequence 的下一個值。

可以看出，這種 INSERT 方式對以下三種資料庫支援良好，且好記好理解。

從 SQL 對各資料庫的兼容性考慮，推薦這種省略列名的方式。

	Oracle	PostgreSQL	MySQL
觸發器中調用 sequence	YES	YES
DEFAULT 中調用 sequence	YES	YES	–
AUTO INCREMENT	YES	YES	YES

INSERT NULL

SQL 如下

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

下表統計 INSERT NULL 時，資料庫是否能如期插入 sequence 的下一個值。

從以下統計表格可以看出，支援不統一。

從 SQL 對各資料庫的兼容性考慮，除非特意使用，一般不作推薦。

	Oracle	PostgreSQL	MySQL
觸發器中調用 sequence	YES	YES	–
DEFAULT 中調用 sequence	NO	NO	–
AUTO INCREMENT	YES/NO	NO	YES

INSERT DEFAULT

SQL 如下

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

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

但在觸發器調用 sequence 的方式中，DEFAULT 並不是專門用來插入 sequence 的下一個值的，此時用 DEFAULT 較奇怪。

DEFAULT 一般僅在定義了列的 DEFAULT 值時使用。

	Oracle	PostgreSQL	MySQL
觸發器中調用 sequence	YES	YES	–
DEFAULT 中調用 sequence	YES	YES	–
AUTO INCREMENT	YES	YES	YES

公眾號

關注 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 擴展創建失敗原因調查

Tags: MySQL Oracle PostgreSQL

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

MySQL 5.7 MYISAM ENGINE

Oracle 12c

PostgreSQL 11

總結

sequence 調用方式支援統計

AUTO INCREMENT 方式統計

INSERT 方式統計

INSERT WITH SEQUENCE

INSERT WITHOUT COLUMN NAME

INSERT NULL

INSERT DEFAULT

公眾號

公眾號優質文章推薦

VirMach 便宜 VPS

QNews

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

MySQL 5.7 MYISAM ENGINE

Oracle 12c

PostgreSQL 11

總結

sequence 調用方式支援統計

AUTO INCREMENT 方式統計

INSERT 方式統計

INSERT WITH SEQUENCE

INSERT WITHOUT COLUMN NAME

INSERT NULL

INSERT DEFAULT

公眾號

公眾號優質文章推薦

分享此文：

Related Posts

1、大數據 Hadoop配置和單機Hadoop系統配置

Assignment Problem的若干思考

健身也能大口吃肉！新希望六和香煎雞排20包：69.8元

MySQL通過bin log日誌恢複數據｜手撕MySQL｜對線面試官

VirMach 便宜 VPS

QNews

熱門搜尋