The Effect of Record Deletions on Sequence Generation

11.5.1 Problem

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

11.5.2 Solution

It depends on which records you delete and on the table type.

11.5.3 Discussion

We have thus far considered how sequence values in an AUTO_INCREMENT column are generated for circumstances where records are only added to a table. But it's unrealistic to assume that records 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 | 2001-09-10 | kitchen |
| 2 | millipede | 2001-09-10 | driveway |
| 3 | grasshopper | 2001-09-10 | front yard |
| 4 | stink bug | 2001-09-10 | front yard |
| 5 | cabbage butterfly | 2001-09-10 | garden |
| 6 | ant | 2001-09-10 | back yard |
| 7 | ant | 2001-09-10 | back yard |
| 8 | millbug | 2001-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 bear on 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 record 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 records 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 valuesthey allow you to specify any record unambiguously. The ant records 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 records have been removed, the resulting table contents become:

mysql> SELECT * FROM insect ORDER BY id;
+----+-------------------+------------+------------+
| id | name | date | origin |
+----+-------------------+------------+------------+
| 1 | housefly | 2001-09-10 | kitchen |
| 3 | grasshopper | 2001-09-10 | front yard |
| 4 | stink bug | 2001-09-10 | front yard |
| 5 | cabbage butterfly | 2001-09-10 | garden |
| 6 | ant | 2001-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 records 7 and 8 removes values at the top of the sequence, and the effect of this depends on the table type:

• With ISAM and BDB tables, the next sequence number always is the smallest positive integer not currently present in the column. If you delete rows containing values at the top of the sequence, those values will be reused. (Thus, after deleting records with values 7 and 8, the next inserted record 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 record gets a value of 9 even if you delete records 7 and 8 first.) If you require strictly monotonic sequences, you should use one of these table types.

ISAM tables are the only table type available until MySQL 3.23, so prior to that version, reuse of values deleted from the top of a sequence is the only behavior you can get. MyISAM tables are available as of MySQL 3.23 (at which point, MyISAM also became the default table type). BDB and InnoDB tables are available as of MySQL 3.23.17 and 3.23.29, respectively.

If you're using a table with a type that differs in value-reuse behavior from the behavior you require, use ALTER TABLE to change the table to a more appropriate type. For example, if you want to change an ISAM table to be a MyISAM table (to prevent sequence values from being reused after records are deleted), do this:

ALTER TABLE tbl_name TYPE = MYISAM;

If you don't know what type a table is, use SHOW TABLE STATUS to find out:

mysql> SHOW TABLE STATUS LIKE 'insect'G;
*************************** 1. row ***************************
 Name: insect
 Type: MyISAM
 Row_format: Dynamic
 Rows: 7
 Avg_row_length: 30
 Data_length: 216
Max_data_length: 4294967295
 Index_length: 2048
 Data_free: 0
 Auto_increment: 8
 Create_time: 2002-01-25 16:55:32
 Update_time: 2002-01-25 16:55:32
 Check_time: NULL
 Create_options:
 Comment:

The output shown here indicates that insect is a MyISAM table. (You can also use SHOW CREATE TABLE.)

In this chapter, you can assume that if a table is created with no explicit table type, it's a MyISAM table.

A special case of record deletion occurs when you clear out a table entirely using a DELETE with no WHERE clause:

DELETE FROM tbl_name;

In this case, the sequence counter may be reset to 1, even for table types for which values normally are not reused (MyISAM and InnoDB). For those types, if you wish to delete all the records while maintaining the current sequence value, tell MySQL to perform a record-at-a-time delete by including a WHERE clause that specifies some trivially true condition:

DELETE FROM tbl_name WHERE 1 > 0;