Recipe 11.3. The Effect of Row Deletions on Sequence Generation

Problem

You want to know what happens to a sequence when you delete rows from a table that contains an AUTO_INCREMENT column.

Solution

It depends on which rows you delete and on the storage engine.

Discussion

We have thus far considered how sequence values in an AUTO_INCREMENT column are generated for circumstances where rows are only added to a table. But it's unrealistic to assume that rows will never be deleted. What happens to the sequence then?

Refer again to Junior's bug-collection project, for which you currently have an insect table that looks like this:

mysql> SELECT * FROM insect ORDER BY id; +----+-------------------+------------+------------+ | id | name              | date       | origin     | +----+-------------------+------------+------------+ |  1 | housefly          | 2006-09-10 | kitchen    | |  2 | millipede         | 2006-09-10 | driveway   | |  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  | |  7 | ant               | 2006-09-10 | back yard  | |  8 | millbug           | 2006-09-10 | under rock | +----+-------------------+------------+------------+ 

That's about to change because after Junior remembers to bring home the written instructions for the project, you read through them and discover two things that affect the insect table's contents:

• Specimens should include only insects, not other insect-like creatures such as millipedes and millbugs.

• The purpose of the project is to collect as many different specimens as possible, not just as many specimens as possible. This means that only one ant row is allowed.

These instructions require that a few rows be removed from the insect tablespecifically those with id values 2 (millipede), 8 (millbug), and 7 (duplicate ant). Thus, despite Junior's evident disappointment at the reduction in the size of his collection, you instruct him to remove those rows by issuing a DELETE statement:

mysql> DELETE FROM insect WHERE id IN (2,8,7);             

This statement illustrates one reason why it's useful to have unique ID values: they enable you to specify any row unambiguously. The ant rows are identical except for the id value. Without that column in the insect table, it would be more difficult to delete just one of them.

After the unsuitable rows have been removed, the resulting table contents become:

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  | +----+-------------------+------------+------------+ 

The sequence in the id column now has a hole (row 2 is missing) and the values 7 and 8 at the top of the sequence are no longer present. How do these deletions affect future insert operations? What sequence number will the next new row get?

Removing row 2 created a gap in the middle of the sequence. This has no effect on subsequent inserts, because MySQL makes no attempt to fill in holes in a sequence. On the other hand, deleting rows 7 and 8 removes values at the top of the sequence, and the effect of this depends on the storage engine:

• With BDB tables, the next sequence number always is the maximum integer currently present in the column plus one. If you delete rows containing values at the top of the sequence, those values will be reused. (Thus, after deleting rows with values 7 and 8, the next inserted row will be assigned the value 7.)

• For MyISAM or InnoDB tables, values are not reused. The next sequence number is the smallest positive integer that has not previously been used. (For a sequence that stands at 8, the next row gets a value of 9 even if you delete rows 7 and 8 first.) If you require strictly monotonic sequences, you should use one of these storage engines.

If a table uses an engine that differs in value-reuse behavior from the behavior you require, use ALTER TABLE to change the table to a more appropriate engine. For example, if you want to change a BDB table to be a MyISAM table (to prevent sequence values from being reused after rows are deleted), do this:

ALTER TABLE tbl_name ENGINE = MyISAM; 

If you don't know what engine a table uses, consult INFORMATION_SCHEMA or use SHOW TABLE STATUS or SHOW CREATE TABLE to find out. For example, the following statement indicates that insect is a MyISAM table:

mysql> SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES     -> WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'insect'; +--------+ | ENGINE | +--------+ | MyISAM | +--------+ 

NOTE

In this chapter, you can assume that if a table's definition has no explicit storage engine, it's a MyISAM table.

If you want to clear out a table and reset the sequence counter, use trUNCATE TABLE:

TRUNCATE TABLE tbl_name;