Chiunque lavori abitualmente con MySQL (e con i DBMS in generale) conosce bene l'attributo AUTO_INCREMENT e come utilizzarlo per fare in modo che una colonna di una tabella - spesso la chiave primaria - contenga una identità univoca per ciascun record. Un ottimo esempio dell'utilizzo di questo attributo si può trovare nella documentazione ufficiale di MySQL:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM; INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich'); SELECT * FROM animals; |
che restituisce i seguenti risultati:
|
1 2 3 4 5 6 7 8 9 10 |
+----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+ |
Detto questo, potremmo chiederci cosa succede nei casi in cui la nostra chiave primaria è articolata su due colonne e l'attributo AUTO_INCREMENT in questione è presente soltanto in una di queste.
Il counter riparte da 1 ad ogni cambio di valore dell'altra colonna?
|
1 2 3 4 5 6 7 8 9 10 |
+--------+----+---------+ | grp | id | name | +--------+----+---------+ | fish | 1 | lax | | mammal | 1 | dog | | mammal | 2 | cat | | mammal | 3 | whale | | bird | 1 | penguin | | bird | 2 | ostrich | +--------+----+---------+ |
Oppure la numerazione progressiva viene mantenuta a prescindere?
|
1 2 3 4 5 6 7 8 9 10 |
+--------+----+---------+ | grp | id | name | +--------+----+---------+ | fish | 1 | lax | | mammal | 2 | dog | | mammal | 3 | cat | | mammal | 4 | whale | | bird | 5 | penguin | | bird | 6 | ostrich | +--------+----+---------+ |
La risposta dipende sia dal DB Engine utilizzato che, soprattutto, da come vengono creati gli indici della tabella all'interno del comando CREATE TABLE . La documentazione ufficiale dice che:
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix . This is useful when you want to put data into ordered groups.
Questo significa che, se vogliamo che la nostra tabella si comporti come nella prima ipotesi, dobbiamo crearla inserendo la colonna grp prima della colonna id quando impostiamo la PRIMARY KEY come nell'esempio seguente:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE animals ( grp ENUM('fish','mammal','bird') NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (grp,id) ) ENGINE=MyISAM; INSERT INTO animals (grp,name) VALUES ('mammal','dog'),('mammal','cat'), ('bird','penguin'),('fish','lax'),('mammal','whale'), ('bird','ostrich'); SELECT * FROM animals ORDER BY grp,id; |
Inutile dire che, invertendo l'ordine delle colonne nella definizione della PRIMARY KEY , otterremo il risultato mostrato nella seconda ipotesi.
Alcune considerazioni da fare:
- Ricordate sempre che - come spiegato abbondantemente dalla documentazione ufficiale di MySQL - i valori impostati dall'attributo AUTO_INCREMENT vengono sempre riutilizzati nei casi in cui viene eliminato il valore più alto in qualsiasi gruppo di chiavi primarie. Questo significa che, se imposterete la PRIMARY KEY per ottenere quanto descritto nella prima ipotesi, questo accadrà per ciascuno dei gruppi AUTO_INCREMENT che verranno a crearsi.
- Se la colonna AUTO_INCREMENT è presente in altri indici relativi alla tabella, ed è la prima colonna ad essere menzionata in almeno uno di questi, i valori saranno generati prendendo in considerazione quell'indice a prescindere da quanto definito a livello di chiave primaria. In altre parole, se la tabella animals descritta in precedenza contenesse un indice di tipo INDEX (id) , MySQL ignorerebbe l'indice corrispondente alla chiave primaria e genererebbe valori univoci per la colonna id , ottenendo così il risultato descritto nella seconda ipotesi.
- IMPORTANTE: Se state utilizzando questa tecnica in un cluster di DB in replication e la vostra versione di MySQL è inferiore alla 5.1.65 rischierete di incorrere in alcuni problemi descritti dalla documentazione ufficiale: An INSERT into a table that has a composite primary key that includes an AUTO_INCREMENT column that is not the first column of this composite key is not safe for statement-based logging or replication. Beginning with MySQL 5.1.64, such statements are marked as unsafe. (Bug #11754117, Bug #45670). Fortunatamente, questi bug sono stati risolti in conseguenza del rilascio di MySQL 5.1.65.
Purtroppo, nel caso in cui la vostra tabella utilizzi InnoDB, non c'è modo di ottenere lo stesso risultato mediante strumenti nativi dell'engine: questo perché, come spiegato nella documentazione ufficiale, any InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column. Se avete l'esigenza di ottenere un risultato analogo a quello della prima ipotesi per una tabella InnoDB, potete sfruttare le potenzialità del clustered composite index di InnoDB, come descritto in questo intervento sul sito StackOverflow, e vedere se può adattarsi alla vostra situazione.
Questo è quanto: felice sviluppo!
