MySQL AUTO_INCREMENT med exempel
Marcus Allen
⚡ Smart sammanfattning
MySQL AUTO_INCREMENT genererar automatiskt sekventiella tal för en numerisk kolumn varje gång en rad infogas. Attributet eliminerar behovet av att beräkna unika identifierare manuellt, vilket gör det till standardsättet att fylla i en primärnyckel.
Vad är automatisk ökning?
Automatisk ökning är en funktion som fungerar på numeriska datatyper. Den genererar automatiskt sekventiella numeriska värden varje gång en post infogas i en tabell för ett fält som definieras som automatisk inkrement.
Attributet fungerar på alla heltalstyper, från TINYINT till BIGINT. Kolumnen måste också indexeras, vilket sker automatiskt när den deklareras som primärnyckel.
När använder du automatisk ökning?
I lektionen om databasnormalisering, tittade vi på hur data kan lagras med minimal redundans, genom att lagra data i många små tabeller, relaterade till varandra med hjälp av primär- och främmande nycklar.
En primärnyckel måste vara unik, eftersom den identifierar en rad i en databas. Men hur kan vi säkerställa att primärnyckeln alltid är unik?
En möjlig lösning vore att använda en formel för att generera primärnyckeln, som kontrollerar nyckelns existens i tabellen innan data läggs till. Detta kan fungera, men metoden är komplex och inte idiotsäker. Två sessioner som infogas samtidigt kan fortfarande läsa samma maximala värde och kollidera.
För att undvika sådan komplexitet, och för att säkerställa att primärnyckeln alltid är unik, kan vi använda MySQL Funktionen för automatisk ökning för att generera primärnycklar. Automatisk ökning används med datatypen INT. Datatypen INT stöder både teckenförsedda och oteckenförsedda värden. Oteckenförsedda datatyper kan endast innehålla positiva tal. Som bästa praxis rekommenderas att definiera den oteckenförsedda begränsningen på primärnyckeln för automatisk ökning.
Syntax för automatisk ökning
När du har avklarat resonemanget, titta på manuset som användes för att skapa tabellen över 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`) );
Lägg märke till "AUTO_INCREMENT" i fältet category_id. Detta gör att kategori-ID:t genereras automatiskt varje gång en ny rad infogas i tabellen. Det anges inte när data infogas i tabellen. MySQL genererar det.
Obs: Nyckelordet UNSIGNED fördubblar kolumnens positiva intervall, och visningsbredden när den skrivits som int(11) är föråldrad från och med MySQL 8.0.17 och framåt. Vanligt int är den nuvarande formen.
Som standard är startvärdet för AUTO_INCREMENT 1, och det ökar med 1 för varje ny post.
Låt oss undersöka det aktuella innehållet i kategoritabellen.
SELECT * FROM `categories`;
Exekvera skriptet ovan i MySQL Workbench mot myflixdb ger oss följande resultat.
| 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 |
Det finns åtta rader, så nästa genererade id bör vara 9. Låt oss nu infoga en ny kategori i kategoritabellen och endast ange namnet.
INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');
Exekvera skriptet ovan mot myflixdb in MySQL arbetsbänk ger oss följande resultat som visas nedan.
| 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 |
Observera att vi inte angav kategori-ID:t. MySQL genererade det automatiskt, eftersom kategori-ID:t är definierat som automatisk ökning.
Om du vill få det senaste infognings-id:t som genererades av MySQL, du kan använda funktionen LAST_INSERT_ID för att göra det. Skriptet som visas nedan får det senaste id som genererades.
SELECT LAST_INSERT_ID();
Genom att köra ovanstående skript får du det senaste automatiska ökningsnumret som genererades av INSERT-frågan. Resultaten visas nedan.
Dricks: LAST_INSERT_ID() är begränsat till din egen anslutning, så ett värde som genereras av en annan användares insert kan aldrig returneras till dig av misstag.
Så här ställer du in eller återställer startvärdet för AUTO_INCREMENT
Standardsekvensen börjar på 1, men det är inte alltid vad ett projekt behöver. Fakturanummer kan behöva fortsätta från ett äldre system, och en testtabell måste ofta återställas. MySQL exponerar räknaren direkt, så båda fallen hanteras med en enda klausul. Följ dessa steg för att kontrollera startnumret.
- Ställ in värdet vid skapandet. Lägg till AUTO_INCREMENT-klausulen i CREATE TABLE-satsen. Den första raden som infogas får då det numret istället för 1.
- Ändra värdet i en befintlig tabell. Använda ALTER TABLE med samma klausul. MySQL accepterar endast det nya numret om det är högre än den största identifieraren som för närvarande är lagrad.
- Återställ en tabell du har tömt. TRUNCATE TABLE tar bort alla rader och returnerar räknaren till 1 i en operation, vilket DELETE ensamt inte gör.
- Bekräfta ändringen. Infoga en rad och läs tillbaka identifieraren med LAST_INSERT_ID() innan du förlitar dig på den nya sekvensen.
-- 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`;
Stegstorleken kan också ändras med systemvariabeln auto_increment_increment, men den gäller för hela servern snarare än för en tabell. Den används främst vid replikering, där två servrar inte får generera samma identifierare.
Varför visas mellanrum i en AUTO_INCREMENT-sekvens?
Förr eller senare visar en tabell identifierare som 1, 2, 5, 6. Ingenting är trasigt. Räknaren är utformad för att garantera unikhet, inte för att garantera en obruten serie av siffror, och den utfärdar aldrig samma värde två gånger.
Luckor uppstår av följande skäl.
- Borttagna rader: När en rad tas bort från en tabell återanvänds inte dess automatiskt ökade ID. MySQL fortsätter att generera nya nummer sekventiellt.
- Återställda transaktioner: Numret görs anspråk i samma ögonblick som infogningen körs. Om transaktionen återställs försvinner raden men numret är redan förbrukat.
- Misslyckade insatser: Ett uttalande som avvisas av en UNIK begränsning kan fortfarande förbruka en identifierare innan det misslyckas.
- Stora insatser: InnoDB kan reservera ett block med siffror för en flerradig infogning och ignorera de som inte används.
Att försöka täppa till dessa luckor är ett misstag. Att omnumrera rader bryter alla främmande nycklar som pekar på dem, och själva värdet har ingen affärsmässig betydelse. Om en rapport behöver en kontinuerlig lista, generera radnumret i frågan istället för att skriva om lagrade data.
