Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the value of the modified self-increment column in the SqlServer Mysql database and the solution to the corresponding problem. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

SQL Server platform modifies self-increment values

Because we have dealt with the migration of sql server database before, we have tried to change its self-increment value, but it is strictly not allowed to modify the self-increment value through SQL statement, and the error can be reported directly (the identity column 'self-increment column name' cannot be updated). Sql server I tested 2008, 2012 and 2014, and none of them are allowed to change self-increment values. I believe that the environment of SQL Server 2005 + does not allow field column values to be changed.

If you have to modify the self-increment value on the SQL Server platform, you need to manually add the self-increment property, then modify the column value, and then manually add the self-increment attribute after the modification is successful. If self-adding columns are modified in the build environment, it is recommended to deal with such problems in idle time (after zero, when few users are used by the platform or site). If there is a large amount of data and multiple tables are associated, then change it through T-SQL. The biggest disadvantage of this method is to cancel and add self-increasing attributes by manual assistance.

Another method is to first organize the modified data into an insert script for T-SQL, and then delete the batch of data to be modified, which is achieved by displaying the inserted data. This method is suitable for single table records with less changes, and this method is more flexible at that time.

An easier way is to ask the operator to republish the information and delete the previous data if only a few.

In addition, the self-increment attribute is cancelled by modifying the T-SQL statement on the Internet. I failed the test in the SQL Server 2005 + environment. The corresponding T-SQL code is as follows:

EXEC sys.sp_configure@configname = 'allow updates',-- varchar (35) @ configvalue = 1;-- intEXEC sys.sp_configure@configname =' show advanced options',-- varchar (35) @ configvalue = 1 -- intRECONFIGURE WITH OVERRIDE;GOUPDATE sys.syscolumnsSET colstat = 1WHERE id = OBJECT_ID (nasty PrimaryKeyAndIdentityUpdateTestDataTableworthy,'U') AND name = N'ID'AND colstat = 1N'ID'AND is_identity sys.columnsSET is_identity = 0WHERE object_id = OBJECT_ID (nasty PrimaryKeyAndIdentityUpdateTestDataTablestones,'U') AND name = TestDataTableboards,'U')

The results are as follows:

MySQL platform modifies self-increment values

It is troublesome for mysql platform to modify self-increment values. There is a self-incrementing column in mysql, which can be either a stand-alone primary key column or a compound primary key column if its engine is myisam, that is, it must be an associated column of the primary key; if its engine is innodb, the column must be an independent primary key column. It is definitely not possible to directly modify the exchange of two self-increasing values.

My method is to divide two self-increment values (such as 1 and 2) into the following three steps: 1. First, modify the self-increment value of 1 to 0, then change the self-increment value of 2 to 1, and then change the self-increment value of 0 to 2.

The test environment for the following two data engines is mysql 5.6.

If the database engine is innodb, the specific mysql test code is 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) insert into identity_datatable (id, name) values (3,'3'), (4) from identity_datatable -- it is not feasible to modify directly-- update identity_datatable-- set id = case when id = 1 then 2 when id = 2 then 1 end-- where id in (1, 2); update identity_datatableset id = 0where id = 1 1where id update identity_datatableset id = 1where id = 2 1where id update identity_datatableset id = 2where id = 0 Singapore select * from identity_datatable

The result of the data table before modification, as shown below:

The result of the modified data table is shown in the following figure:

Note:

1. The method of exchanging two numbers is adopted. 2. The introduced intermediate value is the best.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.