AUTO_INCREMENT With InnoDB

• If an AUTO_INCREMENT column is specified for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter.
  • It is used in assigning new values for the column.
  • This counter is stored only in main memory, not on disk.

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

• 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 an 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.
  • If an 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.

• There may be gaps in the sequence of values assigned to the AUTO_INCREMENT column if transactions that have generated numbers using the counter are rolled back.

How does InnoDB handle FOREIGN_KEY constraints?

---

---

© 2007 John Michael Pierobon

Notes