Sequencing an Unsequenced Table
Problem
You forgot to include an
AUTO_INCREMENT
column when you created a table. Is
it too late?
Solution
No, just add one using
ALTER
TABLE
. MySQL
will create the column and number the rows automatically.
Discussion
To add a sequence to a table that doesn’t currently
contain one, use ALTER
TABLE
to
create an AUTO_INCREMENT
column. Suppose you have
a table t
that contains name
and age
columns, but no sequence column:
+----------+------+ | name | age | +----------+------+ | boris | 47 | | clarence | 62 | | abner | 53 | +----------+------+
You can 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 automatically. It’s not necessary 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 would 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);
For
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.