MySQL AUTO_INCREMENT cu Exemple
Marcus Allen
⚡ Rezumat inteligent
MySQL AUTO_INCREMENT generează automat numere secvențiale pentru o coloană numerică de fiecare dată când se introduce un rând. Atributul elimină necesitatea calculării manuale a identificatorilor unici, ceea ce îl face metoda standard de populare a unei chei primare.
Ce este creșterea automată?
Auto Increment este o funcție care operează pe tipuri de date numerice. Acesta generează automat valori numerice secvențiale de fiecare dată când o înregistrare este inserată într-un tabel pentru un câmp definit ca increment automat.
Atributul funcționează pe orice tip întreg, de la TINYINT până la BIGINT. Coloana trebuie, de asemenea, indexată, ceea ce se întâmplă automat atunci când este declarată drept cheie primară.
Când folosiți creșterea automată?
În lecția despre normalizarea bazei de date, am analizat modul în care datele pot fi stocate cu redundanță minimă, prin stocarea datelor în mai multe tabele mici, legate între ele folosind chei primare și externe.
O cheie primară trebuie să fie unică, deoarece identifică în mod unic un rând într-o bază de date. Dar cum ne putem asigura că cheia primară este întotdeauna unică?
Una dintre posibilele soluții ar fi utilizarea unei formule pentru a genera cheia primară, care verifică existența cheii în tabel înainte de a adăuga date. Această metodă ar putea funcționa, dar abordarea este complexă și nu este infailibilă. Două sesiuni care inserează date în același timp pot citi aceeași valoare maximă și pot intra în coliziune.
Pentru a evita o astfel de complexitate și pentru a ne asigura că cheia primară este întotdeauna unică, putem folosi MySQL Funcția de incrementare automată pentru generarea cheilor primare. Incrementarea automată este utilizată cu tipul de date INT. Tipul de date INT acceptă atât valori cu semn, cât și valori fără semn. Tipurile de date fără semn pot conține doar numere pozitive. Ca practică optimă, se recomandă definirea constrângerii fără semn asupra cheii primare de incrementare automată.
Sintaxa de incrementare automată
Odată ce raționamentul a fost clarificat, uită-te la scriptul folosit pentru a crea tabelul cu categorii de filme.
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`) );
Observați „AUTO_INCREMENT” în câmpul category_id. Aceasta face ca ID-ul categoriei să fie generat automat de fiecare dată când un rând nou este inserat în tabel. Acesta nu este furnizat la inserarea datelor în tabel. MySQL îl generează.
Notă: Cuvântul cheie UNSIGNED dublează intervalul pozitiv al coloanei, iar lățimea afișată, odată scrisă ca int(11), este depreciată din MySQL 8.0.17 în continuare. Simplu int este forma actuală.
În mod implicit, valoarea inițială pentru AUTO_INCREMENT este 1 și va crește cu 1 pentru fiecare înregistrare nouă.
Să examinăm conținutul actual al tabelului de categorii.
SELECT * FROM `categories`;
Executarea scriptului de mai sus în MySQL Workbench-ul pentru myflixdb ne dă următoarele rezultate.
| 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 |
Există opt rânduri, deci următorul ID generat ar trebui să fie 9. Să introducem acum o nouă categorie în tabelul de categorii, furnizând doar numele.
INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');
Executarea scriptului de mai sus împotriva myflixdb în MySQL banc de lucru ne oferă următoarele rezultate prezentate mai jos.
| 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 |
Rețineți că nu am furnizat ID-ul categoriei. MySQL a generat-o automat, deoarece ID-ul categoriei este definit ca incrementare automată.
Dacă doriți să obțineți ultimul ID de inserare care a fost generat de MySQL, puteți utiliza funcția LAST_INSERT_ID pentru a face asta. Scriptul prezentat mai jos primește ultimul id care a fost generat.
SELECT LAST_INSERT_ID();
Executarea scriptului de mai sus oferă ultimul număr de incrementare automată generat de interogarea INSERT. Rezultatele sunt afișate mai jos.
Sfat: LAST_INSERT_ID() este limitată la propria conexiune, deci o valoare generată de inserția altui utilizator nu vă poate fi returnată niciodată din greșeală.
Cum se setează sau se resetează valoarea inițială AUTO_INCREMENT
Secvența implicită începe de la 1, dar acest lucru nu este întotdeauna necesar pentru un proiect. Numerele de factură pot fi necesare pentru a fi preluate dintr-un sistem vechi, iar un tabel de testare trebuie adesea resetat. MySQL expune direct contorul, astfel încât ambele cazuri sunt tratate cu o singură clauză. Urmați acești pași pentru a controla numărul de început.
- Setați valoarea la momentul creării. Adăugați clauza AUTO_INCREMENT la instrucțiunea CREATE TABLE. Primul rând inserat primește apoi acel număr în loc de 1.
- Modificați valoarea dintr-un tabel existent. Utilizare ALTER TABLE cu aceeași clauză. MySQL acceptă noul număr numai dacă acesta este mai mare decât cel mai mare identificator stocat în prezent.
- Resetează un tabel pe care l-ai golit. TRUNCATE TABLE elimină toate rândurile și readuce contorul la 1 într-o singură operație, lucru pe care DELETE singură nu îl face.
- Confirmă modificarea. Introduceți un rând și citiți identificatorul cu LAST_INSERT_ID() înainte de a vă baza pe noua secvență.
-- 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`;
Dimensiunea pasului poate fi modificată și cu variabila de sistem auto_increment_increment, dar se aplică întregului server și nu unui singur tabel. Este utilizată în principal în replicare, unde două servere nu trebuie să genereze același identificator.
De ce apar goluri într-o secvență AUTO_INCREMENT?
Mai devreme sau mai târziu, un tabel afișează identificatori precum 1, 2, 5, 6. Nimic nu este defect. Contorul este conceput pentru a garanta unicitatea, nu pentru a garanta o serie neîntreruptă de numere și nu emite niciodată aceeași valoare de două ori.
Lacunele apar din următoarele motive.
- Rânduri șterse: Când un rând este șters dintr-un tabel, ID-ul său incrementat automat nu este reutilizat. MySQL continuă să genereze noi numere secvenţial.
- Tranzacții anulate: Numărul este revendicat în momentul în care se execută inserarea. Dacă tranzacția este anulată, rândul dispare, dar numărul este deja cheltuit.
- Inserări eșuate: O instrucțiune respinsă de o constrângere UNIQUE poate consuma în continuare un identificator înainte de a eșua.
- Inserții în vrac: InnoDB poate rezerva un bloc de numere pentru o inserare pe mai multe rânduri și le poate elimina pe cele pe care nu le folosește.
Încercarea de a închide aceste goluri este o greșeală. Renumerotarea rândurilor rupe fiecare cheie externă care indică spre ele, iar valoarea în sine nu are nicio semnificație comercială. Dacă un raport are nevoie de o listă continuă, generați numărul rândului în interogare în loc să rescrieți datele stocate.
