Recipe 11.5. Renumbering an Existing Sequence


Problem

You have gaps in a sequence column, and you want to resequence it.

Solution

Don't bother. Or at least don't do so without a good reason, of which there are very few.

Discussion

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:


Aesthetics

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.


Performance

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 operationwhich 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.


Running out of numbers

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 Section 11.6.)

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.




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net