SqlServer Mysql数据库修改自增列的值及相应问题的解决方案
由于之前处理过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平台的博友针对其自增列的变更做个测试或给出个总结。
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 SqlServer Mysql数据库修改自增列的值及相应问题的解决方案
相关文章
- MySQL数据库设计:最佳实践与实施指南(mysql设计)
- MySQL数据库比对:快速、高效的结果”(mysql数据库比对工具)
- MySQL数据库修复:给您带来的专业解决方案(mysql数据库修复大师)
- mysql在Linux系统中安装MySQL数据库(linux下装)
- MySQL:灵活的富文本存储方案(mysql富文本)
- MySQL连接数过载警示(mysql连接数过多)
- 「MySQL 数据库面试题大揭秘」(mysql数据库面试题)
- 「MySQL前台工具推荐:让你的数据库管理更简单!」(mysql前台工具)
- 修改MySQL服务器时间的方法实录(修改mysql系统时间)
- 优雅的MySQL数据库自动化编号实现方法(mysql数据库自动编号)
- MySQL集群:实现数据库高可用性(mysql的集群)
- MySQL浏览器:快速轻松的数据库管理方式(mysql浏览器)
- 如何在MySQL中查询日期段数据?(mysql日期段)
- 深入解析MySQL链接参数,优化数据库性能(mysql链接参数)
- 解析MySQL的extra参数,提升数据库性能(mysql的extra)
- 「MySQL读写分离软件」:提高数据库性能的不二之选(mysql读写分离软件)
- 深入探究MySQL数据库,解读最全面的MySQL大全!(mysql大全)
- Mysql数据库的网络备份策略(net mysql 备份)
- MySQL实现人员登录功能详解(mysql中人员登录)
- MySQL 中 Cast 函数的使用方法(mysql中cast用法)
- 从sql中刷新MySQL数据库(.sql刷mysql)
- 从csv文件到MySQL数据库简单而有效的数据迁移方案(.csv转mysql)
- MySQL教程创建新用户(mysql中创建新的用户)
- 简单易懂的教程如何在MySQL中更改引擎(mysql下更改引擎)