<div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <div style="text-align:left;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> <div style="color:#000000;background-color:inherit;"> <span style="background-color:inherit;"><span style="color:#FF0000;background-color:inherit;">Oracle：</span></span> </div> <div style="color:#000000;background-color:inherit;"> 把表he的id列设成自增的。 </div> <div style="color:#000000;background-color:inherit;"> 1创建测试表： </div> <div style="color:#000000;background-color:inherit;"> create table<span style="background-color:inherit;"> </span><span style="color:#FF0000;background-color:inherit;">HE</span> </div> <div style="color:#000000;background-color:inherit;"> ( </div> <div style="color:#000000;background-color:inherit;">   id   INT， </div> <div style="color:#000000;background-color:inherit;">   name VARCHAR2(40), </div> <div style="color:#000000;background-color:inherit;">   sex  VARCHAR2(10) </div> <div style="color:#000000;background-color:inherit;"> ) </div> <div style="color:#000000;background-color:inherit;"> （1）建立sequence </div> <div style="color:#000000;background-color:inherit;">  create sequence<span style="background-color:inherit;"> </span><span style="color:#FF0000;background-color:inherit;">he_seq</span>  <div style="background-color:inherit;"> increment by 1    </div> <div style="background-color:inherit;"> start with 1  </div> <div style="background-color:inherit;"> nomaxvalue  </div> <div style="background-color:inherit;"> nominvalue  </div> <div style="background-color:inherit;"> nocache  </div> </div> <div style="color:#000000;background-color:inherit;"> （2）建立触发器 </div> <div style="color:#000000;background-color:inherit;"> <span style="font-size:14px;background-color:inherit;">create or replace trigger<span style="background-color:inherit;"> </span><span style="color:#FF0000;background-color:inherit;">tri_he_id</span></span> </div> <div style="background-color:inherit;"> <div style="color:#000000;background-color:inherit;"> <span style="background-color:inherit;">before insert on  <span style="color:#FF0000;background-color:inherit;">he</span><br style="background-color:inherit;" /> </span> </div> <div style="color:#000000;background-color:inherit;"> <span style="background-color:inherit;"><span style="background-color:inherit;">for</span></span><span style="background-color:inherit;"> </span><span style="background-color:inherit;">each row</span> </div> <div style="color:#000000;background-color:inherit;"> <span style="background-color:inherit;">declare  </span> </div> <div style="color:#000000;background-color:inherit;"> <span style="background-color:inherit;"><span style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;">nextid number;<span style="background-color:inherit;"> </span></span><span style="background-color:inherit;"> </span>           --定义一个变量</span><span style="font-size:14px;background-color:inherit;">  名字为<span style="background-color:inherit;"> </span><span style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;">nextid<span style="background-color:inherit;"> </span></span> </span> </div> <div style="color:#000000;background-color:inherit;"> <span style="background-color:inherit;">begin</span> </div> <div style="color:#000000;background-color:inherit;"> <span style="background-color:inherit;"><span style="background-color:inherit;">select  he_seq.nextval          --<span style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;">he_seq.nextval表示取序列<span style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;">he_seq的增加之后的值，</span></span></span><br style="background-color:inherit;" /> </span> </div> <div style="color:#000000;background-color:inherit;"> <span style="background-color:inherit;">into nextid  from dual;         --选出序列的值给变量<span style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;">nextid  </span></span> </div> <div style="background-color:inherit;"> <span style="color:#000000;background-color:inherit;">:</span><span style="color:#000000;background-color:inherit;"><span style="background-color:inherit;">new</span></span><span style="background-color:inherit;">.id:=nextid;               --把变量<span class="Apple-converted-space"> </span><span style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;">nextid<span style="background-color:inherit;"> 的值赋给</span></span> <span class="Apple-converted-space"> </span><span style="text-align:left;color:#FF0000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;">he表的id列，</span><span style="text-align:left;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;">固定格式   <span style="color:#FF0000;background-color:inherit;"><span style="text-align:left;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:inherit;">:</span><span style="text-align:left;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:inherit;"><span style="background-color:inherit;">new</span></span><span style="text-align:left;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:inherit;">.列名：</span></span></span></span> </div> <div style="color:#000000;background-color:inherit;">  <span style="background-color:inherit;">end<span style="background-color:inherit;"> </span><span style="background-color:inherit;">tri_he_id</span>;</span> </div> <div style="color:#000000;background-color:inherit;"> <div style="background-color:inherit;"> <span style="font-size:14px;background-color:inherit;">3，测试；</span> </div> </div> <div style="color:#000000;background-color:inherit;"> SQL> insert into he (name,sex) values('liuwenhe','m'); </div> <div style="color:#000000;background-color:inherit;"> 1 row created. </div> <div style="color:#000000;background-color:inherit;"> SQL> insert into he (name,sex) values('dashuai','m'); </div> <div style="color:#000000;background-color:inherit;"> 1 row created. </div> <div style="color:#000000;background-color:inherit;"> SQL> commit; </div> <div style="color:#000000;background-color:inherit;"> Commit complete. </div> <div style="color:#000000;background-color:inherit;"> SQL> select * from he; </div> <div style="color:#000000;background-color:inherit;">         ID NAME                                     SEX </div> <div style="color:#000000;background-color:inherit;"> ---------- ---------------------------------------- ---------- </div> <div style="color:#000000;background-color:inherit;">        <span style="background-color:inherit;"> </span><span style="color:#FF0000;background-color:inherit;"> <span style="background-color:inherit;">1</span></span><span style="background-color:inherit;"> </span>liuwenhe                                 m </div> <div style="color:#000000;background-color:inherit;">        <span style="color:#FF0000;background-color:inherit;"><span style="background-color:inherit;"> </span> <span style="background-color:inherit;">2</span></span><span style="background-color:inherit;"> </span>dashuai                                   m </div> </div> </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> <br style="background-color:inherit;" /> </div> </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> <span style="font-size:14px;background-color:inherit;">对sequence说明</span> </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> increment by  用于指定序列增量，默认值 1，如果指定的是正整数，则序列号自动递增， </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> 如果指定的是负数，则自动递减。  </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> start with  用于指定序列生成器生成的第一个序列号?当序列号顺序递增时默认值为序列 </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> 号的最小值当序列号顺序递减时默认值为序列号的最大值。  </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> Maxvalue:用于指定序列生成器可以生成的组大序列号，必须大于或等于start with并且 </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> 必须大于minvalue,默认为nomaxvalue。  </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> Minvalue：用于指定序列生成器可以生成的最小序列号，必须小于或等于starr with并且 </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> 必须小于maxvalue,默认值为nominvalue。  </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> Cycle：用于指定在达到序列的最大值或最小值之后是否继续生成序列号，默认为nocycle。  </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> Cache:用于指定在内存中可以预分配的序列号个数，默认值20。  </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> <span style="background-color:#FFFFFF;">在sequence中应注意? </span> </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> 1、第一次NEXTVAL返回的是初始值，随后的NEXTVAL会自动增加你定义的INCREMENT BY </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> 值，然后返回增加后的值。CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> 初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以 </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> 如果你在同一个语句里面使用多个NEXTVAL?其值就是不一样的。  </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> 2、如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence?这样存取的快些。 </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> cache里面的取完后，oracle自动再取一组到cache。使用cache或许会跳号，比如数据 </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> 库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create  </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14.39px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> sequence的时候用nocache防止这种情况。 </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> <br style="background-color:inherit;" /> </div> <div style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;"> <br style="background-color:inherit;" /> </div> <div style="text-align:left;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;">  <span style="color:#FF0000;line-height:1.5;background-color:inherit;">MySQL：</span><span style="line-height:1.5;background-color:inherit;">mysql有自增字段的类型（<span style="text-align:left;color:#000000;font-family:微软雅黑;font-size:14px;font-style:normal;font-weight:normal;background-color:#FFFFFF;">auto_increment）</span>，不必要像oracle那么繁琐。</span> </div> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> create table test(id int(3) </div> <span style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> </span><span style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;">auto_increment</span><span style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;">primary key</span> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> ,tname char(10)); <div style="background-color:inherit;"> 注意自增的字段必须是唯一建，不加唯一索引，不行，不一定是主键 </div> <div style="background-color:inherit;"> <span style="background-color:inherit;"><span style="background-color:inherit;">mysql></span>alter table Product_0630_bak  add id int(11) auto_increment  unique key;  ##这样就把字段加在了最后，</span> </div> <div style="background-color:inherit;"> <span style="background-color:inherit;">mysql> alter table Product_0630_bak  add id int(11) auto_increment  unique key   first;  ##这样就把字段加在了最前面，</span> </div> <div style="background-color:inherit;"> <span style="background-color:inherit;">注意：</span><span style="line-height:1.5;background-color:inherit;">如果给已经存在数据的一个表，添加自增字段，那添加成功后，这个自增字段就有相应的自增数据了，</span> </div> <div style="background-color:inherit;"> <span style="background-color:inherit;"><br style="background-color:inherit;" /> </span> </div> <div style="background-color:inherit;"> <br style="background-color:inherit;" /> </div> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <br style="background-color:inherit;" /> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <br style="background-color:inherit;" /> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <br style="background-color:inherit;" /> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <div style="background-color:inherit;"> <br style="background-color:inherit;" /> <div style="background-color:inherit;"> <div style="background-color:inherit;"> <div style="background-color:inherit;"> <br style="background-color:inherit;" /> </div> <div style="background-color:inherit;"> <div style="background-color:inherit;"> <div style="background-color:inherit;"> <div style="background-color:inherit;"> <div style="background-color:inherit;"> <div style="background-color:inherit;"> <div style="background-color:inherit;"> <span style="background-color:inherit;">    </span> </div> </div> </div> </div> </div> </div> </div> <div style="background-color:inherit;"> <br style="background-color:inherit;" /> </div> <div style="background-color:inherit;"> <br style="background-color:inherit;" /> </div> </div> </div> </div> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <br style="background-color:inherit;" /> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <br style="background-color:inherit;" /> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <br style="background-color:inherit;" /> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <br style="background-color:inherit;" /> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <br style="background-color:inherit;" /> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <br style="background-color:inherit;" /> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <br style="background-color:inherit;" /> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <br style="background-color:inherit;" /> </div> <div style="font:14px/21px 微软雅黑;text-align:left;color:#000000;text-transform:none;text-indent:0px;letter-spacing:normal;word-spacing:0px;white-space:normal;font-size-adjust:none;font-stretch:normal;background-color:#FFFFFF;-webkit-text-stroke-width:0px;"> <br style="background-color:inherit;" /> </div> <br class="Apple-interchange-newline" />

ITPub博客

oracle和mysql设置自增字段

最新文章