MySQL AUTO_INCREMENT esimerkkien kanssa
Marcus Allen
⚡ Älykäs yhteenveto
MySQL AUTO_INCREMENT luo automaattisesti peräkkäiset numerot numeeriselle sarakkeelle aina, kun rivi lisätään. Määrite poistaa tarpeen laskea yksilöllisiä tunnisteita manuaalisesti, mikä tekee siitä vakiotavan täyttää ensisijainen avain.
Mikä on automaattinen lisäys?
Automaattinen lisäys on toiminto, joka toimii numeerisilla tietotyypeillä. Se luo automaattisesti peräkkäisiä numeerisia arvoja aina, kun tietue lisätään taulukkoon kenttään, joka on määritelty automaattiseksi lisäykseksi.
Attribuutti toimii minkä tahansa kokonaislukutyypin kanssa TINYINT:stä BIGINT:iin. Sarake on myös indeksoitava, mikä tapahtuu automaattisesti, kun se määritetään ensisijaiseksi avaimeksi.
Kun käytät automaattista lisäystä?
Oppitunnilla aiheesta tietokannan normalisointi, tarkastelimme, miten dataa voidaan tallentaa minimaalisella redundanssilla tallentamalla dataa useisiin pieniin taulukoihin, jotka ovat yhteydessä toisiinsa ensisijaisten ja viiteavainten avulla.
Perusavaimen on oltava yksilöllinen, koska se yksilöi tietokannan rivin. Mutta miten voimme varmistaa, että perusavain on aina yksilöllinen?
Yksi mahdollinen ratkaisu olisi käyttää kaavaa ensisijaisen avaimen luomiseen, joka tarkistaa avaimen olemassaolon taulukossa ennen tietojen lisäämistä. Tämä saattaa toimia, mutta lähestymistapa on monimutkainen eikä erehtymätön. Kaksi samaan aikaan lisättyä istuntoa voivat silti lukea saman maksimiarvon ja törmätä toisiinsa.
Tällaisen monimutkaisuuden välttämiseksi ja sen varmistamiseksi, että ensisijainen avain on aina ainutlaatuinen, voimme käyttää MySQL automaattisen lisäyksen ominaisuus ensisijaisten avainten luomiseen. Automaattista lisäystä käytetään INT-tietotyypin kanssa. INT-tietotyyppi tukee sekä etumerkittyjä että etumerkittömiä arvoja. Etumerkittömissä tietotyypeissä voi olla vain positiivisia lukuja. Parhaana käytäntönä on suositeltavaa määrittää etumerkitön rajoite automaattisen lisäyksen ensisijaiselle avaimelle.
Automaattinen lisäyssyntaksi
Kun päättely on selvitetty, tarkastellaan elokuvakategorioiden taulukon luomiseen käytettyä käsikirjoitusta.
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`) );
Huomaa category_id-kentässä oleva ”AUTO_INCREMENT”. Tämä aiheuttaa sen, että luokkatunnus luodaan automaattisesti aina, kun uusi rivi lisätään taulukkoon. Sitä ei anneta, kun tietoja lisätään taulukkoon. MySQL synnyttää sen.
Huomautus: UNSIGNED-avainsana kaksinkertaistaa sarakkeen positiivisen alueen, ja näyttöleveys, joka on kirjoitettu muodossa int(11), on vanhentunut MySQL 8.0.17 ja uudemmat. Yksinkertainen int on nykyinen muoto.
Oletusarvoisesti AUTO_INCREMENT-funktion aloitusarvo on 1, ja se kasvaa yhdellä jokaista uutta tietuetta kohden.
Tarkastellaanpa kategoriataulukon nykyistä sisältöä.
SELECT * FROM `categories`;
Suoritetaan yllä oleva komentosarja MySQL Workbenchin vertailu myflixdb-tiedostoa vastaan antaa seuraavat tulokset.
| 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 |
Rivejä on kahdeksan, joten seuraavan luodun id:n pitäisi olla 9. Lisätään nyt uusi luokka kategoriataulukkoon antamalla vain nimi.
INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');
Suoritetaan yllä oleva komentosarja myflixdb in -ohjelmaa vastaan MySQL työpöytä antaa meille seuraavat alla esitetyt tulokset.
| 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 |
Huomaa, että emme antaneet luokan tunnusta. MySQL luotiin automaattisesti, koska luokkatunnus on määritelty automaattiseksi lisäykseksi.
Jos haluat saada viimeisen lisäystunnuksen, jonka on luonut MySQL, voit käyttää LAST_INSERT_ID-funktiota tehdäksesi sen. Alla näkyvä skripti saa viimeisen luodun tunnuksen.
SELECT LAST_INSERT_ID();
Yllä olevan komentosarjan suorittaminen antaa INSERT-kyselyn luoman viimeisen automaattisen lisäyksen numeron. Tulokset näkyvät alla.
Vinkki: LAST_INSERT_ID()-funktion laajuus on rajattu omaan yhteyteesi, joten toisen käyttäjän lisäyskäskyn luomaa arvoa ei voida koskaan palauttaa sinulle vahingossa.
AUTO_INCREMENT-aloitusarvon asettaminen tai palauttaminen
Oletusjärjestys alkaa numerosta 1, mutta se ei aina ole projektin vaatimus. Laskunumeroiden on ehkä jatkettava vanhasta järjestelmästä, ja testitaulukko on usein nollattava. MySQL näyttää laskurin suoraan, joten molemmat tapaukset käsitellään yhdellä lausekkeella. Noudata näitä ohjeita aloitusnumeron hallitsemiseksi.
- Aseta arvo luontihetkellä. Lisää AUTO_INCREMENT-lause CREATE TABLE -lausekkeeseen. Ensimmäiselle lisätylle riville lisätään sitten kyseinen numero yhden sijaan.
- Muuta olemassa olevan taulukon arvoa. Käyttää ALTER TABLE samalla lausekkeella. MySQL hyväksyy uuden numeron vain, jos se on suurempi kuin suurin tallennettu tunniste.
- Nollaa tyhjentämäsi pöytä. TRUNCATE TABLE poistaa kaikki rivit ja palauttaa laskurin arvoon 1 yhdellä operaatiolla, mitä pelkkä DELETE ei tee.
- Vahvista muutos. Lisää rivi ja lue tunniste takaisin LAST_INSERT_ID()-funktiolla ennen kuin luotat uuteen sekvenssiin.
-- 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`;
Askeleen kokoa voidaan muuttaa myös auto_increment_increment-järjestelmämuuttujalla, mutta se koskee koko palvelinta yhden taulukon sijaan. Sitä käytetään pääasiassa replikoinnissa, jossa kahden palvelimen ei tarvitse luoda samaa tunnistetta.
Miksi AUTO_INCREMENT-sekvenssissä on aukkoja?
Ennemmin tai myöhemmin taulukossa näkyy tunnisteita, kuten 1, 2, 5, 6. Mikään ei ole rikki. Laskuri on suunniteltu takaamaan ainutlaatuisuus, ei takaamaan katkeamatonta numerosarjaa, eikä se koskaan anna samaa arvoa kahdesti.
Aukkoja esiintyy seuraavista syistä.
- Poistetut rivit: Kun rivi poistetaan taulukosta, sen automaattisesti kasvatettua tunnusta ei käytetä uudelleen. MySQL jatkaa uusien numeroiden luomista peräkkäin.
- Takaisin palautetut tapahtumat: Numero varataan heti lisäyksen suorittamisen jälkeen. Jos tapahtuma peruutetaan, rivi katoaa, mutta numero on jo käytetty.
- Epäonnistuneet lisäykset: UNIQUE-rajoitteen hylkäämä lauseke voi silti käyttää tunnistetta ennen epäonnistumista.
- Irtotavarana lisättyjä: InnoDB voi varata numerolohkon moniriviselle lisäykselle ja hylätä ne numerot, joita se ei käytä.
Näiden aukkojen täyttäminen on virhe. Rivien uudelleennumerointi rikkoo kaikki niihin osoittavat viiteavaimet, eikä itse arvolla ole liiketoiminnallista merkitystä. Jos raportti tarvitsee jatkuvan luettelon, luo rivinumero kyselyssä tallennetun datan uudelleenkirjoittamisen sijaan.
