ORACLE SEQUENCE和MYSQL 自增字段对比

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