简单对比Mysql的AUTO_INCREMENT和Oracle的SEQUENCE
2010年06月11日 作者： 大头刚
好几年前刚学习MYSQL的一些笔记，当时把MYSQL和ORACLE进行一系列对比。前几天遇到MYSQL的AUTO_INCREMENT值增长到极限，导致表无法插入的问题，正好把以前的笔记复习下。
使用oracle的scott模式的数据作为测试数据：
oracle>conn mytest/mytest Connected. oracle>@$ORACLE_HOME/sqlplus/demo/demobld.sql oracle>select table_name from user_tables; TABLE_NAME ------------------------------------------------------------ BONUS DEPT DUMMY EMP SALGRADE
把这些表在导入到MYSQL，具体过程就省略了。
mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | bonus | | dept | | dummy | | emp | | salgrade | +------------------+ 5 rows in set (0.11 sec) mysql> select * from emp; +-------+--------+-----------+------+---------------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+---------------------+---------+---------+--------+ | 1 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | | 2 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | | 3 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | | 4 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 5 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | | 6 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 7 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | | 8 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 | | 9 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 10 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | | 11 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 | | 12 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 13 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 14 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+---------------------+---------+---------+--------+ 14 rows in set (0.00 sec) mysql> alter table emp change column empno empno int unsigned NOT NULL auto_increment,ADD PRIMARY KEY (empno); Query OK, 14 rows affected (0.36 sec) Records: 14 Duplicates: 0 Warnings: 0 mysql> INSERT INTO EMP(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES('MARTIN', 'SALESMAN',7698,now(), 1250, 1400, 30); Query OK, 1 row affected (0.08 sec) mysql> select * from emp; +-------+--------+-----------+------+---------------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+---------------------+---------+---------+--------+ | 1 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | | 2 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | | 3 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | | 4 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 5 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | | 6 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 7 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | | 8 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 | | 9 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 10 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | | 11 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 | | 12 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 13 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 14 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | | 15 | MARTIN | SALESMAN | 7698 | 2007-10-24 11:13:15 | 1250.00 | 1400.00 | 30 | +-------+--------+-----------+------+---------------------+---------+---------+--------+ 15 rows in set (0.01 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 15 | +------------------+ 1 row in set (0.00 sec)
last_insert_id()函数可获得自增列自动生成的最后一个编号。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值，则该函数返回0。
mysql> delete from emp where empno=15; Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO EMP(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES('MARTIN', 'SALESMAN',7698,now(), 1250, 1400, 30); Query OK, 1 row affected (0.03 sec) mysql> select * from emp order by empno desc limit 5; +-------+--------+----------+------+---------------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+---------------------+---------+---------+--------+ | 16 | MARTIN | SALESMAN | 7698 | 2007-10-24 11:21:17 | 1250.00 | 1400.00 | 30 | | 14 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | | 13 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 12 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 11 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 | +-------+--------+----------+------+---------------------+---------+---------+--------+ 5 rows in set (0.00 sec) mysql> delete from emp where empno=16; Query OK, 1 row affected (0.03 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> alter table emp AUTO_INCREMENT=15; Query OK, 14 rows affected (0.27 sec) Records: 14 Duplicates: 0 Warnings: 0 mysql> INSERT INTO EMP(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES('MARTIN', 'SALESMAN',7698,now(), 1250, 1400, 30); Query OK, 1 row affected (0.03 sec) mysql> select * from emp order by empno desc limit 5; +-------+--------+----------+------+---------------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+---------------------+---------+---------+--------+ | 15 | MARTIN | SALESMAN | 7698 | 2007-10-24 11:33:53 | 1250.00 | 1400.00 | 30 | | 14 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | | 13 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 12 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 11 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 | +-------+--------+----------+------+---------------------+---------+---------+--------+ 5 rows in set (0.00 sec) oracle>CREATE SEQUENCE emp_sequence INCREMENT BY 1.START WITH 1 NOMAXVALUE NOCYCLE CACHE 10; Sequence created. INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 oracle>INSERT INTO emp VALUES (emp_sequence.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20); 1 row created. oracle>select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------- -------------------- ------------------ ---------- --------- ---------- ---------- ---------- 1 SMITH CLERK 7902 17-DEC-80 800 20 2 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 3 WARD SALESMAN 7698 22-FEB-81 1250 500 30 4 JONES MANAGER 7839 02-APR-81 2975 20 5 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 6 BLAKE MANAGER 7839 01-MAY-81 2850 30 7 CLARK MANAGER 7839 09-JUN-81 2450 10 8 SCOTT ANALYST 7566 09-DEC-82 3000 20 9 KING PRESIDENT 17-NOV-81 5000 10 10 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 11 ADAMS CLERK 7788 12-JAN-83 1100 20 12 JAMES CLERK 7698 03-DEC-81 950 30 13 FORD ANALYST 7566 03-DEC-81 3000 20 14 MILLER CLERK 7782 23-JAN-82 1300 10 1 LEWIS CLERK 7902 24-OCT-07 1200 20 15 rows selected. oracle>rollback; Rollback complete. oracle>ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY(empno); Table altered. oracle>INSERT INTO emp VALUES (emp_sequence.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20); oracle>INSERT INTO emp VALUES (emp_sequence.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20); INSERT INTO emp VALUES (emp_sequence.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20) * ERROR at line 1: ORA-00001: unique constraint (MYTEST.PK_EMP) violated oracle>alter sequence emp_sequence INCREMENT BY 1.START WITH 15 NOMAXVALUE NOCYCLE; oracle>alter sequence emp_sequence INCREMENT BY 1.START WITH 15 NOMAXVALUE NOCYCLE; alter sequence emp_sequence INCREMENT BY 1.START WITH 15 NOMAXVALUE NOCYCLE * ERROR at line 1: ORA-02283: cannot alter starting sequence number
你或者是该sequence的owner，或者有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值，必须 drop sequence 再 re-create .
oracle>drop SEQUENCE emp_sequence; Sequence dropped. oracle>CREATE SEQUENCE emp_sequence INCREMENT BY 1.START WITH 15 NOMAXVALUE NOCYCLE; Sequence created. oracle>INSERT INTO emp VALUES (emp_sequence.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20); 1 row created. oracle>select * from (select * from emp order by empno desc) where ROWNUM<6; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------- -------------------- ------------------ ---------- --------- ---------- ---------- ---------- 15 LEWIS CLERK 7902 24-OCT-07 1200 20 14 MILLER CLERK 7782 23-JAN-82 1300 10 13 FORD ANALYST 7566 03-DEC-81 3000 20 12 JAMES CLERK 7698 03-DEC-81 950 30 11 ADAMS CLERK 7788 12-JAN-83 1100 20 oracle>select emp_sequence.nextval from dual; NEXTVAL ---------- 16 oracle>select emp_sequence.nextval from dual; NEXTVAL ---------- 17
mysql一个表只能有一个AUTO_INCREMENT属性,且该属性必须为主键的一部分,且初始值可以通过alter来修改。oracle的sequence是数据库的一个object，不一定非得在主键，不能通过alter初始值，只能删除重建，且sequence的灵活性明显比较大。
Leave a Reply.
gary 1F
07月 5th, 2010 at 15:06
粗看下,oracle sequence比mysql table内的auto incremental列强.
据文档,Mysql Innodb 可能出现性能问题.
When accessing the auto-increment counter, InnoDB uses a special table-level AUTO-INC lock that it keeps to the end of the current SQL statement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing an AUTO_INCREMENT column. Nevertheless, two transactions cannot have the AUTO-INC lock on the same table simultaneously, which can have a performance impact if the AUTO-INC lock is held for a long time. That might be the case for a statement such as INSERT INTO t1 … SELECT … FROM t2 that inserts all rows from one table into another.