三大数据库 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