MariaDB: le funzioni COALESCE, FIELD ed ELT

MariaDB e MySQL hanno una varietà di funzioni utilizzabili all’interno delle istruzioni SQL, che però non sono molto conosciute – né largamente usate. In questo post intendo illustrarne tre, che in qualche modo si complementano tra loro: COALESCE(), FIELD() ed ELT(). Per ognuna farò un esempio della loro utilità (senza escludere che esistano esempi più comuni e interessanti che io non ho ancora trovato) e spiegherò perché in MariaDB sono più interessanti che in MySQL.

Nota: come al solito, in questo post farò riferimento a MariaDB. Ciò non toglie che quanto scrivo, in buona parte, è vero anche su MySQL.

COALESCE

Fra le tre funzioni, questa è la più comune. Accetta un numero di argomenti variabile e restituisce il primo che è diverso da NULL.
Si supponga di voler leggere (SELECT) il modo migliore per contattare una serie di clienti, utenti, dipendenti o fornitori (o amici, perché no?). Per ognuna di queste persone abbiamo una serie di informazioni per i contatti, ma potremmo non averle tutte. Ad esempio potrebbe macare un numero di cellulare, o la mail. Allora COALESCE() ci viene in aiuto: basta invocarla passandole la lista dei campi che ci interessano, nell’ordine in cui li preferiamo:

SELECT COALESCE(cell, tel_lavoro, tel_casa, email, skype) AS contatto
    FROM cliente;

Per ogni cliente ci verrà restituito il cellulare, o se questo è assente il numero dell’ufficio, o se questo manca il numero di casa, etc.

Prima di passare alla prossima fuzione, vediamo un altro caso interessante: quello in cui, per ogni valore, abbiamo più valori di default. In che senso? Bene, partiamo dal caso base, cioè quello in cui abbiamo un solo valore di default. In questo caso ci basterà usare IFNULL():

SELECT IFNULL(url, CONCAT('http://.../', titolo)) AS url
    FROM pagina;

Ma forse abbiamo più valori di default: se il primo è NULL ripieghiamo sul secondo, e così via. Allora, invece di annidare una serie di IFNULL(), useremo COALESCE():

SELECT 
        COALESCE(p.template, s.template, b.template) AS template
    FROM post AS p
    LEFT JOIN sezione AS s
        ON p.id_sezione = s.sezione
    INNER JOIN blog AS b
        ON p.id_blog = b_id;

L’esempio è molto semplice, ma spieghiamolo: abbiamo un post che fa parte di un blog e, opzionalmente, di una sezione. A ogni post potrebbe essere assegnato un template specifico; se così non è si usa il template di default della sezione; se non esiste, si usa il template di default dell’intero blog.

Se il valore cercato non esiste, COALESCE() restituisce NULL.

Nota: è possibile usare COALESCE() solo se si impostano a NULL le colonne in cui il valore è assente. Chi disapprova questa tecnica e preferisce inserire stringhe vuote dovrà fare a meno di COALESCE(). In teoria, potrebbe anche scrivere una UDF simile a COALESCE() che tratti le stringhe vuote come NULL. Non una Stored Function, perché queste non accettano argomenti variabili.

FIELD

Dopo aver trattato COALESCE(), è naturale presentare FIELD() come suo complemento: FIELD() ci dice in quale caso è stato trovato il valore passato come primo argomento. Un esempio:

SELECT 
        FIELD('pinco@pallino.info', cell, tel_lavoro, tel_casa, email, skype) AS tipo_contatto
    FROM cliente;

A volte può essere utile usare COALESCE() e FIELD() nella stessa query, il che rende ovvia il loro ruolo quasi complementare:

SELECT
        COALESCE(cell, tel_lavoro, tel_casa, email, skype) AS contatto,
        FIELD(COALESCE(cell, tel_lavoro, tel_casa, email, skype), cell, tel_lavoro) AS tipo_contatto
    FROM cliente;

Purtroppo qui il codice è ripetitivo, ma non c’è modo di evitarlo in una caso d’uso comune. Come abbiamo visto però, la ripetitività sussiste solo se le due funzioni vengono usate all’interno della stessa query.

Se il primo argomento è NULL (ossia non c’è un valore), FIELD() restituisce 0.

ELT

