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.

Quando utilizzare l'incremento automatico?

Nella lezione sulla normalizzazione del database, abbiamo visto come i dati possono essere archiviati con una ridondanza minima, archiviando i dati in molte piccole tabelle, correlate tra loro utilizzando chiavi primarie ed esterne.

Una chiave primaria deve essere univoca in quanto 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, che verifica l'esistenza della chiave nella tabella prima di aggiungere dati. Questo potrebbe funzionare bene, ma come puoi vedere l'approccio è complesso e non infallibile. Per evitare tale complessità e garantire che la chiave primaria sia sempre univoca, possiamo utilizzare MySQLLa funzionalità di incremento automatico per generare chiavi primarie. L'incremento automatico è utilizzato con il tipo di dati INT. Il tipo di dati INT supporta sia valori con segno che senza segno. I tipi di dati senza segno possono contenere solo numeri positivi. Come best practice, si consiglia di definire il vincolo senza segno sulla chiave primaria di incremento automatico.

Sintassi di incremento automatico

Diamo ora un'occhiata allo script utilizzato per creare la tabella delle categorie di film.

CREATE TABLE `categories` (
  `category_id` int(11) AUTO_INCREMENT,
  `category_name` varchar(150) DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`category_id`)
);

Notare "AUTO_INCREMENT" nel campo Category_id. Ciò fa sì che l'ID della categoria venga generato automaticamente ogni volta che una nuova riga viene inserita nella tabella. Non viene fornito quando si inseriscono dati nella tabella, MySQL lo genera.

Per impostazione predefinita, il valore iniziale per AUTO_INCREMENT è 1 e aumenterà di 1 per ogni nuovo record

Esaminiamo il contenuto attuale della tabella delle categorie.

SELECT * FROM `categories`;

Eseguendo lo script precedente in MySQL 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

Inseriamo ora una nuova categoria nella tabella delle categorie.

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

Tieni presente che non abbiamo fornito l'ID della categoria. MySQL lo ha generato automaticamente per noi perché l'ID della categoria è definito come incremento automatico.

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 precedente fornisce l'ultimo numero di incremento automatico generato dalla query INSERT. I risultati sono mostrati sotto.

Sommario

• L'attributo di incremento automatico quando specificato su una colonna con tipi di dati numerici, genera numeri in sequenza ogni volta che una nuova riga viene aggiunta al database.
• L'incremento automatico viene comunemente utilizzato per generare chiavi primarie.
• Il tipo di dati definito nell'incremento automatico dovrebbe essere sufficientemente grande da contenere molti record. La definizione di TINYINT come tipo di dati per un campo di incremento automatico limita il numero di record che possono essere aggiunti alla tabella solo a 255 poiché qualsiasi valore oltre quello non verrebbe accettato dal tipo di dati TINYINT.
• È considerata una buona pratica specificare il vincolo senza segno sulle chiavi primarie a incremento automatico per evitare di avere numeri negativi.
• Quando una riga viene eliminata da una tabella, il suo ID incrementato automaticamente non viene riutilizzato. MySQL continua a generare nuovi numeri in sequenza.
• Per impostazione predefinita, il valore iniziale per AUTO_INCREMENT è 1 e aumenterà di 1 per ogni nuovo record
• Per consentire alla sequenza AUTO_INCREMENT di iniziare con un altro valore, utilizzare AUTO_INCREMENT = 10