MySQL AUTO_INCREMENT med eksempler

Hvad er automatisk stigning?

Auto Increment er en funktion, der fungerer på numeriske datatyper. Den genererer automatisk sekventielle numeriske værdier, hver gang en post indsættes i en tabel for et felt, der er defineret som automatisk stigning.

Attributten fungerer på alle heltalstyper, fra TINYINT til BIGINT. Kolonnen skal også indekseres, hvilket sker automatisk, når den erklæres som primærnøgle.

Hvornår bruger du automatisk stigning?

I lektionen om database normalisering, så vi på, hvordan data kan lagres med minimal redundans ved at lagre data i mange små tabeller, der er relateret til hinanden ved hjælp af primære og fremmednøgler.

En primærnøgle skal være unik, da den entydigt identificerer en række i en database. Men hvordan kan vi sikre, at primærnøglen altid er unik?

En af de mulige løsninger ville være at bruge en formel til at generere den primære nøgle, som kontrollerer, om nøglen findes i tabellen, før data tilføjes. Dette kan virke, men fremgangsmåden er kompleks og ikke idiotsikker. To sessioner, der indsættes på samme tidspunkt, kan stadig læse den samme maksimale værdi og kollidere.

For at undgå en sådan kompleksitet, og for at sikre at primærnøglen altid er unik, kan vi bruge MySQL Funktionen til automatisk forøgelse til at generere primærnøgler. Automatisk forøgelse bruges med datatypen INT. Datatypen INT understøtter både signerede og usignerede værdier. Usignerede datatyper kan kun indeholde positive tal. Som bedste praksis anbefales det at definere den usignerede begrænsning på den automatiske forøgelsesprimærnøgle.

Automatisk stigningssyntaks

Når du har afklaret ræsonnementet, kan du se på det manuskript, der blev brugt til at oprette tabellen med filmkategorier.

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

Bemærk "AUTO_INCREMENT" i feltet category_id. Dette forårsager, at kategori-id'et genereres automatisk, hver gang en ny række indsættes i tabellen. Det angives ikke, når data indsættes i tabellen. MySQL genererer det.

Bemærk: Nøgleordet UNSIGNED fordobler kolonnens positive område, og visningsbredden, når den er skrevet som int(11), er udfaset fra MySQL 8.0.17 og fremefter. Almindelig int er den nuværende form.

Som standard er startværdien for AUTO_INCREMENT 1, og den øges med 1 for hver ny post.

Lad os undersøge det nuværende indhold af kategoritabellen.

SELECT * FROM `categories`;

Udførelse af ovenstående script i MySQL Workbench mod myflixdb giver os følgende resultater.

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

Der findes otte rækker, så det næste genererede id skal være 9. Lad os nu indsætte en ny kategori i kategoritabellen, hvor vi kun angiver navnet.

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

Udførelse af ovenstående script mod myflixdb in MySQL arbejdsbord giver os følgende resultater vist nedenfor.

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

Bemærk, at vi ikke har angivet kategori-id'et. MySQL genererede det automatisk, fordi kategori-id'et er defineret som automatisk forøgelse.

Hvis du ønsker at få det sidste indsæt-id, der blev genereret af MySQL, kan du bruge funktionen LAST_INSERT_ID til at gøre det. Scriptet vist nedenfor får det sidste id, der blev genereret.

SELECT LAST_INSERT_ID();

Udførelse af ovenstående script giver det sidste automatiske forøgelsesnummer genereret af INSERT-forespørgslen. Resultaterne er vist nedenfor.

Tip: LAST_INSERT_ID() er begrænset til din egen forbindelse, så en værdi genereret af en anden brugers insert kan aldrig returneres til dig ved en fejltagelse.

Sådan indstiller eller nulstiller du startværdien for AUTO_INCREMENT

Standardsekvensen starter ved 1, men det er ikke altid, hvad et projekt har brug for. Fakturanumre skal muligvis fortsætte fra et ældre system, og en testtabel skal ofte nulstilles. MySQL eksponerer tælleren direkte, så begge tilfælde håndteres med en enkelt klausul. Følg disse trin for at kontrollere starttallet.

1. Indstil værdien ved oprettelse. Tilføj AUTO_INCREMENT-klausulen til CREATE TABLE-sætningen. Den første række, der indsættes, får derefter dette tal i stedet for 1.
2. Ændr værdien i en eksisterende tabel. Brug ALTER TABEL med den samme klausul. MySQL accepterer kun det nye nummer, hvis det er højere end den største identifikator, der aktuelt er gemt.
3. Nulstil en tabel, du har tømt. TRUNCATE TABLE fjerner alle rækker og returnerer tælleren til 1 i én operation, hvilket DELETE alene ikke gør.
4. Bekræft ændringen. Indsæt en række og læs identifikatoren tilbage med LAST_INSERT_ID(), før du bruger den nye sekvens.

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

Trinstørrelsen kan også ændres med systemvariablen auto_increment_increment, men den gælder for hele serveren i stedet for én tabel. Den bruges primært i replikering, hvor to servere ikke må generere den samme identifikator.

Hvorfor opstår der huller i en AUTO_INCREMENT-sekvens?

Før eller siden viser en tabel identifikatorer som 1, 2, 5, 6. Intet er ødelagt. Tælleren er designet til at garantere unikhed, ikke til at garantere en ubrudt række af tal, og den udsteder aldrig den samme værdi to gange.

Huller opstår af følgende årsager.

• Slettede rækker: Når en række slettes fra en tabel, genbruges dens automatisk forøgede id ikke. MySQL fortsætter med at generere nye numre sekventielt.
• Tilbageførte transaktioner: Nummeret gøres krav på i det øjeblik, indsættelsen kører. Hvis transaktionen rulles tilbage, forsvinder rækken, men nummeret er allerede brugt.
• Mislykkede indsættelser: En sætning, der afvises af en UNIK begrænsning, kan stadig forbruge en identifikator, før den fejler.
• Masseindsatser: InnoDB kan reservere en blok af tal til en indsættelse med flere rækker og kassere dem, den ikke bruger.

Det er en fejltagelse at forsøge at lukke disse huller. Omnummerering af rækker ødelægger alle fremmednøgler, der peger på dem, og selve værdien har ingen forretningsmæssig betydning. Hvis en rapport har brug for en kontinuerlig liste, skal du generere rækkenummeret i forespørgslen i stedet for at omskrive lagrede data.

Ofte Stillede Spørgsmål