Generating and Using Sequences - MySQL - page 463

Discussion
To add a sequence to a table that doesn't currently contain one, use ALTER TABLE to create
an AUTO_INCREMENT column. Suppose that a table contains name and age columns, but
no sequence column:
mysql> SELECT * FROM t;
+----------+------+
| name | age |
+----------+------+
| boris | 47 |
| clarence | 62 |
| abner | 53 |
+----------+------+
Add a sequence column named id to the table as follows:
mysql> ALTER TABLE t
-> ADD id INT NOT NULL AUTO_INCREMENT,
-> ADD PRIMARY KEY (id);
mysql> SELECT * FROM t ORDER BY id;
+----------+------+----+
| name | age | id |
+----------+------+----+
| boris | 47 | 1 |
| clarence | 62 | 2 |
| abner | 53 | 3 |
+----------+------+----+
MySQL numbers the rows for you; it's unnecessary to assign the values yourself. Very
handy.
By default, ALTER TABLE adds new columns to the end of the table. To place a column
at a specific position, use FIRST or AFTER at the end of the ADD clause. The following
ALTER TABLE statements are similar to the one just shown, but place the id column first
in the table or after the name column, respectively:
ALTER TABLE t
ADD id INT NOT NULL AUTO_INCREMENT FIRST ,
ADD PRIMARY KEY ( id );
ALTER TABLE t
ADD id INT NOT NULL AUTO_INCREMENT AFTER name ,
ADD PRIMARY KEY ( id );