程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SqlServer Mysql數據庫修改自增列的值及相應問題的解決方案

SqlServer Mysql數據庫修改自增列的值及相應問題的解決方案

編輯:關於SqlServer

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.syscolumns
SET colstat = 1
WHERE id = OBJECT_ID(N'PrimaryKeyAndIdentityUpdateTestDataTable', 'U')
AND name = N'ID'
AND colstat = 1;
UPDATE sys.columns
SET is_identity = 0
WHERE 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_datatable
set id = 0
where id = 1;
update identity_datatable
set id = 1
where id = 2;
update identity_datatable
set id = 2
where 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_myisam
set id = 0;
where id = 1;
select *
from autoincremenet_datatable_myisam;
update autoincremenet_datatable_myisam
set id = 1;
where id = 2;
select *
from autoincremenet_datatable_myisam;
update autoincremenet_datatable_myisam
set id = 2;
where id = 0;
select *
from autoincremenet_datatable_myisam;

注意:

1、以上測試中的變更不可行。

2、疑問“第一條update和其後面的select確實看到了修改後的值,但是隨後的sql繼續執行,均報錯卻又恢復了未修改之前的狀態“,這個還不清楚,需要繼續研究。

Oracle平台的沒有接觸,不曉得,熟悉oracle平台的博友針對其自增列的變更做個測試或給出個總結。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved