MySQL AUTO_INCREMENT példákkal
Marcus Allen
⚡ Okos összefoglaló
MySQL Az AUTO_INCREMENT minden sor beszúrásakor automatikusan sorszámokat generál egy numerikus oszlophoz. Az attribútum szükségtelenné teszi az egyedi azonosítók kézi kiszámítását, így ez az elsődleges kulcs kitöltésének szabványos módja.
Mi az automatikus növelés?
Az Auto Increment egy olyan funkció, amely numerikus adattípusokon működik. Automatikusan szekvenciális numerikus értékeket generál minden alkalommal, amikor egy rekordot beszúrnak egy táblázatba egy automatikus növekedésként meghatározott mezőhöz.
Az attribútum bármilyen egész szám típuson működik, a TINYINT-től a BIGINT-ig. Az oszlopot indexelni is kell, ami automatikusan megtörténik, amikor elsődleges kulcsként deklaráljuk.
Mikor használja az automatikus növelést?
A leckében adatbázis normalizálás, megvizsgáltuk, hogyan lehet minimális redundanciával tárolni az adatokat, sok kis táblázatban tárolva azokat, amelyek elsődleges és idegen kulcsok segítségével kapcsolódnak egymáshoz.
Egy elsődleges kulcsnak egyedinek kell lennie, mivel egyértelműen azonosít egy sort az adatbázisban. De hogyan biztosíthatjuk, hogy az elsődleges kulcs mindig egyedi legyen?
Az egyik lehetséges megoldás az lenne, hogy egy képletet használjunk az elsődleges kulcs generálására, amely az adatok hozzáadása előtt ellenőrzi a kulcs meglétét a táblázatban. Ez működhet, de a megközelítés összetett és nem hibátlan. Két, ugyanabban a pillanatban beszúrt munkamenet is ugyanazt a maximális értéket olvashatja, és ütközhetnek.
Az ilyen bonyolultság elkerülése, és annak biztosítása érdekében, hogy az elsődleges kulcs mindig egyedi legyen, használhatjuk a MySQL automatikus növelés funkció elsődleges kulcsok generálásához. Az automatikus növelés az INT adattípussal használatos. Az INT adattípus mind az előjeles, mind az előjel nélküli értékeket támogatja. Az előjel nélküli adattípusok csak pozitív számokat tartalmazhatnak. Ajánlott gyakorlatként az automatikus növelés elsődleges kulcsára vonatkozóan definiálni az előjel nélküli korlátozást.
Automatikus növekmény szintaxis
Miután a gondolatmenet leegyeztetett, nézzük meg a filmkategóriák táblázatának létrehozásához használt szkriptet.
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`) );
Figyeljük meg az „AUTO_INCREMENT” paramétert a category_id mezőben. Ez azt eredményezi, hogy a kategóriaazonosító minden új sor beszúrásakor automatikusan generálódik a táblázatba. Az azonosítót nem kell megadni, amikor adatokat illesztünk be a táblázatba. MySQL generálja azt.
Jegyzet: Az UNSIGNED kulcsszó megduplázza az oszlop pozitív tartományát, és az int(11)-ként írt megjelenítési szélesség elavult. MySQL 8.0.17-es verziótól felfelé. Sima int a jelenlegi forma.
Alapértelmezés szerint az AUTO_INCREMENT kezdőértéke 1, és minden új rekordnál 1-gyel növekszik.
Vizsgáljuk meg a kategóriatábla jelenlegi tartalmát.
SELECT * FROM `categories`;
A fenti szkript végrehajtása MySQL A Workbench és a myflixdb összehasonlítása a következő eredményeket adja.
| 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 |
Nyolc sor létezik, tehát a következő generált azonosítónak 9-nek kell lennie. Most illesszünk be egy új kategóriát a kategóriák táblázatba, csak a nevet adva meg.
INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');
A fenti szkript végrehajtása a myflixdb in MySQL munkapad az alábbi eredményeket adja nekünk.
| 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 |
Megjegyzendő, hogy nem adtuk meg a kategória azonosítóját. MySQL automatikusan generálta, mivel a kategória azonosítója automatikus növekedésűként van definiálva.
Ha szeretné lekérni az utolsó beszúrási azonosítót, amelyet a MySQL, használhatja a LAST_INSERT_ID függvényt ehhez. Az alább látható szkript az utoljára generált azonosítót kapja.
SELECT LAST_INSERT_ID();
A fenti szkript végrehajtása visszaadja az INSERT lekérdezés által generált utolsó automatikus növelési számot. Az eredmények alább láthatók.
Tipp: A LAST_INSERT_ID() hatóköre a saját kapcsolatodra korlátozódik, így egy másik felhasználó beszúrása által generált érték soha nem kerülhet vissza hozzád véletlenül.
Az AUTO_INCREMENT kezdőérték beállítása vagy visszaállítása
Az alapértelmezett sorrend 1-gyel kezdődik, de egy projektnek nem mindig erre van szüksége. Előfordulhat, hogy a számlaszámokat egy korábbi rendszerből kell folytatni, és egy teszttáblát gyakran vissza kell állítani. MySQL közvetlenül a számlálót teszi elérhetővé, így mindkét esetet egyetlen záradékkal kezeljük. A kezdőszám szabályozásához kövesd az alábbi lépéseket.
- Állítsa be az értéket a létrehozáskor. Fűzd be az AUTO_INCREMENT záradékot a CREATE TABLE utasításhoz. Az első beszúrt sor ezután ezt a számot kapja 1 helyett.
- Módosítsa egy meglévő tábla értékét. Felhasználás ALTER TABLE ugyanazzal a záradékkal. MySQL csak akkor fogadja el az új számot, ha az nagyobb, mint a jelenleg tárolt legnagyobb azonosító.
- Állítsd vissza az kiürített asztalt. A TRUNCATE TABLE egyetlen művelettel eltávolítja az összes sort és 1-re állítja vissza a számlálót, amit a DELETE önmagában nem tesz meg.
- Erősítse meg a módosítást. Szúrj be egy sort, és olvasd vissza az azonosítót a LAST_INSERT_ID() függvénnyel, mielőtt az új szekvenciára hagyatkoznál.
-- 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`;
A lépésköz az auto_increment_increment rendszerváltozóval is módosítható, de ez az egész szerverre vonatkozik, nem pedig egyetlen táblára. Főleg replikációban használják, ahol két szerver nem generálhat ugyanazt az azonosítót.
Miért jelennek meg hézagok egy AUTO_INCREMENT szekvenciában?
Előbb vagy utóbb egy táblázat olyan azonosítókat jelenít meg, mint az 1, 2, 5, 6. Semmi sem hibás. A számláló célja az egyediség garantálása, nem pedig a számok megszakítás nélküli sorozatának garantálása, és soha nem adja ki ugyanazt az értéket kétszer.
A rések a következő okok miatt jelennek meg.
- Törölt sorok: Amikor egy sort törölnek egy táblázatból, az automatikusan növelt azonosítója nem kerül újra felhasználásra. MySQL folytatja az új számok szekvenciális generálását.
- Visszavont tranzakciók: A szám a beszúrás futtatásának pillanatában igénylésre kerül. Ha a tranzakciót visszavonják, a sor eltűnik, de a szám már elköltésre került.
- Sikertelen beillesztések: Egy UNIQUE megszorítás által elutasított utasítás továbbra is felhasználhat egy azonosítót, mielőtt meghiúsulna.
- Tömeges betétek: Az InnoDB lefoglalhat egy számblokkot egy többsoros beszúráshoz, és elveti azokat, amelyeket nem használ.
Hiba megpróbálni betömni ezeket a hézagokat. A sorok újraszámozása minden rájuk mutató idegen kulcsot érvénytelenít, és maga az érték nem hordoz üzleti jelentést. Ha egy jelentésnek folyamatos listára van szüksége, akkor a tárolt adatok átírása helyett a lekérdezésben kell generálni a sorszámot.
