MySQL模拟Oracle的Sequence两种方法

<!-- /* 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使用方法