<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" />

oracle和mysql設定自增欄位

相關文章