SQL Server如何修改自增列值以及相應的解決方案

SQL Server如何修改自增列值以及相應的解決方案

今天工作中遇到特殊的一個任務，就是將兩個自增列值的進行對調變更。

SQL Server 平台修改自增列值由於之前處理過sql server資料庫的遷移工作，嘗試過其自增列值的變更，但是通過SQL 陳述式修改自增列值，是嚴格不允許的，直接報錯（無法更新識別欄位 ’自增列名稱‘）。sql server我測試是2008、2012和2014，都不允許變更自增列值，我相信SQL Server 2005+的環境均不允許變更欄位列值。如果非要在SQL Server 平台修改自增列值的，那就手動需要自增列屬性，然後修改該列值，修改成功後再手動添加自增列屬性。如果在產生環境修改自增列的話，建議在空閑時間（零點以後，平台或網站使用的使用者很少的時間段）來處理這類問題。資料量大且多表關聯的，那就通過T-SQL來變更。該方法最大的缺點就是要通過手工輔助取消和添加自增屬性的。還有一個方法，先將要修改的資料整理為T-SQL的插入指令碼，再刪除這批要修改的資料，在通過顯示插入資料來實現。這種方式適用於要變更不較少的單表記錄，該方法到時比較靈活的。更簡單的方法，那就是如果僅僅若干條，那就讓運營人員重新發布資訊，刪除以前的資料。還有網上通過修過T-SQL語句取消自增屬性，我在SQL Server 2005+環境測試均未通過，相應的T-SQL代碼如下： 

EXEC sys.sp_configure    @configname = 'allow updates', -- varchar(35)    @configvalue = 1； -- int EXEC sys.sp_configure    @configname = 'show advanced options'  , -- varchar(35)    @configvalue = 1； -- int RECONFIGURE WITH OVERRIDE;GO UPDATE sys.syscolumnsSET colstat = 1WHERE id = OBJECT_ID(N'PrimaryKeyAndIdentityUpdateTestDataTable', 'U')    AND name = N'ID'    AND colstat = 1; UPDATE sys.columnsSET is_identity = 0WHERE object_id = OBJECT_ID(N'PrimaryKeyAndIdentityUpdateTestDataTable', 'U')    AND name = N'ID'    AND is_identity = 1; 

執行後的結果如下：MySQL 平台修改自增列值 mysql平台修改自增列值，有些麻煩的。mysql中存在自增列，如果其引擎是myisam，則該列可以為獨立主鍵列，也可以為複合主鍵列，即該列必須為主鍵的關聯列；如果其引擎是innodb，則該列必須是獨立主鍵列。要直接修改兩個自增列值對調變更，肯定是不行的。我採用的方法是將兩個自增列值（比如1、2）分為以下三個步驟來實現：1、先將自增列值為1的修改為0；2、再將自增列值為2的修改為1；3、再將自增列值為0的修改為2；以下兩種資料引擎的測試環境均是mysql 5.6。資料庫引擎為innodb的前提下，具體的mysql測試代碼如下：

drop table if exists identity_datatable;create table identity_datatable (  id int not null AUTO_INCREMENT,   name varchar(10) not null,  primary key (id)  ) engine=innodb,default charset=utf8; insert into identity_datatable (id, name)values (1, '1'),(2,'2');insert into identity_datatable (id, name)values (3, '3'),(4,'4'); select *from identity_datatable; -- 直接修改不可行-- update identity_datatable-- set id = case when id = 1 then 2 when id = 2 then 1 end-- where id in (1, 2); update identity_datatableset id = 0where id = 1; update identity_datatableset id = 1where id = 2; update identity_datatableset id = 2where id = 0; select *from identity_datatable; 

未修改前的資料表結果，如：修改後的資料表結果，如：注���：1、採用了兩個數字進行交換的方法。2、引入的中間值最好<=0的數字。3、僅僅提供一種解決方案，也可採用sql server平台的修改方法（1、先取消自增屬性後變更最後增加自增屬性，2、整理T-SQL指令碼重新插入----小資料量時可以；3、運營人員手工重新添加，也是資料量小的情況下）。資料庫引擎為myisam的前提下，具體的mysql測試代碼如下：

drop table if exists autoincremenet_datatable_myisam;create table autoincremenet_datatable_myisam (  tid int not null,  id int not null auto_increment,  name varchar(20) not null,  primary key(id)) engine = myisam, default charset = utf8; insert into autoincremenet_datatable_myisam (tid, id, name)values(1,1,'a'),(2,2,'b'),(3,3,'c'),(4,4,'d'); select *from autoincremenet_datatable_myisam; update autoincremenet_datatable_myisamset id = 0;where id = 1; select *from autoincremenet_datatable_myisam; update autoincremenet_datatable_myisamset id = 1;where id = 2; select *from autoincremenet_datatable_myisam; update autoincremenet_datatable_myisamset id = 2;where id = 0; select *from autoincremenet_datatable_myisam; 

注意：1、以上測試中的變更不可行。2、疑問“第一條update和其後面的select確實看到了修改後的值，但是隨後的sql繼續執行，均報錯卻又恢複了未修改之前的狀態“，這個還不清楚，需要繼續研究。Oracle平台的沒有接觸，不曉得，熟悉oracle平台的博友針對其自增列的變更做個測試或給出個總結。如果博友針對我提出的疑問有更好的解釋，也請指教，謝謝。 

本文永久更新連結地址：