Come incrementare un campo con chiave univoca

La domanda viene da uno scorato frequentatore di una mail list: Ho una tabella InnoDB con una chiave univoca sul campo numerico ID. Devo incrementare il campo di cinque unità, ma i miei tentativi non hanno dato frutto. Infatti, se provo a usare la formula SET ID = ID + 5, ottengo un errore di violazione di chiave. Questa è l’istruzione che ho tentato


BEGIN;
UPDATE nometabella SET ID = ID + 1;
COMMIT;

Credevo che InnoDB avrebbe contenuto l’errore per ogni singola violazione, e aggiornato tutta la tabella, e solo dopo avrebbe considerato che in realtà non c’è conflitto perché dopo che tutti i record sono stati aggiornati si ritorna a valori univoci. La mia tabella ha 12000 record. Non vorrei essere costretto a usare 12.000 istruzioni di UPDATE. Cosa posso fare?

Questa è stata la mia risposta:

InnoDB fa il suo mestiere, fermandosi al primo errore. Il meccanismo transazionale fa sì che la serie di istruzioni venga interrotta al primo errore, dando la possibilità al programmatore di chiamare un ROLLBACK. Quindi, al primo record, ci sarà un conflitto di valori che renderà impossibile l’aggiornamento.

Tuttavia, esiste una soluzione elegante, senza dover inviare 12.000 richieste. Basta chiedere l’aggiornamento con ordinamento decrescente:


UPDATE nometabella SET ID = ID + 1 ORDER BY ID DESC;

In questo modo, il motore aggiornerà la tabella in ordine decrescente di ID, garantendo che non ci saranno conflitti. Se invece si volesse decrementare il valore, basta usare l’ordinamento crescente (omettendo la clausola DESC).

Ordinare per condizioni trovate

FN mi ha proposto un problema interessante. Deve ricercare un archivio usando tre condizioni unite dall’operatore OR, e gli serve che il risultato sia ordinato in modo che i record che rispondono alla prima condizione vengano per primi, poi quelli della seconda condizione, e infine quelli della terza.

Lui si è arenato alla semplice query senza ordinamento.


mysql> SELECT nome, stipendio, DDN
FROM impiegati
WHERE
nome LIKE 'M%'
OR DDN < '1960-01-01' OR stipendio > 6000;
+---------+-----------+------------+
| nome | stipendio | DDN |
+---------+-----------+------------+
| Mario | 5100 | 1956-10-24 |
| Marco | 5600 | 1943-03-09 |
| John | 5550 | 1955-04-02 |
| Maria | 5700 | 1979-12-11 |
| Colette | 6100 | 1960-08-14 |
| Antonio | 6200 | 1968-12-08 |
| Nina | 6100 | 1967-05-24 |
+---------+-----------+------------+
7 rows in set (0.00 sec)

Così a prima vista io ho tirato fuori una soluzione che sembra fare al caso suo. Basta ordinare con una funzione CASE che assegna un valore a ogni condizione.


mysql> SELECT nome, stipendio, DDN
FROM impiegati
WHERE nome like 'M%'
OR DDN < '1960-01-01' OR stipendio > 6000
ORDER BY
CASE
WHEN nome LIKE 'M%' THEN 1
WHEN DDN < '1960-01-01' THEN 2 WHEN stipendio > 6000 THEN 3
END;
+---------+-----------+------------+
| nome | stipendio | DDN |
+---------+-----------+------------+
| Mario | 5100 | 1956-10-24 |
| Marco | 5600 | 1943-03-09 |
| Maria | 5700 | 1979-12-11 |
| John | 5550 | 1955-04-02 |
| Colette | 6100 | 1960-08-14 |
| Antonio | 6200 | 1968-12-08 |
| Nina | 6100 | 1967-05-24 |
+---------+-----------+------------+
7 rows in set (0.01 sec)

Il brutto di questo approccio, mi fa gentilmente notare FN, è che le condizioni vengono valutate due volte, e anche se questo avviene solo per i record trovati (la clausola ORDER BY viene eseguita dopo che i record sono stati filtrati) è sempre uno spreco e un rallentamento.

Pertanto, concentrandomi sull’efficienza, ho trovato un’altra soluzione:


mysql> SELECT nome, stipendio, DDN
FROM impiegati
WHERE nome like 'M%'
UNION
SELECT nome, stipendio, DDN
FROM impiegati
WHERE DDN < '1960-01-01' UNION SELECT nome, stipendio, DDN FROM impiegati WHERE stipendio > 6000 ;

Questa query risolve il problema dell’inefficienza dell’operatore OR con campi diversi, e inoltre risolve anche il problema principale dell’ordinamento. Benché teoricamente l’ordine del risultato di una query sia imprevedibile, il modo in cui MySQL organizza i dati in una UNION fa sì che l’ordinamento sia quello voluto. Si ricordi che una UNION senza la clausola ALL elimina i record duplicati, per cui se un record viene ritrovato grazie alla prima condizione, verrà scartato quando sono valutate la seconda e la terza.
Risultato ottenuto, e ottimizzato allo stesso tempo!