AUTO INCREMENT key

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 as NULL.
• 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_offset and auto_increment_increment.
• It is OK to have something else as the PRIMARY KEY and simply do INDEX(id). (This is an optimization in some situations.)
• Using the AUTO_INCREMENT as the “PARTITION key” 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 is DELETE plus INSERT) and others. ROLLBACK is 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 COMMIT order.