Anche ELT() può essere considerato un complemento di FIELD(), infatti dato un indice passato come primo argomento, restetuisce l’argomento corrispondente. Prima di vedere un esempio realistico, vediamone uno completamente inutile che renda più chiara questa spiegazione:

SELECT ELT(2, 'a', 'b', 'c'); -- restituisce 'b'

Ora, supponiamo che una query contenente COALESCE() abbia estratto un certo valore corrispondente a un certo contatto. FIELD() ci dice qual è l’indice del contatto estratto all’interno della nostra lista di campi. ELT() ci restituisce l’informazione complementare: dato l’indice, ci dice qual è il campo corrispondente.

Se il primo argomento è un numero reale, viene arrotondato. Se il primo argomento (o il risultato dell’arrotondamento) non è un numero naturale o è maggiore del numero degli argomenti passati escluso il primo, restituisce NULL.

MariaDB rocks!

Va bene, ammettiamolo: se MariaDB spacca non è certo perché rende più utili queste funzioni… ma il titolo MariaDB rocks mi piace tanto!

Tornando alle cose serie, la funzionalità di MariaDB che ci interessa in questo post sono le Colonne Virtuali. Per chi non le conoscesse, consiglio la lettura della Knowledge Base. Per i molto pigri, dico solo che sono campi che contengono un valore calcolato sulla base delle altre colonne – valore che può essere ricalcolato ogni volta che una SELECT lo richiede, oppure scritto su disco.

Con le Colonne Virtuali possiamo aggiungere, alla nostra tabella cliente, una colonna contatto_preferito e una colonna testuale tipo_contatto_preferito:

CREATE TABLE cliente
(
    ...
    contatto_preferito CHAR(50) AS (COALESCE(cell, tel_lavoro, tel_casa, email, skype)) PERSISTENT,
    tipo_contatto_preferito CHAR(20,ELT(FIELD(COALESCE(cell, tel_lavoro, tel_casa, email, skype), cell, tel_lavoro, tel_casa, email, skype), 'cell', 'tel_lavoro', 'tel_casa', 'email', 'skype'))
);

Non c’è dubbio che la definizione della colonna tipo_contatto_preferito sia orribile. Ma questa tecnica risolve almeno due grossi problemi:

  • Permette di usare FIELD() e ELT() senza rendere illeggibili le query che ne fanno uso. Questa illeggibilità è un ottimo motivo per evitare l’uso di queste funzioni in MySQL. Come si vede, nell’esempio riportato, l’unica cosa poco leggibile è la definizione delle Colonne Virtuali. Ma nella vita di tutti i giorni non avremo alcun bisogno di leggerla, e un buon commento risolve in parte il problema.
  • Senza stare a fare altri esempi (che peraltro andrebbero tratti da applicazioni reali), dovrebbe essere chiaro che le tre funzioni in questione non vanno molto d’accordo con gli indici. Anche se COALESCE() legge diverse colonne, generalmente non è il caso di creare un indice su di esse. Una Colonna Virtuale di tipo PERSISTENT però può essere indicizzata; e anche se scegliamo di non farlo, si tratta di una colonna sola. Anziché leggere un insieme di colonne, possiamo leggerne una sola, forse indicizzata, che viene scritta automaticamente ogni volta che inseriamo o modifichiamo un record.

Gli altri linguaggi

Implementare queste tre funzioni in un qualsiasi linguaggio di programmazione è molto semplice. Solo per i pigri, riporto le implementazioni PHP che uso io:

function coalesce()
{
    $args     = func_get_args();
    $numArgs  = count($args);
    for ($i = 0; $i < $numArgs; $i++) {
        if ($args[$i] !== NULL) {
            return $args[$i];
        }
    }
    return NULL;
}
function elt($index)
{
    if ($index  func_num_args() - 1) {
        return NULL;
    }
    return func_get_arg($index);
}
function field($needle)
{
    if ($needle === NULL) {
        return NULL;
    }
    $numArgs = func_num_args();
    $args = func_get_args();
    for ($i = 1; $i < $numArgs; $i++) {
        if ($needle === $args[$i]) {
            return $i;
        }
    }
    return 0;
}

Teniamo però presente che se si usa una singola Colonna Virtuale di MariaDB (o se si usano queste funzioni in una query MySQL) si riduce il traffico di rete.

A presto!

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...