<!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} @font-face {font-family:"/@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:宋体;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} /* List Definitions */ @list l0 {mso-list-id:54815324; mso-list-type:hybrid; mso-list-template-ids:1437342586 67698705 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-text:"%1/)"; mso-level-tab-stop:.5in; mso-level-number-position:left; text-indent:-.25in;} ol {margin-bottom:0in;} ul {margin-bottom:0in;} -->
Oracle 中可以创建 Sequence,下面创建一个 Sequence
CREATE SEQUENCE mysequence START WITH 1 INCREMENT BY 10
此 SEQUENCE的下一个值可以用 mysequence.nextval获得,比如
SELECT mysequence.nextval from dual.
或者
Insert into table( id, … ) values( mysequence.nextval, …)
MySQL不支持 Sequence, 可以用 stored procedure或 function模拟 Oracle Sequence
1) stored procedure 方法
创建数据库表:
create table mysequence
(
Id bigint not null auto_increment;
);
创建存储过程(使用动态 SQL):
DELIMITER $$
drop PROCEDURE if exists nextval$$
CREATE PROCEDURE nextval( seq_name varchar(64) )
BEGIN
SET @sql= concat( 'insert into ', seq_name, ' values(NULL)');
PREPARE s1 FROM @sql;
execute s1;
SET @sql = concat ( 'DELETE FROM ', seq_name);
PREPARE s2 FROM @sql;
execute s2;
select LAST_INSERT_ID();
END
$$
DELIMITER ;
调用存储过程获得 mysequence的下一个值:
Call { nextval(‘mysequence’) }
将获得的值插入表中:
Insert into mytable( id,…) values( generated_id, …)
2) function 方法
同样创建数据库表:
create table mysequence
(
Id bigint not null auto_increment;
);
创建一个函数:
DELIMITER$$
Drop function if exists `mysequence.nextval`$$
create function ` mysequence.nextval`()
RETURNS bigint
NOT DETERMINISTIC
READS SQL DATA
BEGIN
insert mysequence values( NULL );
delete from mysequence;
return LAST_INSERT_ID();
END
$$
DELIMITER ;
将 mysequence的下一个值插入表中:
Insert into mytable( id, … ) values( `mysequence.nextval`(), …);
第二种方法比较接近 Oracle的 Sequence使用方法