| |
Encountered in the work of a special task today, it is to conduct two additional self-swap values change.
SQL Server platform to modify the auto-increment value
Since treated before migration sql server database, tried to change its auto-increment value, but modified from the value added by the SQL statement is strictly not allowed, direct error (Can not update identity column 'from additional name' ). sql server I tested 2008,2012 and 2014, are not allowed to change the auto-increment value, I believe that the environment SQL Server 2005+ are not allowed to change the field column values.
If you have to modify the SQL Server platform from added value, since it would require additional manual property, and then modify the column values, and then manually add the amendment is successful auto-increment property. If modified from the inclusion in the build environment, I suggest in my spare time (after 0:00, the user platform or website using very little time) to deal with such problems. Large volumes of data and multi-table associated with it through T-SQL to change. The biggest drawback is the method to assist canceled by hand and adding self-energizing properties.
There is also a method, the first data to be modified finishing as T-SQL script insertion, and then delete these data to be modified, by inserting data display to achieve. This approach applies not less to change a single table record, when the method more flexible.
A more simple way, that is, if only several pieces, then let operators who re-publish the information, delete the previous data.
There are online repaired by T-SQL statements cancel the self-energizing properties, in SQL Server 2005+ I did not pass the test environment, the corresponding T-SQL code is as follows:
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 platform modified from value added
mysql platform modified from value added, some trouble. mysql exists in an auto-increment, if its engine is myisam, the column can be independent primary key column, may be a composite primary key column, the column must be mainly associated with the column bonds; if its engine is innodb, the column must be independent primary key column. To directly modify the two auto-increment value swap change, it is definitely impossible.
The method I used is from two additional values (eg 1,2) is divided into the following three steps to achieve:
1, first modify the value added from 1 to 0;
2, and then modify the value added from 2 to 1;
3, and then modify the value added from 0 to 2;
Two data engine test environment are mysql 5.6.
Innodb database engine under the premise of the specific mysql test code as follows:
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;
- Directly modify infeasible
- 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, using the method of exchanging two numbers.
2, introduced in the middle of the best value <= 0.
3, only to provide a solution, the method can also be used to modify the sql server platform (1, to cancel a small amount of data since the last increase in property changes increase the self-energizing properties, 2, finishing T-SQL script reinsert ---- when can; 3, manually re-add operations staff, but also a small amount of data in the case).
The database engine under the premise myisam, specific mysql test code as follows:
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;
note:
1, changes in the above test is not feasible.
2, asks "the first update and the following select do see the modified value, but the subsequent sql continue, but are being given to restore the status before the unmodified," this is unclear, need to continue to study.
Oracle platform no contact, I do not know, familiar with the oracle platform blog friends to be tested or for which a summary is given from additional changes. |
|
|
|