三大数据库 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 的日常工作,学习实用数据库技术干货!
公众号优质文章推荐
[PG Upgrade Series] Extract Epoch Trap
[PG Upgrade Series] Toast Dump Error
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· AI与.NET技术实操系列(二):开始使用ML.NET
· 单线程的Redis速度为什么快?