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;