MYSQL: how to "reorder" a table

Question

I have a table like the following,

| id  | name   | color  |
------+--------+---------
| 1   | pear   | green  |
| 2   | apple  | red    |
| 3   | banana | yellow |
| 4   | grape  | purple |

I'd like to reorder alphabetically using the "name" column and reset the id (autoincrement) with this new order to end up with the following

| id  | name   | color  |
------+--------+---------
| 1   | apple  | red    |
| 2   | banana | yellow |
| 3   | grape  | purple |
| 4   | pear   | green  |

QUESTION: how can I do this with MYSQL?

Answer 1

The cleanest way to reset the auto increment is to create another table.

MySQL provides commands such as CREATE TABLE LIKE and RENAME TABLE that are useful.

CREATE TABLE table2 LIKE table1;

INSERT INTO table2
  SELECT * FROM table1 ORDER BY name;

DROP TABLE table1;

RENAME TABLE table2 TO table1;