How do I update a table to add a primary key and u

问题:

I have a table with 20,000 rows of data that I imported but I forgot to put a primary key on it so that each row has a unique key.

I want the first row to start at ID 1 and increment all the way up to the last row and finish at ID 20000. How do I update all of the rows with a single query?

I'm using MySQL. Have tried using PhpMyAdmin but it wouldn't do it.

回答1:

After adding a new ID column (don't set as a primary key just yet, and don't turn on auto increment) run:

SET @index = 1;
UPDATE tablename SET ID = (@index:=@index+1);

This sets an incrementing ID value starting from 1 onto each of your existing rows thus solving the duplicate key issue you would face if you tried to insert a new primary key column after data has been already entered.

Once this is done you can set the ID column as a primary key with auto increment.

回答2:

Change tablename and run the following SQL statement. It will create a new primary key called id and populate it starting with the first row as 1.

ALTER TABLE `tablename` ADD COLUMN id INT(10) AUTO_INCREMENT NOT NULL FIRST, ADD PRIMARY KEY (id);