大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说华山论剑官网_华山论鉴大论战,希望您对编程的造诣更进一步.
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 的日常工作，学习实用数据库技术干货！
公众号优质文章推荐
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 扩展创建失败原因调查
原文地址：https://www.cnblogs.com/dbadaily/archive/2022/03/03/sequence4.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/5474.html
