MySQL AUTO_INCREMENT con esempi

Cos'è l'incremento automatico?

Incremento automatico è una funzione che opera su tipi di dati numerici. Genera automaticamente valori numerici sequenziali ogni volta che un record viene inserito in una tabella per un campo definito come incremento automatico.

L'attributo funziona con qualsiasi tipo intero, da TINYINT a BIGINT. La colonna deve inoltre essere indicizzata, operazione che avviene automaticamente quando viene dichiarata come chiave primaria.

Quando utilizzare l'incremento automatico?

Nella lezione su normalizzazione del databaseAbbiamo esaminato come i dati possono essere archiviati con ridondanza minima, memorizzandoli in molte piccole tabelle, correlate tra loro tramite chiavi primarie e chiavi esterne.

Una chiave primaria deve essere univoca, poiché identifica in modo univoco una riga in un database. Ma come possiamo garantire che la chiave primaria sia sempre univoca?

Una delle possibili soluzioni sarebbe quella di utilizzare una formula per generare la chiave primaria, verificando l'esistenza della chiave nella tabella prima di aggiungere i dati. Questo potrebbe funzionare, ma l'approccio è complesso e non infallibile. Due sessioni che inseriscono dati nello stesso momento potrebbero comunque leggere lo stesso valore massimo e causare un conflitto.

Per evitare tale complessità e per garantire che la chiave primaria sia sempre unica, possiamo utilizzare il MySQL La funzionalità di incremento automatico viene utilizzata per generare chiavi primarie. L'incremento automatico è impiegato con il tipo di dati INT. Il tipo di dati INT supporta valori sia con segno che senza segno. I tipi di dati senza segno possono contenere solo numeri positivi. Come buona pratica, si consiglia di definire il vincolo "senza segno" sulla chiave primaria con incremento automatico.

Sintassi di incremento automatico

Una volta chiarito il ragionamento, esaminiamo lo script utilizzato per creare la tabella delle categorie di film.

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`)
);

Notare “AUTO_INCREMENT” sul campo category_id. Questo fa sì che l’ID della categoria venga generato automaticamente ogni volta che viene inserita una nuova riga nella tabella. Non viene fornito quando si inseriscono i dati nella tabella, MySQL lo genera.

Nota: la parola chiave UNSIGNED raddoppia l'intervallo positivo della colonna e la larghezza di visualizzazione una volta scritta come int(11) è deprecata da MySQL Dalla versione 8.0.17 in poi. Semplice int è la forma attuale.

Di default, il valore iniziale di AUTO_INCREMENT è 1 e verrà incrementato di 1 per ogni nuovo record.

Esaminiamo il contenuto attuale della tabella delle categorie.

SELECT * FROM `categories`;

Eseguendo lo script precedente in MySQL L'analisi con Workbench su myflixdb ci fornisce i seguenti risultati.

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

Esistono otto righe, quindi il prossimo ID generato dovrebbe essere 9. Inseriamo ora una nuova categoria nella tabella delle categorie, fornendo solo il nome.

INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');

Eseguendo lo script precedente su myflixdb in MySQL banco di lavoro ci fornisce i seguenti risultati mostrati di seguito.

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

Si noti che non abbiamo fornito l'ID della categoria. MySQL È stato generato automaticamente perché l'ID della categoria è definito come autoincrementante.

Se desideri ottenere l'ultimo ID di inserimento generato da MySQL, puoi utilizzare la funzione LAST_INSERT_ID per farlo. Lo script mostrato di seguito ottiene l'ultimo ID generato.

SELECT LAST_INSERT_ID();

L'esecuzione dello script sopra riportato restituisce l'ultimo numero di autoincremento generato dalla query INSERT. I risultati sono mostrati di seguito.

Suggerimento: La funzione LAST_INSERT_ID() è limitata alla tua connessione, quindi un valore generato dall'inserimento di un altro utente non può mai esserti restituito per errore.

Come impostare o reimpostare il valore iniziale di AUTO_INCREMENT

La sequenza predefinita inizia da 1, ma non sempre è ciò che serve a un progetto. I numeri di fattura potrebbero dover provenire da un sistema precedente e spesso è necessario reimpostare una tabella di test. MySQL espone direttamente il contatore, quindi entrambi i casi vengono gestiti con un'unica clausola. Segui questi passaggi per controllare il numero iniziale.

1. Imposta il valore al momento della creazione. Aggiungi la clausola AUTO_INCREMENT all'istruzione CREATE TABLE. La prima riga inserita riceverà quindi quel numero anziché 1.
2. Modifica il valore in una tabella esistente. Usa il ALTER TABLE con la stessa clausola. MySQL accetta il nuovo numero solo se è superiore all'identificativo più grande attualmente memorizzato.
3. Reimposta una tabella che hai svuotato. TRUNCATE TABLE rimuove tutte le righe e riporta il contatore a 1 in un'unica operazione, cosa che DELETE da solo non fa.
4. Conferma la modifica. Inserisci una riga e leggi l'identificativo con LAST_INSERT_ID() prima di fare affidamento sulla nuova sequenza.

-- 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`;

La dimensione del passo può essere modificata anche tramite la variabile di sistema auto_increment_increment, ma questa si applica all'intero server anziché a una singola tabella. Viene utilizzata principalmente nella replica, dove due server non devono generare lo stesso identificatore.

Perché compaiono degli spazi vuoti in una sequenza AUTO_INCREMENT?

Prima o poi una tabella mostrerà identificatori come 1, 2, 5, 6. Non c'è niente di rotto. Il contatore è progettato per garantire l'unicità, non per garantire una sequenza ininterrotta di numeri, e non emette mai lo stesso valore due volte.

Le lacune si verificano per i seguenti motivi.

• Righe eliminate: Quando una riga viene eliminata da una tabella, il suo ID a incremento automatico non viene riutilizzato. MySQL continua a generare nuovi numeri in sequenza.
• Transazioni annullate: Il numero viene assegnato nel momento stesso in cui viene eseguita l'operazione di inserimento. Se la transazione viene annullata, la riga scompare ma il numero è già stato utilizzato.
• Inserimenti non riusciti: Un'istruzione rifiutata da un vincolo UNIQUE può comunque consumare un identificatore prima che si verifichi l'errore.
• Inserti sfusi: InnoDB può riservare un blocco di numeri per un inserimento su più righe e scartare quelli non utilizzati.

Tentare di colmare queste lacune è un errore. Rinumerare le righe invalida ogni chiave esterna che punta a esse e il valore stesso non ha alcun significato aziendale. Se un report necessita di un elenco continuo, è preferibile generare il numero di riga nella query anziché riscrivere i dati memorizzati.

DOMANDE FREQUENTI