资源描述：
《mysql序列解决方案》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、MySQL自增长与Oracle序列的区别:自增长只能用于表中的其中一个字段自增长只能被分配给固定表的固定的某一字段，不能被多个表共用.自增长会把一个未指定或NULL值的字段自动填上.在mysql中添加序列，请看下面的实例：在MYSQL里有这样一张表:Java代码1.CREATETABLEMovie( 2.id INTNOTNULLAUTO_INCREMENT, 3.name VARCHAR(60)NOTNULL, 4.releasedYEARNOTNULL, 5.PRIMARYKEY(id) 6.)ENGI
2、NE=InnoDB; CREATETABLEMovie(idINTNOTNULLAUTO_INCREMENT,nameVARCHAR(60)NOTNULL,releasedYEARNOTNULL,PRIMARYKEY(id))ENGINE=InnoDB;Java代码1.INSERTINTOMovie(name,released)VALUES('Gladiator',2000); 2.INSERTINTOMovie(id,name,released)VALUES(NULL,'TheBourneIdentity',1998); INSERTI
3、NTOMovie(name,released)VALUES('Gladiator',2000);INSERTINTOMovie(id,name,released)VALUES(NULL,'TheBourneIdentity',1998);在ORACLE是这样的:Java代码1.CREATETABLEMovie( 2.id INTNOTNULL, 3.name VARCHAR2(60)NOTNULL, 4.releasedINTNOTNULL, 5.PRIMARYKEY(id) 6.); 7.CREATESE
4、QUENCEMovieSeq; CREATETABLEMovie(idINTNOTNULL,nameVARCHAR2(60)NOTNULL,releasedINTNOTNULL,PRIMARYKEY(id));CREATESEQUENCEMovieSeq;Java代码1.INSERTINTOMovie(id,name,released)VALUES(MovieSeq.NEXTVAL,'Gladiator',2000); INSERTINTOMovie(id,name,released)VALUES(MovieSeq.NEXTVAL,'Gla
5、diator',2000);在oracle下为表添加一个触发器，就可以实现mysql自增长功能:Java代码1.CREATEORREPLACETRIGGERBRI_MOVIE_TRG 2.BEFOREINSERTONMovie 3.FOREACHROW 4.BEGIN 5. SELECTMovieSeq.NEXTVALINTO:new.idFROMDUAL; 6.ENDBRI_MOVIE_TRG; 7.. 8.RUN; CREATEORREPLACETRIGGERBRI_MOVIE_TRGBEFOREINSERTONMovie
6、FOREACHROWBEGINSELECTMovieSeq.NEXTVALINTO:new.idFROMDUAL;ENDBRI_MOVIE_TRG;.RUN;这样，插件记录就可以成为MYSQL风格：Java代码1.INSERTINTOMovie(name,released)VALUES('TheLionKing',1994); INSERTINTOMovie(name,released)VALUES('TheLionKing',1994);下面我们来看看如何在mysql数据里使用Oracle序列语法.NEXTVAL和.CURVAL.我们假设在
7、mysql中序列的语法是:NEXTVAL(’sequence’);CURRVAL(’sequence’);SETVAL(’sequence’,value);下面就是CURRRVAL的实现方案:Java代码1.DROPTABLEIFEXISTSsequence; 2.CREATETABLEsequence( 3.name VARCHAR(50)NOTNULL, 4.current_valueINTNOTNULL, 5.increment INTNOTNULLDEFAULT1, 6.PRIMARYKE
8、Y(name) 7.)ENGINE=InnoDB; 8.INSERTINTOsequenceVALUES('MovieSeq',3,5); 9.
