ORACLE SEQUENCE和MYSQL 自增字段对比-CSDN博客

ORACLE 序列：不同于MYSQL的自增字段ORACLE序列是单独的一个object，单独进行维护，可以查看
SQL> select * from dba_sequences a where a.sequence_name='TEST_SEQ';
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ ---------- ---------- ---------- -----------
SYS TEST_SEQ 1 1E28 1 N N 10 11

这里的TEST_SEQ使用了CACHE 10的选项，意思为缓存10个数字在缓存中，索引LAST_NUMBER是11，如果重启后或者导入到其他数据库那么值是从11开始，丢失内存中的
计数，同时和MYSQL 的INNODB auto_increment一样如果事物回滚都可能出现断裂带
SQL> insert into testseq values(test_seq.nextval,'gaopeng');
1 row inserted

SQL> commit;
Commit complete

SQL> insert into testseq values(test_seq.nextval,'gaopeng');
1 row inserted

SQL> rollback;
Rollback complete

SQL> insert into testseq values(test_seq.nextval,'gaopeng');
1 row inserted

SQL> select * from testseq;
ID NAME
---------- --------------------
1 gaopeng
3 gaopeng

但是任何TRUNCATE,DELETE是不会影响到寻列本生的。
SQL> truncate table testseq;
Table truncated

SQL> insert into testseq values(test_seq.nextval,'gaopeng');
1 row inserted

SQL> select* from testseq;
ID NAME
---------- --------------------
121 gaopeng

删除顶端的值,重启后怎么样呢？
SQL> select LAST_NUMBER from dba_sequences a where a.sequence_name='TEST_SEQ';
LAST_NUMBER
-----------
130

SQL> select* from testseq;
ID NAME
---------- --------------------
121 gaopeng
122 gaopeng
123 gaopeng
124 gaopeng
125 gaopeng
126 gaopeng
删除顶端部分值
SQL> delete from testseq where id>124;
2 rows deleted

SQL> commit;
Commit complete
这里实际的值下次应该是

SQL> select test_seq.nextval from dual;
NEXTVAL
----------
127
然后重启
SQL> insert into testseq values(test_seq.nextval,'gaopeng');
Warning: connection was lost and re-established
1 row inserted

SQL> select* from testseq;
ID NAME
---------- --------------------
121 gaopeng
122 gaopeng
123 gaopeng
124 gaopeng
130 gaopeng
可以看到这里的ID=130实际上是LAST_NUMBER的值

但是MYSQL INNODB有一些不同
mysql> create table testseq
-> (id int(10) auto_increment,
-> name varchar(20),
-> primary key (id));
Query OK, 0 rows affected (0.42 sec)

mysql> alter table testseq auto_increment=1000;

1、TRUNCATE后自增字段将从最小值1开始，而ORACLE不会，因为ORACLE SEQUENCE是单独的OBJECT，不受表本生影响
mysql> select * from testseq;
+------+---------+
| id | name |
+------+---------+
| 1000 | gaopeng |
| 1002 | gaopeng |
+------+---------+
2 rows in set (0.00 sec)

mysql> truncate table testseq;
Query OK, 0 rows affected (0.17 sec)

mysql> insert into testseq(name) values('gaopeng');
Query OK, 1 row affected (0.04 sec)

mysql> insert into testseq(name) values('gaopeng');
Query OK, 1 row affected (0.00 sec)

mysql> insert into testseq(name) values('gaopeng');
Query OK, 1 row affected (0.00 sec)

mysql> select * from testseq;
+----+---------+
| id | name |
+----+---------+
| 1 | gaopeng |
| 2 | gaopeng |
| 3 | gaopeng |
+----+---------+
3 rows in set (0.00 sec)

2、INNODB 在缓存中维护着计数器，如果重启后，任何删除顶端的数据重启后会重用这些数据，而ORACLE不会，因为ORACLE会从LAST_NUMBER开始
mysql> select * from testseq;
+----+---------+
| id | name |
+----+---------+
| 1 | gaopeng |
| 3 | gaopeng |
| 5 | gaopeng |
| 6 | gaopeng |
| 7 | gaopeng |
| 8 | gaopeng |
+----+---------+
6 rows in set (0.00 sec)

mysql> delete from testseq where id>6;
Query OK, 1 row affected (0.01 sec)

mysql> select * from testseq;
+----+---------+
| id | name |
+----+---------+
| 1 | gaopeng |
| 3 | gaopeng |
| 5 | gaopeng |
| 6 | gaopeng |
+----+---------+
4 rows in set (0.00 sec)

这个时候正常的情况下应该是下次是9，如果我们重启后

mysql> insert into testseq(name) values('gaopeng');
Query OK, 1 row affected (0.01 sec)

mysql> select * from testseq;
+----+---------+
| id | name |
+----+---------+
| 1 | gaopeng |
| 3 | gaopeng |
| 5 | gaopeng |
| 6 | gaopeng |
| 7 | gaopeng |
+----+---------+
5 rows in set (0.02 sec)
可以看到ID=7的行被重用

3、相同点都可能出现断裂如下：

mysql> insert into testseq(name) values('gaopeng');
Query OK, 1 row affected (0.03 sec)

mysql> select * from testseq;
+------+---------+
| id | name |
+------+---------+
| 1000 | gaopeng |
+------+---------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testseq(name) values('gaopeng');
Query OK, 1 row affected (0.03 sec)

mysql> rollback;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into testseq(name) values('gaopeng');
Query OK, 1 row affected (0.01 sec)

mysql> select * from testseq;
+------+---------+
| id | name |
+------+---------+
| 1000 | gaopeng |
| 1002 | gaopeng |
+------+---------+
2 rows in set (0.00 sec)

最后总结一下
1、TRUNCATE后自增字段将从最小值1开始，而ORACLE不会，因为ORACLE SEQUENCE是单独的OBJECT，不受表本生影响
2、INNODB和ORACLE 都在缓存中维护着计数器，但是MYSQL任何删除顶端的数据重启后会重用这些数据，而ORACLE重启会丢失全部的CACHE中的值直接从LAST_NUMBER开始
3、MYSQL和ORACLE都会出现回滚断裂问题
4、ORACLE是单独的对象而MYSQL是和表在一起的
5、如果在MYSQL中使用unsignde可以得到更大的自增值如下，因为tinyint的取值范围问题有如下报错：

mysql> create table testseq1 (id tinyint(3) unsigned auto_increment, name varchar(20), primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql> create table testseq2 (id tinyint(3) auto_increment, name varchar(20), primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> alter table testseq1 auto_increment=200;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table testseq2 auto_increment=200;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into testseq1(name) values('gaopeng');
Query OK, 1 row affected (0.00 sec)
mysql> insert into testseq2(name) values('gaopeng');
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine