Database Reference
In-Depth Information
to access the value, you'll get zero. Within a given session, the persistence of
AUTO_IN
CREMENT
values can be much longer on the server side of the session:
• After you execute a statement that generates an
AUTO_INCREMENT
value, the value
remains available through
LAST_INSERT_ID()
even if you execute other statements,
as long as none of those statements generate an
AUTO_INCREMENT
value.
• The sequence value available using client-side API methods typically is set for
every
statement, not only those that generate
AUTO_INCREMENT
values. If you execute an
INSERT
statement that generates a new value and then execute some other statement
before accessing the client-side sequence value, it probably will have been set to
zero. The precise behavior varies among APIs, but to be safe, you can do this: when
a statement generates a sequence value that you won't use immediately, save the
value in a variable that you can refer to later. Otherwise, you may find the sequence
value wiped out by the time you try to access it. (For more on this topic, see
Recipe 13.10
.)
13.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. 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
Recipe 13.4
):
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 |
| 9 | cricket | 2014-09-11 | basement |
