Dov’è il NULL?

Nell’esaminare dati creati da altri, o dati creati da noi stessi in tempi lontani, spesso accade di volerne ottimizzare la struttura, per migliorare l’efficienza. Un caso frequente è la ricerca di campi di chiavi esterne con valori NULL, per poter normalizzare le dipendenze da altre tabelle.

Ma comunque i campi NULL possono, quando usati in maniera irragionevole, influenzare negativamente la precisione o l’efficienza di una query. Quindi succede spesso che un programmatore si chieda: “in quali campi di questa tabella c’è almeno un NULL?”

Se la tabella è piccola, basta uno sguardo.


mysql> select * from persone;
+----+--------+---------+--------+
| id | nome | cognome | id_dep |
+----+--------+---------+--------+
| 1 | Fabio | Rossi | NULL |
| 2 | Mario | Bianchi | NULL |
| 3 | Carlo | Verdi | NULL |
| 4 | Gino | Neri | NULL |
| 5 | Ugo | Rossi | 2 |
| 6 | Piero | Verdi | 1 |
| 7 | Fulvio | Neri | NULL |
| 8 | NULL | Rossi | NULL |
| 9 | NULL | Bianchi | NULL |
+----+--------+---------+--------+
9 rows in set (0.02 sec)

Ma se la tabella ha decine di colonne e centinaia di record, il compito di esaminarla a mano è proibitivo. Tuttavia, possiamo agevolarci il compito con qualche query ad hoc. Sappiamo che la funzione COUNT restituisce un conteggio dell’elemento richiesto. Nella tabella sopra una query


mysql> SELECT count(id_dep) FROM persone;

restituisce 2. Questo ci dice che il conteggio salta i valori NULL. Se diamo alla funzione l’elemento ‘*’, però, ci viene restituito un conteggio dei record, a prescindere dal contenuto. Verifichiamo questo fatto.


mysql> select * from tuttinull;
+------+------+
| id | nome |
+------+------+
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
+------+------+
4 rows in set (0.01 sec)

Questa tabella ha quattro record, con NULL in tutti i campi.


mysql> select count(*) from tuttinull;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.01 sec)

Il conteggio, come annunciato, è riferito ai record. Quindi possiamo sfruttare questo fatto per vedere quali campi contengono un NULL, esaminando il conteggio dei singoli campi, e comparandolo al conteggio totale:


mysql> select count(*), count(nome), count(cognome), count(id_dep) from persone;
+----------+-------------+----------------+---------------+
| count(*) | count(nome) | count(cognome) | count(id_dep) |
+----------+-------------+----------------+---------------+
| 9 | 7 | 9 | 2 |
+----------+-------------+----------------+---------------+
1 row in set (0.02 sec)

Questo ci dice che ci sono 7 nomi su 9 record, 9 cognomi su 9, e 2 id_dep su 9. Ancora meglio, possiamo chiedere al database di fare il calcolo per noi, e darci direttamente il numero di record mancanti:


mysql> select count(*),
-> count(nome) as nome,
-> count(*) - count(nome) as `NULL nome`,
-> count(cognome) as cognome,
-> count(*) - count(cognome) as `NULL cognome`,
-> count(id_dep) as id_dep,
-> count(*) - count(id_dep) as `NULL id_dep`
-> from persone;
+----------+------+-----------+---------+--------------+--------+-------------+
| count(*) | nome | NULL nome | cognome | NULL cognome | id_dep | NULL id_dep |
+----------+------+-----------+---------+--------------+--------+-------------+
| 9 | 7 | 2 | 9 | 0 | 2 | 7 |
+----------+------+-----------+---------+--------------+--------+-------------+
1 row in set (0.02 sec)

Questo metodo funziona, ma se abbiamo una tabella con tanti campi, non è più un metodo efficiente. È possibile però ottenere lo stesso risultato con un approccio programmatico, come questo esempio in Perl dimostra:


#!/usr/bin/perl -w
use strict;
use DBI;


my $dbh = DBI->connect('dbi:mysql:test', 'utente','password')
or die "problema di connessione\n";
my $colonne = $dbh->selectcol_arrayref(qq{describe persone})
or die "colonne non trovate";


my $query = qq{select count(*), }
. join(",", map {qq{count(`$_`) as `$_`,
count(*) - count(`$_`) as `NULL $_`} } @$colonne)
. qq{ from persone};


my $sth = $dbh->prepare($query);
$sth->execute() or die "problema di esecuzione\n";


my $row = $sth->fetchrow_hashref;
printf "%-28s => %s \n", $_, $row->{$_} for @{$sth->{NAME}};
$sth->finish();

Col risultato:


count(*) => 9
id => 9
NULL id => 0
nome => 7
NULL nome => 2
cognome => 9
NULL cognome => 0
id_dep => 2
NULL id_dep => 7

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!