MySQL AUTO_INCREMENT z przykładami
Marcusa Allena
⚡ Inteligentne podsumowanie
MySQL Funkcja AUTO_INCREMENT automatycznie generuje kolejne numery dla kolumny numerycznej za każdym razem, gdy wstawiany jest wiersz. Atrybut ten eliminuje konieczność ręcznego obliczania unikatowych identyfikatorów, co czyni go standardowym sposobem wypełniania klucza podstawowego.
Co to jest automatyczny przyrost?
Auto Increment to funkcja, która działa na typach danych numerycznych. Automatycznie generuje sekwencyjne wartości numeryczne za każdym razem, gdy rekord jest wstawiany do tabeli dla pola zdefiniowanego jako auto increment.
Atrybut działa dla dowolnego typu całkowitego, od TINYINT do BIGINT. Kolumna musi być również indeksowana, co odbywa się automatycznie po jej zadeklarowaniu jako klucza podstawowego.
Kiedy używać automatycznego zwiększania?
Na lekcji o normalizacja bazy danychprzyjrzeliśmy się sposobowi przechowywania danych z minimalną redundancją, poprzez zapisywanie danych w wielu małych tabelach, powiązanych ze sobą za pomocą kluczy podstawowych i obcych.
Klucz podstawowy musi być unikatowy, ponieważ jednoznacznie identyfikuje wiersz w bazie danych. Jak jednak możemy zagwarantować, że klucz podstawowy zawsze będzie unikatowy?
Jednym z możliwych rozwiązań byłoby użycie formuły do generowania klucza podstawowego, która sprawdza istnienie klucza w tabeli przed dodaniem danych. To może zadziałać, ale podejście jest złożone i niepewne. Dwie sesje wstawiające dane w tym samym momencie mogą nadal odczytać tę samą wartość maksymalną i kolidować ze sobą.
Aby uniknąć takiej złożoności i zapewnić, że klucz podstawowy będzie zawsze unikalny, możemy użyć MySQL Funkcja autoinkrementacji do generowania kluczy podstawowych. Autoinkrementacja jest używana z typem danych INT. Typ danych INT obsługuje zarówno wartości ze znakiem, jak i bez znaku. Typy danych bez znaku mogą zawierać tylko liczby dodatnie. Zaleca się zdefiniowanie ograniczenia unsigned dla klucza podstawowego autoinkrementacji.
Składnia automatycznego zwiększania
Mając już ustalone uzasadnienie, przyjrzyjmy się skryptowi użytemu do utworzenia tabeli kategorii filmów.
CREATE TABLE `categories` ( `category_id` int UNSIGNED NOT NULL AUTO_INCREMENT, `category_name` varchar(150) DEFAULT NULL, `remarks` varchar(500) DEFAULT NULL, PRIMARY KEY (`category_id`) );
Zwróć uwagę na „AUTO_INCREMENT” w polu „category_id”. Powoduje to automatyczne generowanie identyfikatora kategorii za każdym razem, gdy do tabeli wstawiany jest nowy wiersz. Nie jest on podawany podczas wstawiania danych do tabeli. MySQL generuje to.
Uwaga: Słowo kluczowe UNSIGNED podwaja dodatni zakres kolumny, a szerokość wyświetlania zapisana kiedyś jako int(11) jest przestarzała MySQL Od wersji 8.0.17. Zwykły int jest obecną formą.
Domyślnie wartość początkowa parametru AUTO_INCREMENT wynosi 1 i będzie zwiększana o 1 dla każdego nowego rekordu.
Przyjrzyjmy się aktualnej zawartości tabeli kategorii.
SELECT * FROM `categories`;
Wykonanie powyższego skryptu w MySQL Workbench i myflixdb dają nam następujące wyniki.
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humour |
| 2 | Romantic | Love stories |
| 3 | Epic | Story acient movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
Istnieje osiem wierszy, więc następny wygenerowany identyfikator powinien wynosić 9. Wstawmy teraz nową kategorię do tabeli kategorii, podając tylko nazwę.
INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');
Wykonanie powyższego skryptu na myflixdb w MySQL Workbench daje nam następujące wyniki pokazane poniżej.
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humour |
| 2 | Romantic | Love stories |
| 3 | Epic | Story acient movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
| 9 | Cartoons | NULL |
Zwróć uwagę, że nie podaliśmy identyfikatora kategorii. MySQL wygenerowało ją automatycznie, ponieważ identyfikator kategorii jest zdefiniowany jako auto-inkrementacja.
Jeśli chcesz uzyskać ostatni identyfikator wkładki, który został wygenerowany przez MySQL, możesz w tym celu użyć funkcji LAST_INSERT_ID. Skrypt pokazany poniżej pobiera ostatni wygenerowany identyfikator.
SELECT LAST_INSERT_ID();
Wykonanie powyższego skryptu zwraca ostatnią wartość autoinkrementacji wygenerowaną przez zapytanie INSERT. Wyniki przedstawiono poniżej.
Wskazówka: Funkcja LAST_INSERT_ID() ma zakres ograniczony do Twojego własnego połączenia, więc wartość wygenerowana przez wstawienie kodu przez innego użytkownika nigdy nie zostanie zwrócona przez pomyłkę.
Jak ustawić lub zresetować wartość początkową AUTO_INCREMENT
Domyślna sekwencja zaczyna się od 1, ale nie zawsze jest to wymagane w projekcie. Numery faktur mogą wymagać kontynuacji ze starszego systemu, a tabela testowa często wymaga zresetowania. MySQL Ujawnia licznik bezpośrednio, więc oba przypadki są obsługiwane za pomocą jednej klauzuli. Wykonaj poniższe kroki, aby kontrolować liczbę początkową.
- Ustaw wartość w momencie utworzenia. Dodaj klauzulę AUTO_INCREMENT do instrukcji CREATE TABLE. Pierwszy wstawiony wiersz otrzyma wtedy tę liczbę zamiast 1.
- Zmień wartość w istniejącej tabeli. Zastosowanie ALTER TABLE z tą samą klauzulą. MySQL akceptuje nowy numer tylko jeśli jest on wyższy od największego obecnie zapisanego identyfikatora.
- Zresetuj opróżnioną tabelę. TRUNCATE TABLE usuwa wszystkie wiersze i zwraca licznik do 1 w jednej operacji, czego nie można zrobić za pomocą samej operacji DELETE.
- Potwierdź zmianę. Wstaw wiersz i odczytaj identyfikator za pomocą LAST_INSERT_ID() przed użyciem nowej sekwencji.
-- Start a brand-new table at 1000 CREATE TABLE `invoices` ( `invoice_id` int UNSIGNED NOT NULL AUTO_INCREMENT, `amount` decimal(10,2), PRIMARY KEY (`invoice_id`) ) AUTO_INCREMENT = 1000; -- Move the counter on an existing table ALTER TABLE `categories` AUTO_INCREMENT = 100; -- Empty the table and reset the counter to 1 TRUNCATE TABLE `categories`;
Rozmiar kroku można również zmienić za pomocą zmiennej systemowej auto_increment_increment, ale dotyczy ona całego serwera, a nie jednej tabeli. Jest ona używana głównie w replikacji, gdzie dwa serwery nie mogą generować tego samego identyfikatora.
Dlaczego w sekwencji AUTO_INCREMENT pojawiają się przerwy?
Wcześniej czy później tabela pokazuje identyfikatory takie jak 1, 2, 5, 6. Nic nie jest zepsute. Licznik został zaprojektowany tak, aby zagwarantować unikalność, a nie nieprzerwany ciąg liczb, i nigdy nie zwraca tej samej wartości dwa razy.
Luki pojawiają się z następujących powodów.
- Usunięte wiersze: gdy wiersz zostaje usunięty z tabeli, jego automatycznie zwiększony identyfikator nie jest ponownie używany. MySQL kontynuuje generowanie nowych numerów sekwencyjnie.
- Wycofane transakcje: Numer jest przejmowany w momencie uruchomienia operacji wstawiania. Jeśli transakcja zostanie wycofana, wiersz zniknie, ale numer zostanie już wykorzystany.
- Nieudane wkładki: polecenie odrzucone przez ograniczenie UNIQUE nadal może wykorzystać identyfikator, zanim zakończy się niepowodzeniem.
- Wkładki zbiorcze: InnoDB może zarezerwować blok liczb do wstawienia wielu wierszy i odrzucić te, których nie używa.
Próba zamknięcia tych luk to błąd. Ponowna numeracja wierszy powoduje uszkodzenie każdego klucza obcego, który na nie wskazuje, a sama wartość nie ma znaczenia biznesowego. Jeśli raport wymaga listy ciągłej, należy wygenerować numer wiersza w zapytaniu zamiast przepisywać zapisane dane.
