How to add auto increment primary key based on an order of column?

Question

I need to add an auto increment id to an already existing table. I did:

ALTER TABLE table_name ADD column_name INT NOT NULL AUTO_INCREMENT FIRST ,
ADD PRIMARY KEY (column_name)

However, the auto numbering wasnt based on any particular column order. I want mysql to smartly put auto numbers based on the order of certain column. Is it possible?

Most of the answers out there tell you how to add auto increment fields, not how to control those numbers in already existing table.

Answer 1

Note that MySQL table can only have one column with AUTO_INCREMENT attribute.

Assuming the table doesn't have a primary key:

ALTER TABLE table_name ADD COLUMN new_id INT NOT NULL;
SET @x = 0;
UPDATE table_name SET new_id = (@x:=@x+1) ORDER BY whateveryouwant ASC;
ALTER TABLE table_name ADD PRIMARY KEY new_id (new_id);
ALTER TABLE table_name CHANGE new_id new_id INT NOT NULL AUTO_INCREMENT;

Assuming the table already has a NON-incremented primary key:

Just ommit the PRIMARY keyword in the fourth command.

Answer 2

1. Add a new field - ALTER TABLE table_name ADD column_name INT FIRTS;
2. Populate data in the new field.

3. Make this field as part of primary key and add AUTO_INCREMENT option -

   ALTER TABLE table_name CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id);

EDIT:

Try this plan:

1. Create another table, the same as table_name.

2. Populate new table with sorted data from table_name table:

   INSERT INTO temp_table_name SELECT * FROM table_name ORDER BY name;

3. Empty table_name table:

   TRUNCATE TABLE table_name;

4. Alter table_name table, add new auto increment field and make it primary (your code).

5. Copy data from temp. table to the table_name:

   INSERT INTO table_name SELECT NULL, t.* FROM temp_table_name t;