SqlServer Mysql数据库修改自增列的值及相应问题的解决方案

这篇文章主要介绍了SqlServer Mysql数据库修改自增列的值及相应问题的解决方案的相关资料,需要的朋友可以参考下

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代码如下:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

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;

执行后的结果如下:

SqlServer Mysql数据库修改自增列的值及相应问题的解决方案

MySQL 平台修改自增列值

mysql平台修改自增列值,有些麻烦的。mysql中存在自增列,如果其引擎是myisam,则该列可以为独立主键列,也可以为复合主键列,即该列必须为主键的关联列;如果其引擎是innodb,则该列必须是独立主键列。要直接修改两个自增列值对调变更,肯定是不行的。

我采用的方法是将两个自增列值(比如1、2)分为以下三个步骤来实现:
1、先将自增列值为1的修改为0;
2、再将自增列值为2的修改为1;
3、再将自增列值为0的修改为2;

以下两种数据引擎的测试环境均是mysql 5.6。

数据库引擎为innodb的前提下,具体的mysql测试代码如下:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

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;

未修改前的数据表结果,如下图:

SqlServer Mysql数据库修改自增列的值及相应问题的解决方案

修改后的数据表结果,如下图:

SqlServer Mysql数据库修改自增列的值及相应问题的解决方案

注意:

1、采用了两个数字进行交换的方法。
2、引入的中间值最好<=0的数字。
3、仅仅提供一种解决方法,也可采用sql server平台的修改方法(1、先取消自增属性后变更最后增加自增属性,2、整理T-SQL脚本重新插入—-小数据量时可以;3、运营人员手工重新添加,也是数据量小的情况下)。

数据库引擎为myisam的前提下,具体的mysql测试代码如下:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

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平台的博友针对其自增列的变更做个测试或给出个总结。

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/26752/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-09-28 8:20:40
下一篇 2022-09-28 8:20:46

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!