MySQL AUTO_INCREMENT met voorbeelden
Marcus Allen
⚡ Slimme samenvatting
MySQL AUTO_INCREMENT genereert automatisch opeenvolgende nummers voor een numerieke kolom telkens wanneer een rij wordt ingevoegd. Dit attribuut maakt het handmatig berekenen van unieke identificatoren overbodig, waardoor het de standaardmethode is voor het vullen van een primaire sleutel.
Wat is automatische verhoging?
Auto Increment is een functie die werkt op numerieke gegevenstypen. Het genereert automatisch opeenvolgende numerieke waarden elke keer dat een record wordt ingevoegd in een tabel voor een veld dat is gedefinieerd als auto increment.
Het attribuut werkt op elk integer-type, van TINYINT tot en met BIGINT. De kolom moet ook geïndexeerd zijn, wat automatisch gebeurt wanneer deze als primaire sleutel wordt gedeclareerd.
Wanneer automatische verhoging gebruiken?
In de les over databasenormalisatieWe hebben onderzocht hoe gegevens met minimale redundantie kunnen worden opgeslagen, door ze op te slaan in veel kleine tabellen die met elkaar verbonden zijn door middel van primaire en externe sleutels.
Een primaire sleutel moet uniek zijn, omdat deze een rij in een database uniek identificeert. Maar hoe kunnen we ervoor zorgen dat de primaire sleutel altijd uniek is?
Een mogelijke oplossing zou zijn om een formule te gebruiken om de primaire sleutel te genereren, die controleert of de sleutel in de tabel aanwezig is voordat er gegevens worden toegevoegd. Dit zou kunnen werken, maar de aanpak is complex en niet waterdicht. Twee sessies die tegelijkertijd gegevens invoegen, kunnen nog steeds dezelfde maximale waarde lezen en conflicten veroorzaken.
Om dergelijke complexiteit te vermijden en ervoor te zorgen dat de primaire sleutel altijd uniek is, kunnen we gebruikmaken van de MySQL De auto-increment-functie wordt gebruikt om primaire sleutels te genereren. Auto-increment wordt gebruikt met het gegevenstype INT. Het gegevenstype INT ondersteunt zowel getekende als ongetekende waarden. Ongetekende gegevenstypen kunnen alleen positieve getallen bevatten. Het is aan te raden om de ongetekende beperking toe te passen op de auto-increment primaire sleutel.
Syntaxis voor automatische verhoging
Nu de redenering duidelijk is, bekijk dan het script dat gebruikt is om de tabel met filmcategorieën te maken.
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`) );
Let op de aanduiding "AUTO_INCREMENT" bij het veld category_id. Hierdoor wordt de categorie-ID automatisch gegenereerd telkens wanneer een nieuwe rij in de tabel wordt ingevoegd. Deze wordt niet automatisch ingevuld bij het invoegen van gegevens in de tabel. MySQL genereert het.
Let op: Het trefwoord UNSIGNED verdubbelt het positieve bereik van de kolom, en de weergavebreedte die ooit als int(11) werd geschreven, is verouderd. MySQL 8.0.17 en verder. Eenvoudig int is de huidige vorm.
De standaardwaarde voor AUTO_INCREMENT is 1 en wordt met 1 verhoogd voor elk nieuw record.
Laten we de huidige inhoud van de categorieëntabel eens bekijken.
SELECT * FROM `categories`;
Voer het bovenstaande script uit in MySQL Workbench levert de volgende resultaten op bij het gebruik van myflixdb.
| 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 |
Er zijn acht rijen, dus de volgende gegenereerde ID zou 9 moeten zijn. Laten we nu een nieuwe categorie invoegen in de tabel 'categorieën', waarbij we alleen de naam opgeven.
INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');
Het bovenstaande script uitvoeren tegen de myflixdb in MySQL werkbank geeft ons de volgende resultaten, zoals hieronder weergegeven.
| 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 |
Merk op dat we het categorie-ID niet hebben opgegeven. MySQL Dit werd automatisch gegenereerd, omdat de categorie-ID is gedefinieerd als automatisch oplopend.
Als u de laatste invoeg-id wilt ophalen die is gegenereerd door MySQL, kunt u daarvoor de functie LAST_INSERT_ID gebruiken. Het onderstaande script haalt de laatste ID op die is gegenereerd.
SELECT LAST_INSERT_ID();
Door het bovenstaande script uit te voeren, krijgt u het laatst gegenereerde auto-incrementnummer van de INSERT-query. De resultaten worden hieronder weergegeven.
Tip: LAST_INSERT_ID() is alleen geldig voor uw eigen verbinding, waardoor een waarde die door een andere gebruiker is gegenereerd nooit per ongeluk aan u kan worden geretourneerd.
Hoe stel ik de startwaarde van AUTO_INCREMENT in of reset ik deze?
De standaardreeks begint bij 1, maar dat is niet altijd wat een project nodig heeft. Factuurnummers moeten mogelijk worden overgenomen uit een oud systeem en een testtabel moet vaak opnieuw worden ingesteld. MySQL De teller wordt direct weergegeven, waardoor beide gevallen met één enkele clausule worden afgehandeld. Volg deze stappen om het startnummer te bepalen.
- Stel de waarde in tijdens het aanmaken. Voeg de AUTO_INCREMENT-clausule toe aan de CREATE TABLE-instructie. De eerste rij die wordt ingevoegd, krijgt dan dat nummer in plaats van 1.
- De waarde in een bestaande tabel wijzigen. Gebruik ALTER TABLE met dezelfde clausule. MySQL Het nieuwe nummer wordt alleen geaccepteerd als het hoger is dan de grootste identificatiecode die momenteel is opgeslagen.
- Een tabel die je hebt leeggemaakt, kun je opnieuw instellen. TRUNCATE TABLE verwijdert alle rijen en zet de teller in één bewerking terug op 1, iets wat DELETE alleen niet doet.
- Bevestig de wijziging. Voeg een rij in en lees de identifier terug met LAST_INSERT_ID() voordat je op de nieuwe reeks vertrouwt.
-- 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`;
De stapgrootte kan ook worden gewijzigd met de systeemvariabele auto_increment_increment, maar dit geldt voor de hele server in plaats van voor één tabel. Het wordt voornamelijk gebruikt bij replicatie, waarbij twee servers niet dezelfde identifier mogen genereren.
Waarom verschijnen er gaten in een AUTO_INCREMENT-reeks?
Vroeg of laat verschijnt er een tabel met identificaties zoals 1, 2, 5, 6. Niets is kapot. De teller is ontworpen om uniciteit te garanderen, niet om een ononderbroken reeks getallen te garanderen, en hij geeft nooit twee keer dezelfde waarde weer.
Er ontstaan hiaten om de volgende redenen.
- Verwijderde rijen: Wanneer een rij uit een tabel wordt verwijderd, wordt het automatisch gegenereerde ID niet opnieuw gebruikt. MySQL blijft sequentieel nieuwe nummers genereren.
- Teruggedraaide transacties: Het nummer wordt gereserveerd op het moment dat de invoegbewerking wordt uitgevoerd. Als de transactie wordt teruggedraaid, verdwijnt de rij, maar het nummer is al verbruikt.
- Mislukte invoegingen: Een instructie die wordt afgewezen door een UNIQUE-beperking kan nog steeds een identificator gebruiken voordat deze mislukt.
- Inzetstukken in bulk: InnoDB kan een blok getallen reserveren voor een invoeging van meerdere rijen en de ongebruikte getallen verwijderen.
Het is een vergissing om deze hiaten te proberen te dichten. Het hernummeren van rijen verbreekt alle externe sleutels die ernaar verwijzen, en de waarde zelf heeft geen zakelijke betekenis. Als een rapport een doorlopende lijst nodig heeft, genereer dan het rijnummer in de query in plaats van opgeslagen gegevens te herschrijven.
