sql query with auto-increment assignment to column?

Question

MySQL

Greetings!
The wp_options table has an option_id column. How to correctly compose an sql query so that:
1. modify option_id to auto-increment + the 'primary key' flag is set.
2. All values ​​in this column are now unique. At the moment they are equal to zero, but it is necessary that they line up one after another approximately in the variant 1, 2, 3, 4, 5, etc. ...
Thank you!

Answer 1

Found a solution:

SET @id = 0;
UPDATE wp_options SET
  option_id = (SELECT @id := @id + 1);

ALTER TABLE wp_options MODIFY COLUMN
  option_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY;

Answer 2

Mike Ro ,
1) Generate a number sequence and update the option_id (you can via sql if you know how, you can via php)
2) In PhpMyAdmin, set autoincrement for the field
Although I suspect that the problem is deeper, autoincrement should have been set by default. You did not create this table yourself.

Answer 3

1) create table
2) insert into table select from previous table