MySQL AUTO_INCREMENT s primjerima

Što je automatsko povećanje?

Automatsko povećanje je funkcija koja radi s numeričkim tipovima podataka. Automatski generira sekvencijalne numeričke vrijednosti svaki put kada se zapis umetne u tablicu za polje definirano kao automatsko povećanje.

Atribut radi na bilo kojem cjelobrojnom tipu, od TINYINT do BIGINT. Stupac također mora biti indeksiran, što se događa automatski kada se deklarira kao primarni ključ.

Kada koristiti automatsko povećanje?

U lekciji o normalizacija baze podataka, pogledali smo kako se podaci mogu pohraniti s minimalnom redundancijom, pohranjivanjem podataka u mnogo malih tablica, međusobno povezanih primarnim i stranim ključevima.

Primarni ključ mora biti jedinstven jer jedinstveno identificira redak u bazi podataka. Ali kako možemo osigurati da je primarni ključ uvijek jedinstven?

Jedno od mogućih rješenja bilo bi korištenje formule za generiranje primarnog ključa, koja provjerava postojanje ključa u tablici prije dodavanja podataka. To bi moglo funkcionirati, ali pristup je složen i nije siguran. Dvije sesije koje se unose u istom trenutku i dalje mogu pročitati istu maksimalnu vrijednost i sudariti se.

Kako bismo izbjegli takvu složenost i osigurali da je primarni ključ uvijek jedinstven, možemo koristiti MySQL Značajka automatskog povećanja za generiranje primarnih ključeva. Automatsko povećanje koristi se s tipom podataka INT. Tip podataka INT podržava i potpisane i nepotpisane vrijednosti. Nepotpisani tipovi podataka mogu sadržavati samo pozitivne brojeve. Kao najbolja praksa, preporučuje se definirati nepotpisano ograničenje na primarnom ključu automatskog povećanja.

Sintaksa automatskog povećanja

Nakon što ste riješili problem, pogledajte skriptu koja se koristi za izradu tablice kategorija filmova.

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`)
);

Obratite pozornost na "AUTO_INCREMENT" u polju category_id. To uzrokuje da se ID kategorije automatski generira svaki put kada se novi redak umetne u tablicu. Ne navodi se prilikom umetanja podataka u tablicu. MySQL generira ga.

Bilješka: Ključna riječ UNSIGNED udvostručuje pozitivni raspon stupca, a širina prikaza, jednom zapisana kao int(11), je zastarjela. MySQL 8.0.17 nadalje. Običan int je trenutni oblik.

Prema zadanim postavkama, početna vrijednost za AUTO_INCREMENT je 1 i povećavat će se za 1 za svaki novi zapis.

Ispitajmo trenutni sadržaj tablice kategorija.

SELECT * FROM `categories`;

Izvršavanje gornje skripte u MySQL Workbench na myflixdb daje nam sljedeće rezultate.

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

Postoji osam redaka, tako da bi sljedeći generirani ID trebao biti 9. Umetnimo sada novu kategoriju u tablicu kategorija, navodeći samo naziv.

INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');

Izvršavanje gornje skripte protiv myflixdb in MySQL radna tezga daje nam sljedeće rezultate prikazane u nastavku.

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

Imajte na umu da nismo naveli ID kategorije. MySQL generirao ga je automatski, jer je ID kategorije definiran kao automatsko povećanje.

Ako želite dobiti zadnji ID umetanja koji je generirao MySQL, za to možete koristiti funkciju LAST_INSERT_ID. Skripta prikazana u nastavku dobiva zadnji ID koji je generiran.

SELECT LAST_INSERT_ID();

Izvršavanje gornjeg skripta daje zadnji broj automatskog povećanja generiran upitom INSERT. Rezultati su prikazani u nastavku.

Savjet: LAST_INSERT_ID() je ograničen na vašu vlastitu vezu, tako da vrijednost generirana umetanjem drugog korisnika nikada ne može biti vraćena vama greškom.

Kako postaviti ili resetirati početnu vrijednost AUTO_INCREMENT

Zadani niz počinje od 1, ali to nije uvijek ono što je projektu potrebno. Brojevi računa možda će morati nastaviti iz naslijeđenog sustava, a testna tablica se često mora resetirati. MySQL izravno otkriva brojač, tako da se oba slučaja obrađuju jednom klauzulom. Slijedite ove korake za kontrolu početnog broja.

1. Postavite vrijednost prilikom kreiranja. Dodajte klauzulu AUTO_INCREMENT naredbi CREATE TABLE. Prvi umetnuti redak tada dobiva taj broj umjesto 1.
2. Promijenite vrijednost u postojećoj tablici. Koristiti ALTER TABLE s istom klauzulom. MySQL prihvaća novi broj samo ako je veći od najvećeg trenutno pohranjenog identifikatora.
3. Resetirajte tablicu koju ste ispraznili. TRUNCATE TABLE uklanja sve retke i vraća brojač na 1 u jednoj operaciji, što samo DELETE ne može učiniti.
4. Potvrdite promjenu. Umetnite redak i pročitajte identifikator natrag pomoću LAST_INSERT_ID() prije nego što se oslonite na novi niz.

-- 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`;

Veličina koraka može se promijeniti i sistemskom varijablom auto_increment_increment, ali se primjenjuje na cijeli poslužitelj, a ne na jednu tablicu. Koristi se uglavnom u replikaciji, gdje dva poslužitelja ne smiju generirati isti identifikator.

Zašto se praznine pojavljuju u AUTO_INCREMENT nizu?

Prije ili kasnije tablica prikazuje identifikatore poput 1, 2, 5, 6. Ništa nije pokvareno. Brojač je dizajniran da jamči jedinstvenost, a ne da jamči neprekinuti niz brojeva, i nikada ne izdaje istu vrijednost dvaput.

Praznine se pojavljuju iz sljedećih razloga.

• Izbrisani retci: Kada se red izbriše iz tablice, njegov automatski povećani ID se ne koristi ponovno. MySQL nastavlja sekvencijalno generiranje novih brojeva.
• Vraćene transakcije: Broj se preuzima u trenutku kada se izvrši unos. Ako se transakcija poništi, redak nestaje, ali broj je već potrošen.
• Neuspjeli umetci: Naredba koju je odbilo UNIQUE ograničenje i dalje može potrošiti identifikator prije nego što ne uspije.
• Umetci u rasutom stanju: InnoDB može rezervirati blok brojeva za umetanje više redova i odbaciti one koje ne koristi.

Pokušaj zatvaranja tih praznina je pogreška. Prenumeriranje redaka prekida svaki strani ključ koji pokazuje na njih, a sama vrijednost nema poslovno značenje. Ako izvješće treba kontinuirani popis, generirajte broj retka u upitu umjesto prepisivanja pohranjenih podataka.

Pitanja i odgovori