Wenn Sie eine AUTO_INCREMENT-Spalte für eine InnoDB-Tabelle definieren, enthält der Tabellen-Handle im InnoDB-Data Dictionary einen speziellen Zähler namens Auto-Increment-Zähler, der zur Zuweisung neuer Werte zu dieser Spalte benutzt wird. Dieser Zähler wird nur im Arbeitsspeicher und nicht auf der Festplatte gespeichert.

InnoDB initialisiert den Auto-Increment-Zähler für eine Tabelle T, die eine AUTO_INCREMENT-Spalte namens ai_col enthält, wie folgt: Nach dem Serverstart führt InnoDB für die erste Einfügung in die Tabelle T das Äquivalent folgender Anweisung aus:

SELECT MAX(ai_col) FROM T FOR UPDATE;

InnoDB inkrementiert den von der Anweisung abgerufenen Wert um eins und weist ihn der Spalte und dem Auto-Increment-Zähler für die Tabelle zu. Ist die Tabelle leer, verwendet InnoDB den Wert 1. Wenn ein Benutzer eine SHOW TABLE STATUS-Anweisung gibt, die Ausgabe für die Tabelle T anzeigt, und der Auto-Increment-Zähler noch nicht initialisiert wurde, nimmt InnoDB die Initialisierung vor, inkrementiert aber nicht den Wert und speichert ihn nicht für spätere Einfügeoperationen. Beachten Sie, dass für diese Initialisierung eine normale Schreiboperation mit exklusiver Sperre auf der Tabelle ausgeführt wird und die Sperre bis zum Ende der Transaktion aufrecht erhalten bleibt.

Genauso geht InnoDB vor, wenn ein Auto-Increment-Zähler für eine neu erzeugte Tabelle initialisiert wird.

Wenn ein Auto-Increment-Zähler initialisiert wurde und ein Benutzer nicht ausdrücklich einen Wert für eine AUTO_INCREMENT-Spalte angibt, inkrementiert InnoDB den Zähler um eins und weist der Spalte den neuen Wert zu. Wenn der Benutzer eine Zeile einfügt, die den Spaltenwert explizit angibt, und dieser Wert größer als der aktuelle Zähler ist, wird der Zähler auf den angegebenen Spaltenwert gesetzt.

Eventuell treten in der Abfolge der Werte einer AUTO_INCREMENT-Spalte Lücken auf, wenn Sie Transaktionen zurückrollen, die Nummern mithilfe des Zählers zugewiesen haben.

Wenn der Benutzer in einem INSERT den Wert NULL oder 0 für die AUTO_INCREMENT-Spalte angibt, behandelt InnoDB die betreffende Zeile, als wäre überhaupt kein Wert vorhanden, und generiert einen neuen Wert für sie.

Das Verhalten des Auto-Increment-Mechanismus ist nicht definiert, wenn ein Benutzer der Spalte einen negativen Wert zuweist, oder wenn der Wert die größte Ganzzahl übersteigt, die in dem angegebenen Integertyp gespeichert werden kann.

InnoDB benutzt für den Zugriff auf den Auto-Increment-Zähler eine spezielle AUTO-INC-Tabellensperre, die bis zum Ende der laufenden SQL-Anweisung (nicht der Transaktion) gehalten wird. Die spezielle Strategie zum Aufheben der Sperre wurde eingefügt, um die Nebenläufigkeit von Einfügeoperationen in Tabellen mit AUTO_INCREMENT-Spalten zu verbessern. Es können nicht zwei Transaktionen zugleich auf derselben Tabelle eine AUTO-INC-Sperre halten.

InnoDB nutzt den speicherresidenten Auto-Increment-Zähler so lange, wie der Server läuft. Wird der Server angehalten und neu gestartet, initialisiert InnoDB den Zähler bei der ersten INSERT-Operation auf der Tabelle neu, wie oben bereits beschrieben.

InnoDB unterstützt die Tabellenoption AUTO_INCREMENT = N in CREATE TABLE und ALTER TABLE-Anweisungen, um den Anfangswert des Zählers einzustellen oder seinen laufenden Wert zu ändern. Die Wirkung dieser Option wird durch einen Neustart des Servers aufgehoben, und zwar aus Gründen, die bereits weiter oben dargestellt wurden.