Insert index column in table with large data
now i have a table with some columns and a large amount of data:
Table1
Column1 Column2 Column3
data1(string) data2 data3
At the moment, the primary key is column1 typed with str开发者_Go百科ing. I need to add a new column Column0 with seriel interger, an index really. How can i do it using an mysql index?
Something like this
//First add a extra column.
ALTER TABLE table1
ADD COLUMN column0 INTEGER NOT NULL;
//Fill the column with incrementing integers in the same order as the old index.
UPDATE table1 SET column0 = @rank:= @rank + 1
CROSS JOIN (SELECT @rank:= 0) init_rank
ORDER BY column1 ASC;
//Remove the old primary key.
ALTER TABLE table1 DROP PRIMARY KEY;
//Set column0 as the new PK.
ALTER TABLE table1 CHANGE COLUMN column0 PRIMARY KEY AUTO_INCREMENT;
First you have to introduce the new column:
ALTER TABLE Table1 ADD COLUMN Column0 INTEGER(10)
The insert the ID in the new column in any way you want them; can be done by a script for example.
IF this is done, redefine the PK:
ALTER TABLE Table1 DROP PRIMARY KEY;
Insert the auto-increment:
ALTER TABLE Table1 change column Column0 PRIMARY KEY AUTO_INCREMENT;
Make sure your auto_increment value is at the value your last id is:
ALTER TABLE Table1 AUTO_INCREMENT = value_of_max_id+1_you_have;
精彩评论