How to add AUTO_INCREMENT to an existing column?

How do I add auto_increment to an existing column of a MySQL table?

I think you want to MODIFY the column as described for the ALTER TABLE command. It might be something like this:

ALTER TABLE users MODIFY id INTEGER NOT NULL AUTO_INCREMENT;

Before running above ensure that id column has a Primary index.

Method to add AUTO_INCREMENT to a table with data while avoiding “Duplicate entry” error:

1. Make a copy of the table with the data using INSERT SELECT:

   CREATE TABLE backupTable LIKE originalTable; 
   INSERT backupTable SELECT * FROM originalTable;
   

2. Delete data from originalTable (to remove duplicate entries):

   TRUNCATE TABLE originalTable;
   

3. To add AUTO_INCREMENT and PRIMARY KEY

   ALTER TABLE originalTable ADD id INT PRIMARY KEY AUTO_INCREMENT;
   

4. Copy data back to originalTable (do not include the newly created column (id), since it will be automatically populated)

   INSERT originalTable (col1, col2, col3) 
   SELECT col1, col2,col3
   FROM backupTable;
   

5. Delete backupTable:

   DROP TABLE backupTable;
   

I hope this is useful!

More on the duplication of tables using CREATE LIKE:

Duplicating a MySQL table, indexes and data

Alter table table_name modify column_name datatype(length) AUTO_INCREMENT PRIMARY KEY

You should add primary key to auto increment, otherwise you got error in mysql.