If you insert rows into a table that has an
AUTO_INCREMENT
column
and never delete any of them, values in the column form an unbroken
sequence. But if you delete rows, the sequence begins to have holes in
it. For example, Junior’s insect
table currently looks something like this, with gaps in the sequence
(assuming that you’ve inserted the cricket and moth rows shown in the
preceding section on retrieving sequence values):
mysql>SELECT * FROM insect ORDER BY id;
+----+-------------------+------------+------------+
| id | name | date | origin |
+----+-------------------+------------+------------+
| 1 | housefly | 2006-09-10 | kitchen |
| 3 | grasshopper | 2006-09-10 | front yard |
| 4 | stink bug | 2006-09-10 | front yard |
| 5 | cabbage butterfly | 2006-09-10 | garden |
| 6 | ant | 2006-09-10 | back yard |
| 9 | cricket | 2006-09-11 | basement |
| 10 | moth | 2006-09-14 | windowsill |
+----+-------------------+------------+------------+
MySQL won’t attempt to eliminate these gaps by filling in the
unused values when you insert new rows. People who don’t like this
behavior tend to resequence AUTO_INCREMENT
columns periodically to
eliminate the holes. The next few recipes show how to do that. It’s
also possible to extend the range of an existing sequence, add a
sequence column to a table that doesn’t currently have one, force
deleted values at the top of a sequence to be reused, or specify an
initial sequence value when creating or resequencing a table.
Before you decide to resequence an AUTO_INCREMENT
column, consider whether you
really want or need to do so. It’s unnecessary in most cases. In fact,
renumbering a sequence sometimes can cause you real problems. For
example, you should not resequence a column
containing values that are referenced by another table. Renumbering
the values destroys their correspondence to values in the other table,
making it impossible to properly relate rows in the two tables to each
other.
Here are reasons that I have seen advanced for resequencing a column:
Sometimes the desire to renumber a column is for aesthetic reasons. People seem to prefer unbroken sequences to sequences with holes in them. If this is why you want to resequence, there’s probably not much I can say to convince you otherwise. Nevertheless, it’s not a particularly good reason.
The impetus for resequencing may stem from the notion that
doing so “compacts” a sequence column by removing
gaps and enables MySQL to run statements more quickly. This is
not true. MySQL doesn’t care whether there are holes, and there
is no performance gain to be had by renumbering an AUTO_INCREMENT
column. In fact,
resequencing affects performance negatively in the sense that
the table remains locked while MySQL performs the
operation—which may take a nontrivial amount of time for a large
table. Other clients can read from the table while this is
happening, but clients that are trying to insert new rows must
wait until the operation is complete.
The upper limit of a sequence column is determined by the
column’s data type. If an AUTO_INCREMENT
sequence is approaching
the upper limit of its data type, renumbering packs the sequence
and frees up more values at the top. This may be a legitimate
reason to resequence a column, but it is still unnecessary in
many cases to do so. You may be able to expand the column’s
range to increase its upper limit without changing the values
stored in the column. (See Extending the Range of a Sequence Column.)
If you’re determined to resequence a column, despite my advice
not to, it’s easy to do: drop the column from the table; then put it
back. MySQL will renumber the values in the column in unbroken
sequence. The following example shows how to renumber the id
values in the insect
table using this technique:
mysql>ALTER TABLE insect DROP id;
mysql>ALTER TABLE insect
->ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
->ADD PRIMARY KEY (id);
The first
ALTER
TABLE
statement gets rid of the id
column (and as a result also drops the
PRIMARY
KEY
, because the column to which it refers
is no longer present). The second statement restores the column to the
table and establishes it as the PRIMARY
KEY
. (The FIRST
keyword places the column first in the
table, which is where it was originally. Normally, ADD
puts columns at the end of the table.)
When you add an AUTO_INCREMENT
column to a table, MySQL
automatically numbers all the rows consecutively, so the resulting
contents of the insect
table look
like this:
mysql>SELECT * FROM insect ORDER BY id;
+----+-------------------+------------+------------+
| id | name | date | origin |
+----+-------------------+------------+------------+
| 1 | housefly | 2006-09-10 | kitchen |
| 2 | grasshopper | 2006-09-10 | front yard |
| 3 | stink bug | 2006-09-10 | front yard |
| 4 | cabbage butterfly | 2006-09-10 | garden |
| 5 | ant | 2006-09-10 | back yard |
| 6 | cricket | 2006-09-11 | basement |
| 7 | moth | 2006-09-14 | windowsill |
+----+-------------------+------------+------------+
One problem with resequencing a column using separate ALTER
TABLE
statements is that the table will be
without that column for the interval between the two operations. This
might cause difficulties for other clients that try to access the
table during that time. To prevent this from happening, perform both
operations with a single ALTER
TABLE
statement:
mysql>ALTER TABLE insect
->DROP id,
->ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST;
MySQL permits multiple actions to be done with ALTER
TABLE
(something not true for all database
systems). However, notice that this multiple-action statement is not
simply a concatenation of the two single-action ALTER
TABLE
statements. The difference is that it
is unnecessary to reestablish the PRIMARY
KEY
: MySQL doesn’t drop it unless the
indexed column is missing after all the actions specified in the
ALTER
TABLE
statement have been
performed.
3.135.247.11