SQL: utilizzo di sequenze

Una sequenza è un insieme di numeri interi 1, 2, 3, ... che vengono generati in ordine su richiesta. Le sequenze vengono spesso utilizzate nei database perché molte applicazioni richiedono che ogni riga di una tabella contenga un valore univoco e le sequenze forniscono un modo semplice per generarle.

Questo capitolo descrive come utilizzare le sequenze in MySQL.

Utilizzo della colonna AUTO_INCREMENT

Il modo più semplice in MySQL per utilizzare le sequenze è definire una colonna come AUTO_INCREMENT e lasciare il resto a MySQL per fare attenzione.

Esempio

Prova il seguente esempio. Questo creerà una tabella e successivamente inserirà alcune righe in questa tabella in cui non è necessario fornire un ID record perché viene incrementato automaticamente da MySQL.

mysql> CREATE TABLE INSECT
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO INSECT (id,name,date,origin) VALUES
   -> (NULL,'housefly','2001-09-10','kitchen'),
   -> (NULL,'millipede','2001-09-10','driveway'),
   -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM INSECT ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

Ottieni valori AUTO_INCREMENT

LAST_INSERT_ID () è una funzione SQL, quindi è possibile utilizzarla da qualsiasi client che capisca come emettere istruzioni SQL. In caso contrario, gli script PERL e PHP forniscono funzioni esclusive per recuperare il valore incrementato automaticamente dell'ultimo record.

Esempio PERL

Utilizzare il mysql_insertidattributo per ottenere il valore AUTO_INCREMENT generato da una query. Questo attributo è accessibile tramite un handle di database o un handle di istruzione, a seconda di come si invia la query. L'esempio seguente fa riferimento a esso tramite l'handle del database.

$dbh->do ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

Esempio PHP

Dopo aver emesso una query che genera un valore AUTO_INCREMENT, recuperare il valore chiamando il mysql_insert_id( ) funzione.

mysql_query ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Rinumerare una sequenza esistente

Potrebbe verificarsi un caso in cui sono stati eliminati molti record da una tabella e si desidera ripetere la sequenza di tutti i record. Questo può essere fatto usando un semplice trucco, ma dovresti stare molto attento a farlo e controllare se il tuo tavolo sta avendo un join con un altro tavolo o meno.

Se si determina che la ripetizione della sequenza di una colonna AUTO_INCREMENT è inevitabile, il modo per farlo è eliminare la colonna dalla tabella, quindi aggiungerla di nuovo.

L'esempio seguente mostra come rinumerare i valori id nella tabella degli insetti utilizzando questa tecnica.

mysql> ALTER TABLE INSECT DROP id;
mysql> ALTER TABLE insect
   -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
   -> ADD PRIMARY KEY (id);

Avvio di una sequenza a un valore particolare

Per impostazione predefinita, MySQL inizierà la sequenza da 1, ma puoi specificare anche qualsiasi altro numero al momento della creazione della tabella.

Il seguente blocco di codice ha un esempio in cui MySQL inizierà la sequenza da 100.

mysql> CREATE TABLE INSECT
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);

In alternativa, è possibile creare la tabella e quindi impostare il valore della sequenza iniziale con ALTER TABLE.

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

↰ Previous page Next page ↱