MySQL Indexes and Keys AUTO_INCREMENT key

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

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 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.


Got any MySQL Question?