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

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

posted @   DBADaily  阅读(485)  评论(0)  编辑  收藏  举报
相关博文：
· 三大数据库 sequence 之华山论剑 (中篇)
· 三大数据库 sequence 之华山论剑 (上篇)
· MySQL-进阶篇
· MySQL 数据库
· SqlAlchemy-2-0-中文文档-十一-
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· .NET10 - 预览版1新功能体验（一）
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