sequence 用法四 AUTO INCREMENT
通过 DEFAULT 还是需要手动创建 sequence。有没有更简单的用法呢？
当然，就是通过 AUTO INCREMENT 方式，自动创建 sequence，并且自动在 DEFAULT 中调用！
Oracle
同样，Oracle 也是 12c 开始支持 AUTO INCREMENT。
以下测试是在 Oracle Database 12c Release 12.2.0.1.0 中进行的。
SQL> CREATE TABLE tb_test4 ( test_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, test_order NUMBER ); 2 3 4 Table created.
插入测试数据，可以看到预期的结果。
SQL> INSERT INTO tb_test4 (test_id,test_order) VALUES (DEFAULT,1); 1 row created. SQL> INSERT INTO tb_test4 (test_order) VALUES (2); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC; TEST_ID TEST_ORDER ---------- ------------ 2 2 1 1
通过查询可以看到，系统自动创建了 一个名为 ISEQ$$_254835（Oracle 自动生成的名字一般都不太友好） 的 sequence，并将其设置为了 DEFAULT。
SQL> COL object_name FOR a30 COL object_type FOR a30SQL> SQL> SELECT object_name,object_type FROM user_objects; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ TB_TEST4 TABLE ISEQ$$_254835 SEQUENCE SQL> SET linesize 100 COL table_name FOR a30 COL column_name FOR a30 COL data_default FOR a30 SQL> SELECT table_name,column_name,data_default FROM user_tab_columns WHERE table_name = 'TB_TEST4'; TABLE_NAME COLUMN_NAME DATA_DEFAULT ------------------------------ ------------------------------ ------------------------------ TB_TEST4 TEST_ID "TEST"."ISEQ$$_254857".nextval TB_TEST4 TEST_ORDER
通过以下数据字典可以看出，自动生成的 sequence 是与列对应的。
SQL> SET linesize 200 COL table_name FOR a30 COL column_name FOR a30 COL generation FOR a30 COL sequence_name FOR a30SQL> 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$$_254835
自动生成的 sequence 是否可以删除呢？
通过如下实验，可以看到，Oracle 中系统自动生成的 sequence 不能单独删除。
SQL> DROP SEQUENCE ISEQ$$_254835; DROP SEQUENCE ISEQ$$_254835 * ERROR at line 1: ORA-32794: cannot drop a system-generated sequence
删除表会删除对应的 sequence 吗？会。表以 BIN 开头，代表 table 已被删除，但 PURGE RECYCLEBIN 后才会被彻底删除。
SQL> DROP TABLE tb_test4; Table dropped. SQL> SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols; TABLE_NAME COLUMN_NAME SEQUENCE_NAME ------------------------------ ------------------------------ ------------------------------ BIN$vXiAW/32gIHgU5KhXwowkg==$0 TEST_ID ISEQ$$_254835 SQL> SELECT object_name,object_type FROM USER_OBJECTS; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ ISEQ$$_254835 SEQUENCE
SQL> PURGE RECYCLEBIN; Recyclebin purged. SQL> SELECT object_name,object_type FROM USER_OBJECTS; no rows selected SQL> SELECT table_name,column_name,sequence_name FROM user_tab_identity_cols; no rows selected
下面测试表明，当在 INSERT 中指定列 test_id 为 NULL 时，会从 sequence 中取值。
这是 Oracle 中 GENERATED BY DEFAULT ON NULL 中的 ON NULL 决定的。
SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC; TEST_ID TEST_ORDER ---------- ---------- 6 8 SQL> INSERT INTO tb_test4 (test_id,test_order) VALUES (NULL,9); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT * FROM tb_test4 ORDER BY 2 DESC; TEST_ID TEST_ORDER ---------- ---------- 7 9 6 8
以上示例中使用的是 GENERATED BY DEFAULT ON NULL，其他还有 GENERATED BY DEFAULT 和 GENERATED ALWAYS 。
GENERATED BY DEFAULT 与 GENERATED BY DEFAULT ON NULL 区别是，当主键插入 NULL 值时，GENERATED BY DEFAULT 会报错，如下：
SQL> CREATE TABLE tb_test5 ( test_id NUMBER GENERATED BY DEFAULT AS IDENTITY, test_order NUMBER ); 2 3 4 Table created. SQL> INSERT INTO tb_test5 (test_id,test_order) VALUES (NULL,1); INSERT INTO tb_test5 (test_id,test_order) VALUES (NULL,1) * ERROR at line 1: ORA-01400: cannot insert NULL into ("TEST"."TB_TEST5"."TEST_ID")
GENERATED ALWAYS 是插入时，
若主键指定值会报错：
SQL> CREATE TABLE tb_test6 ( test_id NUMBER GENERATED ALWAYS AS IDENTITY, test_order NUMBER ); 2 3 4 Table created. SQL> INSERT INTO tb_test6 (test_id,test_order) VALUES (1,1); INSERT INTO tb_test6 (test_id,test_order) VALUES (1,1) * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column SQL> INSERT INTO tb_test6 (test_id,test_order) VALUES (NULL,2); INSERT INTO tb_test6 (test_id,test_order) VALUES (NULL,2) * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column
但指定 DEFAULT 可以：
SQL> INSERT INTO tb_test6 (test_id,test_order) VALUES (DEFAULT,3); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT * FROM tb_test6 ORDER BY 2 DESC; TEST_ID TEST_ORDER ---------- ---------- 1 3
PostgreSQL
在 PostgreSQL 中同样可以，甚至更简单，SERIAL 一个单词就够了。
创建表
alvindb=> CREATE TABLE tb_test4 ( alvindb(> test_id SERIAL PRIMARY KEY, alvindb(> test_order INTEGER alvindb(> ); CREATE TABLE
插入测试数据，结果符合预期。
alvindb=> INSERT INTO tb_test4 (test_id,test_order) VALUES (DEFAULT,1); INSERT 0 1 alvindb=> INSERT INTO tb_test4 (test_order) VALUES (2); INSERT 0 1 alvindb=> SELECT * FROM tb_test4 ORDER BY 2 DESC; test_id | test_order ---------+-------------- 2 | 2 1 | 1 (2 rows)
通过如下方式查看表结构，可以看到，加了 SERIAL 以后，自动创建了 sequence tb_test4_test_id_seq（PostgreSQL 自动生成的名字一般比较友好），并将其设置为了 DEFAULT。
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)
查看 sequence 定义看的 'Owned by'，可以看到，自动生成的 sequence 与对应列也是绑定的。
即 'Owned by' 也代表删除表或列的时候，对应的 sequence 也会被删除。
alvindb=> \d+ tb_test4_test_id_seq Sequence "public.tb_test4_test_id_seq" Type | Start | Minimum | Maximum | Increment | Cycles? | Cache ---------+-------+---------+------------+-----------+---------+------- integer | 1 | 1 | 2147483647 | 1 | no | 1 Owned by: public.tb_test4.test_id
下面测试删除表后，自动生成的 sequence 也会被删除。
alvindb=> DROP TABLE tb_test4; DROP TABLE alvindb=> \d+ tb_test4_test_id_seq Did not find any relation named "tb_test4_test_id_seq". alvindb=>
从下面的例子中可以看出，删除 sequence 效果与用法三中一样，表列的 DEFAULT 也被删除了。
alvindb=> CREATE TABLE tb_test4 ( alvindb(> test_id SERIAL PRIMARY KEY, alvindb(> test_order INTEGER alvindb(> ); CREATE TABLE alvindb=> DROP SEQUENCE tb_test4_test_id_seq; ERROR: cannot drop sequence tb_test4_test_id_seq because other objects depend on it DETAIL: default value for column test_id of table tb_test4 depends on sequence tb_test4_test_id_seq HINT: Use DROP ... CASCADE to drop the dependent objects too. alvindb=> DROP SEQUENCE tb_test4_test_id_seq CASCADE; NOTICE: drop cascades to default value for column test_id of table tb_test4 DROP SEQUENCE 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)
与上述用法三中手动创建 sequence 不同的是，使用 SERIAL 的话：
a. 自动生成的 sequence 与对应列是绑定的
b. 删除表后，自动生成的 sequence 也会被删除，避免无用的 sequence 的存在
根据 PostgreSQL官方文档，
CREATE TABLE tablename ( colname SERIAL );
与如下三个 SQL 等价：
CREATE SEQUENCE tablename_colname_seq AS integer; CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') ); ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
下面测试表明，当在 INSERT 中指定列 test_id 为 NULL 时，会报错。
从上面 SERIAL 等价的 SQL 中可以看出，PostgreSQL 只是将上述 三个 SQL 精简为 SERIAL 关键字，与 Oracle 中 AUTO INCREMENT 本质是不同的。
alvindb=> INSERT INTO tb_test4 (test_id,test_order) VALUES (NULL,9); ERROR: null value in column "test_id" violates not-null constraint DETAIL: Failing row contains (null, 9). alvindb=> INSERT INTO tb_test3 (test_id,test_order) VALUES (NULL,9); ERROR: null value in column "test_id" violates not-null constraint DETAIL: Failing row contains (null, 9).
那么 PostgreSQL 是哪个版本开始支持 SERIAL 的呢？
PostgreSQL 官网文档中列出的最早的版本是 PostgreSQL 8.0，在这个文档中，已支持这种用法。
这是 PostgreSQL 8.0 文档中的例子
CREATE TABLE cinemas ( id serial, name text, location text ) TABLESPACE diskvol1;
根据 PostgreSQL 官网， PostgreSQL Release 8.0 发布时间是 2005-01-19。
根据 Wikipedia， Oracle Database 12c Release 1 是 July 2014 发布的。
即 PostgreSQL 2005 年已支持 sequence 的 AUTO INCREMENT 用法，九年后，Oracle 也支持了。
MySQL
MySQL 用 AUTO_INCREMENT 关键字。
如下示例。
mysql> CREATE TABLE tb_test4 ( -> test_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, -> test_order INTEGER -> ); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO tb_test4 (test_id,test_order) VALUES (DEFAULT,1); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tb_test4 (test_order) VALUES (2); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC; +---------+--------------+ | test_id | test_order | +---------+--------------+ | 2 | 2 | | 1 | 1 | +---------+--------------+ 2 rows in set (0.00 sec)
从 SHOW CREATE TABLE 中可以看出，与 Oracle 和 PostgresSQL 不同的是，MySQL 并未创建单独的 sequence。
mysql> SHOW CREATE TABLE tb_test4; +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tb_test4 | CREATE TABLE `tb_test4` ( `test_id` int(11) NOT NULL AUTO_INCREMENT, `test_order` int(11) DEFAULT NULL, PRIMARY KEY (`test_id`) ) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1 | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
同 Oracle 中一样，当在 INSERT 中指定列 test_id 为 NULL 时，会正常自增。这是 MySQL AUTO INCREMENT 决定的。
mysql> INSERT INTO tb_test4 (test_id,test_order) VALUES (NULL,9); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM tb_test5 ORDER BY 2 DESC; +---------+------------+ | test_id | test_order | +---------+------------+ | 1 | 8 | +---------+------------+ 1 row in set (0.00 sec)
下面通过以下测试用例，比较一下 INSERT/UPDATE/DELETE/TRUNCATE 对 sequence 的影响。
INSERT INTO tb_test4 (test_order) VALUES (1); INSERT INTO tb_test4 (test_id,test_order) VALUES (100,2); INSERT INTO tb_test4 (test_order) VALUES (3); UPDATE tb_test4 SET test_id = 200 WHERE test_order = 3; INSERT INTO tb_test4 (test_order) VALUES (5); INSERT INTO tb_test4 (test_order) VALUES (6); SELECT * FROM tb_test4 ORDER BY 2 DESC; DELETE FROM tb_test4 WHERE test_order = 5; DELETE FROM tb_test4 WHERE test_order = 6; INSERT INTO tb_test4 (test_order) VALUES (7); TRUNCATE TABLE tb_test4; INSERT INTO tb_test4 (test_order) VALUES (8);
通过下面详细的测试，得出如下结论：
Oracle 和 PostgreSQL 测试结果相同，INSERT 比当前 sequence 大的值， 还有 UPDATE/DELETE/TRUNCATE，均 对其 sequence 无影响。
而在MySQL 5.7 InnoDB ENGINE 中，INSERT 比当前 sequence 大的值和 TRUNCATE 对其 sequence 有影响，而 UPDATE/DELETE 对其 sequence 无影响。
而在MySQL 5.7 MYISAM ENGINE 中，INSERT 比当前 sequence 大的值和 UPDATE/TRUNCATE 对其 sequence 有影响，而 DELETE 对其 sequence 无影响。
MySQL 5.7 InnoDB ENGINE
以下是 MySQL 5.7 InnoDB ENGINE 中的运行结果
mysql> INSERT INTO tb_test4 (test_order) VALUES (1); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC; +---------+------------+ | test_id | test_order | +---------+------------+ | 1 | 1 | +---------+------------+ 1 row in set (0.00 sec) mysql> SELECT last_insert_id(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO tb_test4 (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_test4 (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> SELECT * FROM tb_test4 ORDER BY 2 DESC; +---------+------------+ | test_id | test_order | +---------+------------+ | 101 | 3 | | 100 | 2 | | 1 | 1 | +---------+------------+ 3 rows in set (0.00 sec) mysql> UPDATE tb_test4 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> INSERT INTO tb_test4 (test_order) VALUES (5); Query OK, 1 row affected (0.00 sec) mysql> SELECT last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 102 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC; +---------+------------+ | test_id | test_order | +---------+------------+ | 102 | 5 | | 200 | 3 | | 100 | 2 | | 1 | 1 | +---------+------------+ 4 rows in set (0.00 sec) mysql> INSERT INTO tb_test4 (test_order) VALUES (6); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC; +---------+------------+ | test_id | test_order | +---------+------------+ | 103 | 6 | | 102 | 5 | | 200 | 3 | | 100 | 2 | | 1 | 1 | +---------+------------+ 5 rows in set (0.00 sec) mysql> SELECT last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 103 | +------------------+ 1 row in set (0.00 sec) mysql> DELETE FROM tb_test4 WHERE test_order = 5; Query OK, 1 row affected (0.01 sec) mysql> SELECT last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 103 | +------------------+ 1 row in set (0.00 sec) mysql> DELETE FROM tb_test4 WHERE test_order = 6; Query OK, 1 row affected (0.00 sec) mysql> SELECT last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 103 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tb_test4 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_test4 (test_order) VALUES (7); Query OK, 1 row affected (0.01 sec) mysql> SELECT last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 104 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tb_test4 ORDER BY 2 DESC; +---------+------------+ | test_id | test_order | +---------+------------+ | 104 | 7 | | 200 | 3 | | 100 | 2 | | 1 | 1 | +---------+------------+ 4 rows in set (0.00 sec) mysql> TRUNCATE TABLE tb_test4; Query OK, 0 rows affected (0.00 sec) mysql> SELECT last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 104 | +------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO tb_test4 (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_test4 ORDER BY 2 DESC; +---------+------------+ | test_id | test_order | +---------+------------+ | 1 | 8 | +---------+------------+ 1 row in set (0.00 sec)
公众号
关注 DBA Daily 公众号，第一时间收到文章的更新。
通过一线 DBA 的日常工作，学习实用数据库技术干货！
公众号优质文章推荐
[PG Upgrade Series] Extract Epoch Trap
[PG Upgrade Series] Toast Dump Error