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

总结

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