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

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

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;

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;

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;