W bazie MySQL 8 (silnik InnoDB) tabela pracownicy ma klucz główny id typu INTEGER z atrybutami NOT NULL i AUTO_INCREMENT oraz pola imie i nazwisko. Tabela zawierała rekordy o id 1, 2 i 3, po czym usunięto rekord o id 3. Następnie wykonano kwerendę
INSERT INTO pracownicy (imie, nazwisko) VALUES ('Anna', 'Nowak');Jaką wartość id otrzyma nowy rekord?
NULL.Licznik AUTO_INCREMENT pamięta następną wartość do nadania i tylko rośnie. Po wstawieniu rekordów 1, 2 i 3 ustawił się na 4. Usunięcie rekordu o id 3 nie zmienia licznika, więc nowa Anna Nowak dostanie id 4. W tabeli powstaje luka w numeracji i jest to zachowanie zamierzone.
Dzięki temu identyfikator raz użyty nie trafia do innej osoby, co chroni przed pomyleniem danych, np. gdy w innym systemie została jeszcze stara wartość. Od MySQL 8 licznik InnoDB jest zapisywany trwale i nie cofa się nawet po restarcie serwera. Licznik można przestawić ręcznie: ALTER TABLE pracownicy AUTO_INCREMENT = 3;, ale nie ustawi się go poniżej największego istniejącego id plus jeden, a TRUNCATE TABLE zeruje go razem z danymi.
Najczęstsze założenie jest takie, że baza „uzupełni dziurę” i nada 3. Tak działałby człowiek numerujący kartki, ale nie licznik AUTO_INCREMENT. On nie szuka wolnych wartości, tylko nadaje kolejną po ostatnio nadanej. Luki po usuniętych rekordach są normalne i nie są błędem.
Wartość NULL w kluczu głównym jest niemożliwa, bo klucz główny wymaga wartości, a do tego kolumna ma NOT NULL. Pominięcie pola id w INSERT to wręcz sygnał dla bazy, żeby użyła licznika.
Numeracja od 1 groziłaby duplikatem klucza, bo rekord o id 1 nadal istnieje. Licznik wraca do początku tylko po TRUNCATE TABLE albo po ponownym utworzeniu tabeli.
Aktualny stan licznika pokazuje kolumna Auto_increment w wyniku polecenia SHOW TABLE STATUS LIKE 'pracownicy';. Po usunięciu rekordu nadal będzie tam 4, co potwierdza, że numeracja idzie dalej.