一． Mysql 自增列
众所周知， mysql 有自增列功能。 Mysql 中每张表只能有一个自增列，并且自增列必须是主键或者唯一键。
|
mysql> create table test2(id int not null auto_increment); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key mysql> create table test2(id int not null auto_increment primary key); Query OK, 0 rows affected (0.01 sec) |
Mysql 插入空值，可以看到是允许的，默认会用自增列值插入。
|
mysql> insert into test2 values(); Query OK, 1 row affected (0.01 sec) mysql> select * from test2; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) |
Mysql 也是允许插入的值不连续
|
mysql> insert into test2 values(3); Query OK, 1 row affected (0.00 sec) mysql> select * from test2; +----+ | id | +----+ | 1 | | 3 | +----+ 2 rows in set (0.00 sec) |
表加一列，显式插入新增列，自增列会隐式地从当前最大值自增。
|
mysql> insert into test2(b) values(4); Query OK, 1 row affected (0.00 sec) mysql> select * from test2; +----+------+ | id | b | +----+------+ | 1 | NULL | | 3 | NULL | | 4 | 4 | +----+------+ 3 rows in set (0.00 sec) |
二． Oracle 自增列
Oracle
在
12c
之前的版本，自增列功能只能通过序列
+
触发器的方式实现。
12c
版本终于迎来了这个新功能。
自增列语法
建表
|
SQL> CREATE TABLE test1 ( 2 id int GENERATED ALWAYS AS IDENTITY 3 ); Table created. SQL> desc test1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(38) |
自增列会自动加上 not null 约束。 此时会自动生成一个序列
|
SQL> select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CACHE_SIZE from dba_sequences where SEQUENCE_OWNER='MING'; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CACHE_SIZE ------------------------------ ---------- ---------- ------------ ---------- ISEQ$$_45410 1 1.0000E+28 1 20 SQL> set line 150 SQL> select TABLE_NAME,COLUMN_NAME,DATA_DEFAULT from dba_tab_columns where TABLE_NAME='TEST1' and COLUMN_NAME='ID'; TABLE_NAME COLUMN_NAME DATA_DEFAULT ------------------------------ ------------------------------ -------------------------------------------------- TEST1 ID "MING"."ISEQ$$_45410".nextval 注意下面这两个动作 SQL> select ISEQ$$_45410.nextval from dual; NEXTVAL ---------- 1 SQL> select ISEQ$$_45410.nextval from dual; NEXTVAL ---------- 2 |
插入一个值
|
SQL> insert into test1 values(1); insert into test1 values(1) * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column |
不能向自增列插入值。
|
SQL> alter table test1 add b int; Table altered. SQL> insert into test1(b) values(1); 1 row created. SQL> select * from test1; ID B ---------- ---------- 3 1 |
可以看到 ID 列插入了值，但是不是 1 ，而是 3. 因为之前手动 select 从序列中取过值。 经过上面实验不难猜测，下面语句会报错
|
SQL> insert into test1(id,b) values(4,1); insert into test1(id,b) values(4,1) * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column |
Update 和 delete
|
SQL> update test1 set id=5; update test1 set id=5 * ERROR at line 1: ORA-32796: cannot update a generated always identity column SQL> delete from test1; 1 row deleted. SQL> commit; Commit complete. |
Update 不可以，但是 delete 是可以的。 可以用 default 指代
|
SQL> insert into test1 values( default ,1); 1 row created. SQL> select * from test1; ID B ---------- ---------- 5 1 SQL> commit; Commit complete. |
生成的序列其他用户也是可以用的
|
SQL> insert into tx(a) values(ISEQ$$_45410.nextval); 1 row created. SQL> commit; Commit complete. |
这个序列是不能删除的。
|
SQL> drop sequence ming.ISEQ$$_45410; drop sequence ming.ISEQ$$_45410 * ERROR at line 1: ORA-32794: cannot drop a system-generated sequence SQL> drop table test1; Table dropped. SQL> select ISEQ$$_45410.nextval from dual; NEXTVAL ---------- 6 |
Purge 回收站后就没有了。
|
SQL> purge recyclebin; Recyclebin purged. |
所以在删除表时可以加 purge ，加 cascade constraint 也不会马上删除序列。
|
SQL> CREATE TABLE test2 ( 2 id int GENERATED BY DEFAULT ON NULL AS IDENTITY 3 ); Table created. SQL> insert into test2 values(''); 1 row created. SQL> select * from test2; ID ---------- 1 |
Insert 可以指定值，但是指定的值不会被序列识别，换句话说，如果手动插入值 2 ，序列的下一个值是 2 ，那么在此插入空的时候，会插入 2 ，而不是 3.
|
SQL> insert into test2 values(4); 1 row created. SQL> insert into test2 values(3); 1 row created. SQL> commit; Commit complete. SQL> select ISEQ$$_45418.nextval from dual; NEXTVAL ---------- 3 SQL> select * from test2; ID ---------- 1 2 4 3 SQL> insert into test2 values(''); 1 row created. SQL> select * from test2; ID ---------- 1 2 4 3 4 |
下面这个 Update 的动作有点意思，当前表上有 2 条 ID=4 的值，用 default 更新的时候会用序列赋予不同的值。
|
SQL> update test2 set id=default where id=4; 2 rows updated. SQL> select * from test2; ID ---------- 1 2 5 3 6 |
既然已经知道，自增列是用序列创建的，那么下面两种方式也是可以的
|
create sequence ming$seq01 minvalue 1 maxvalue 9999999 start with 1 increment by 2 cache 20 nocycle; create table test3(id int default ming$seq01.nextval); create table test3(id number); alter table test3 modify id number default seq_1.nextval; |
区别在于是插入 null 的时候不会变成数字，该列也没有 not null 约束。
|
SQL> insert into test3 values(null); 1 row created. SQL> select * from test3; ID ---------- |
总结一下：
l Oracle 自增列是利用序列做到的。自增列会自动加上非空约束；
l 表删除的时候，序列不会马上删除，这是因为删除的表会进入回收站，要关联删除需要加 purge ；
l GENERATED ALWAYS AS IDENTITY 可以 delete ，不能显示 insert ，不能 update ；
l GENERATED BY DEFAULT ON NULL AS IDENTITY 会自动将 null 值插入序列中的值，增删改都可以，相比 GENERATED ALWAYS AS IDENTITY 更加灵活，但是列的唯一性不能保证。
l 受自增列启发，可以自己创建序列，指定为表列的默认值。
l 系统自建序列的属性不能更改，可以在创建自增列的时候手动修改，否则较小 cache 默认值，会造成性能问题。 Like this:
CREATE TABLE test4 (id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10 cache 100 ));
l 其他用户如果要向带有自增列表中插入数据的话，那么需要序列权限
