If you specify an AUTO_INCREMENT
column for
an InnoDB
table, the table handle in the
InnoDB
data dictionary contains a special
counter called the auto-increment counter that is used in
assigning new values for the column. This counter is stored only
in main memory, not on disk.
InnoDB
uses the following algorithm to
initialize the auto-increment counter for a table
t
that contains an
AUTO_INCREMENT
column named
ai_col
: After a server startup, for the first
insert into a table t
,
InnoDB
executes the equivalent of this
statement:
SELECT MAX(ai_col) FROM t FOR UPDATE;
InnoDB
increments by one the value retrieved
by the statement and assigns it to the column and to the
auto-increment counter for the table. If the table is empty,
InnoDB
uses the value 1
.
If a user invokes a SHOW TABLE
STATUS
statement that displays output for the table
t
and the auto-increment counter has not been
initialized, InnoDB
initializes but does not
increment the value and stores it for use by later inserts. This
initialization uses a normal exclusive-locking read on the table
and the lock lasts to the end of the transaction.
InnoDB
follows the same procedure for
initializing the auto-increment counter for a freshly created
table.
After the auto-increment counter has been initialized, if a user
does not explicitly specify a value for an
AUTO_INCREMENT
column,
InnoDB
increments the counter by one and
assigns the new value to the column. If the user inserts a row
that explicitly specifies the column value, and the value is
bigger than the current counter value, the counter is set to the
specified column value.
When accessing the auto-increment counter,
InnoDB
uses a special table-level
AUTO-INC
lock that it keeps to the end of the
current SQL statement, not to the end of the transaction. The
special lock release strategy was introduced to improve
concurrency for inserts into a table containing an
AUTO_INCREMENT
column. Nevertheless, two
transactions cannot have the AUTO-INC
lock on
the same table simultaneously, which can have a performance
impact if the AUTO-INC
lock is held for a
long time. That might be the case for a statement such as
INSERT INTO t1 ... SELECT ... FROM t2
that
inserts all rows from one table into another.
InnoDB
uses the in-memory auto-increment
counter as long as the server runs. When the server is stopped
and restarted, InnoDB
reinitializes the
counter for each table for the first
INSERT
to the table, as described
earlier.
You may see gaps in the sequence of values assigned to the
AUTO_INCREMENT
column if you roll back
transactions that have generated numbers using the counter.
If a user specifies NULL
or
0
for the AUTO_INCREMENT
column in an INSERT
,
InnoDB
treats the row as if the value had not
been specified and generates a new value for it.
The behavior of the auto-increment mechanism is not defined if a user assigns a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type.
An AUTO_INCREMENT
column must appear as the
first column in an index on an InnoDB
table.
Beginning with MySQL 4.1.12, InnoDB
supports
the AUTO_INCREMENT =
table option in
N
ALTER TABLE
statements, to set
the initial counter value or alter the current counter value.
The same is true as of MySQL 4.1.14 for
CREATE TABLE
. The effect of this
option is canceled by a server restart, for reasons discussed
earlier in this section.
User Comments
"An AUTO_INCREMENT column must be the first column listed if it is part of a multiple-column index in an InnoDB table."
That doesn't appear to be true. We have a number of tables where the AUTO_INCREMENT column is *not* the first column listed in a multiple-column index. However, that column IS the first listed column in a separate unique index.
Perhaps the wording should be something like:
"An AUTO_INCREMENT column must be part of at least 1 index, either as the only column in the index, or be the first column listed if it is part of a multiple-column index in an InnoDB table."
I would presume you are correct.
Seeing as how InnoDB AUTO_INCREMENT values are never grouped by previous (prefixed) columns and never repeated, there would be no reason to require it to be the primary column.
However, I think it would be a best practice to keep the AUTO_INCREMENT column first to be both inline with the documentation and prevent any errors should you (or someone) convert to MyISAM.
Add your own comment.