MySQL Indexes and Keys AUTO_INCREMENT key
Example
CREATE TABLE (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY(id),
... );
Main notes:
- Starts with 1 and increments by 1 automatically when you fail to specify it on
INSERT, or specify it asNULL. - The ids are always distinct from each other, but...
- Do not make any assumptions (no gaps, consecutively generated, not reused, etc) about the values of the id other than being unique at any given instant.
Subtle notes:
- On restart of server, the 'next' value is 'computed' as
MAX(id)+1. - If the last operation before shutdown or crash was to delete the highest id, that id may be reused (this is engine-dependent). So, do not trust auto_increments to be permanently unique; they are only unique at any moment.
- For multi-master or clustered solutions, see
auto_increment_offsetandauto_increment_increment. - It is OK to have something else as the
PRIMARY KEYand simply doINDEX(id). (This is an optimization in some situations.) - Using the
AUTO_INCREMENTas the "PARTITIONkey" is rarely beneficial; do something different. - Various operations may "burn" values. This happens when they pre-allocate value(s), then don't use them:
INSERT IGNORE(with dup key),REPLACE(which isDELETEplusINSERT) and others.ROLLBACKis another cause for gaps in ids. - In Replication, you cannot trust ids to arrive at the slave(s) in ascending order. Although ids are assigned in consecutive order, InnoDB statements are sent to slaves in
COMMITorder.
