Database Reference
In-Depth Information
That's about to change because after Junior remembers to bring home the written in‐
structions for the project, you read through them and discover two things that affect
the table contents:
• Specimens should include only insects, not insect-like creatures such as millipedes
and termites.
• 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 permitted.
These instructions dictate that a few rows be removed from table—specifically those
with
id
values 2 (millipede), 8 (termite), 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 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 table, it would be more difficult to delete just one of them
(though not impossible; see
Recipe 16.4
).
After removing the unsuitable rows, the table has these remaining:
mysql>
SELECT * FROM insect ORDER BY id;
+----+-------------------+------------+------------+
| id | name | date | origin |
+----+-------------------+------------+------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 3 | grasshopper | 2014-09-10 | front yard |
| 4 | stink bug | 2014-09-10 | front yard |
| 5 | cabbage butterfly | 2014-09-10 | garden |
| 6 | ant | 2014-09-10 | back yard |
+----+-------------------+------------+------------+
The
id
column sequence 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 creates 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. For
InnoDB or MyISAM 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 can use one of these storage engines. For other storage
engines, values removed at the top of the sequence may or may not be reused. Check
the properties of the engine before using it.
