auto-increment
A property of a table column (specified by the AUTO-INCREMENT keyword) that automatically adds an ascending sequence of values in the column. InnoDB supports auto-increment only for primary key columns.
It saves work for the developer, not to have to produce new unique values when inserting new rows. It provides useful information for the query optimizer, because the column is known to be not null and with unique values. The values from such a column can be used as lookup keys in various contexts, and because they are auto-generated there is no reason to ever change them; for this reason, primary key columns are often specified as auto-incrementing.
Auto-increment columns can be problematic with statement-based replication, because replaying the statements on a slave might not produce the same set of column values as on the master, due to timing issues. When you have an auto-incrementing primary key, you can use statement-based replication only with the setting innodb-autoinc-lock_mode=1. If you have innodb_autoinc_lock_mode=2, which allows higher concurrency for insert operations, use row-based replication rather than statement-based replication. The setting innodb_autoinc_lock_mode=0 is the previous (traditional) default setting and should not be used except for compatibility purposes.
See also auto-increment locking.
See also innodb-autoinc-lock-mode.
See also primary key.
See also row-based replication.
See also statement-based replication.