Sante Caserio

I miei ultimi articoli su MySQL e argomenti affini sono stati pubblicati su MySQL Italia (e per pigrizia non su questo blog, almeno per ora). La licenza con cui sono distribuiti è la Creative Commons Attribution Non-Commercial Share Alike versione 2.5, che ritengo libera. Potete trovare i miei testi nelle sezioni wiki e articoli, insieme ad altro materiale altrettanto interessante, se non di più.

Ringrazio sentitamente lo staff e in particolare Gianluigi e colgo l’occasione per invitare gli utenti di MySQL a utilizzare MySQL Italia, uno dei pochi siti ben curati e precisi.

Ho appena aggiunto una sezione di una pagina del progetto di documentazione libera di MySQL (metadati sugli eventi). E’ un lavoro sporco, o meglio noioso, ma qualcuno doveva pur farlo. Mi dispiace solo che al momento il progetto sia un po’ abbandonato a sè stesso, perchè non ce n’è mai stato così bisogno come oggi.

Spero che a qualcuno venga voglia di contribuire. Non fatevi intimidire dall’inglese. Prima di tutto, credo che i contributor più attivi non siano madrelingua inglese. E poi c’è sempre bisogno di una traduzione in italiano - anche se fosse parziale e aggiornata di rado sarebbe meglio di niente.

Buona rivolta.

Oltre ai vari problemi elencati nel post precedente e nel post su Drizzle, l’acquisizione di MySQL da parte di Sun ne ha portato un altro, non meno grave: MySQL Community Edition viene ora rilasciato ogni morte di papa. Chi usa questa edizione (cioè tendenzialmente chi non lavora in un’impresa di un certo livello) è costretto a tenersi i bug per parecchio tempo, anche se magari sono già stati corretti da qualche mese.

OurDelta non è proprio un fork - è piuttosto una serie di rilasci non ufficiali che comprendono aggiunte che sono state apportate dalla comunità. Queste build vengono rilasciate certamente più spesso della Community Edition.

Le aggiunte della comunità riguardano per lo più il motore InnoDB, ma riguardano anche altri settori del server. E’ tutto documentato, compreso il grado di stabilità.

E’ attivo un repository contenente i pacchetti deb e (s)rpm.

Il sito di OutDelta è OurDelta.org

MySQL è sicuramente il più diffuso Dbms (DataBase Management System) libero e open source. Storicamente si contendeva gli utenti con PostgreSQL, che era comunque molto diverso. Mentre quest’ultimo era (ed è a maggior ragione oggi) avanzatissimo, estendibile in molti modi e adatto a progetti che richiedono notevoli prestazioni e una notevole complessità, MySQL si manteneva leggero e facile da usare. Il suo codice era pulito, relativamente pochi i bug. Per un progetto di piccole (non per forza piccolissime) dimensioni era spesso la scelta migliore. Molte erano le funzionalità lasciate fuori (viste, stored procedure, trigger…), ma la maggioranza degli utenti non ne aveva bisogno. Chi ne aveva bisogno, naturalmente, doveva dirigersi verso altri lidi.

A partire dalla versione 4.1, la filosofia di MySQL è molto cambiata. Ha iniziato a implementare funzionalità a volte molto complesse, di cui la maggioranza degli utenti non aveva e non ha bisogno. Queste funzionalità aggiungono molto codice al programma, questo codice si porta dietro inevitabilmente: pesantezza, peggioramento delle prestazioni, aumento del consumo di risorse, bug, difficoltà nella programmazione e nella manutenzione. Per quella grande fetta di utenti che utilizza solo funzionalità introdotte prima della versione 4.1, tutto ciò è abbastanza seccante. Non solo: a partire dalla versione 5.0 la qualità del codice è peggiorata tremendamente. Questa versione è stata rilasciata con numerosi bug critici (che provocano il crash) ed era molto lenta rispetto alla 4.1.

Per questo motivo è nato un fork chiamato Drizzle, che comprende solo le funzionalità indispensabili (il codice vecchio è stato quasi completamente… eliminato dal fork, mentre il rimanente è stato risistemato per migliorarne la qualità), però molte funzionalità vengono reimplementate come plugin. In altre parole chi le vuole usare le attiva, gli altri utilizzano solo il codice di cui hanno effettivamente bisogno. Di tutto ciò parlo in maniera più approfondita in questo articolo.

Nel gennaio 2007 MySQL è stato venduto alla Sun Microsystem. Un pessimo affare per entrambi. Per la Sun perchè quest’anno ha annunciato il licenziamento di migliaia di lavoratori: forse quel miliardo di dollari sarebbe stato speso meglio se usato per pagare gli stipendi di esseri umani, piuttosto che la proprietà immateriale di un marchio. Per MySQL perchè da piccola società in cui tutti si conoscono, è diventata una divisione di una multinazionale, in cui forse tutti si conoscono ma certo non conoscono coloro che prendono le decisioni importanti.

Michael Widenius, detto Monty, il fondatore di MySQL, ha lasciato la Sun per mettersi in proprio. Dice di essere comunque contento di aver venduto la società alla Sun… come diceva quella canzone? Ah, si: “parole, parole, parole…”. Uscendo, si è potuto prendere la soddisfazione di rivelare al pubblico un po’ di idiozie commesse dalla Sun.

Prima di tutto la versione 5.1 non è stabile. Le nuove funzionalità sarebbero affette da bug gravi, a volte critici. Questo sarebbe avvenuto perchè è stata dichiarata “beta” quando ancora era in uno stato molto embrionale; dopodichè, sono state rilasciate meno versioni di quanto sarebbe stato necessario, perciò gli utenti non hanno testato il codice a sufficienza. Ma quel che è peggio è che non è stato un errore: è stata una scelta di marketing. La Sun ha pensato che rilasciando troppe versioni beta avrebbero fatto brutta figura. Personalmente ritengo che la brutta figura l’abbiano fatta così, dimostrando la totale incapacità di prendere decisioni sensate.

Insomma Monty, stando a quanto afferma, avrebbe chiesto a più riprese che il modo di lavorare della divisione MySQL cambiasse. In particolare, sostiene, lui avrebbe voluto che il processo di sviluppo fosse più aperto ai volontari esterni. Ciò lascia un po’ perplessi, sapendo che tale processo è sempre stato piuttosto chiuso, fin dagli albori di MySQL. In ogni caso, ora il progetto principale a cui Monty si sta dedicando sarebbe un fork chiamato MariaDB.

MariaDB importerà, man mano, tutte le nuove modifiche che la Sun apporterà al codice di MySQL. Queste modifiche però saranno testate e migliorate da MariaDB. Inoltre si concentreranno soprattutto sullo storage engine Maria. Per chi non lo sapesse, si tratta di una sorta di riscrittura di MyISAM, progettato per essere a prova di crash e per supportare (opzionalmente) le transazioni. Il locking è gestito a livello di pagina, non di riga come in MyISAM. Rinunciando ad alcune funzionalità di Maria è comunque possibile utilizzare gli stessi formati usati da MyISAM (dynamic, fixed, compressed). Maria sarà presente anche in MySQL 6, però tutto lascia pensare che sarà supportato meglio nel fork MariaDB.

La gestione di MariaDB dovrebbe essere molto più aperta ai contributi esterni, così come lo è Drizzle. Entrambi infatti sono progetti ospitati da Launchpad.

A titolo di curiosità… gli altri progetti a cui Monty si sta dedicando sono: OpenOcean (una società che finanzia software libero, cbe esisteva già) e un ristorante che utilizzerebbe i database per migliorare l’esperienza del cliente… qualunque cosa ciò significhi.

Link per approfondimenti

Drizzle

• Drizzle.Org
• Wiki in italiano
• Blog di Brian Aker (fondatore di Drizzle)
• Progetto su Launchpad

MariaDB

• Ask Monty
• Monty Says (blog)
• Documentazione di Maria
  
• Progetto su LaunchPad

Riprendo a scrivere qualcosa di (spero) utile su MySQL e tratto un argomento strano: gli utenti. In realtà MySQL ha sempre avuto un sistema molto potente per la gestione dei permessi, che quasi tutti hanno sempre ignorato creando o utilizzando (in hosting) un utente che ha tutti i permessi possibili almeno sui suoi database. Ma vale la pena di saperne di più.

In realtà bisogna distinguere tra:

• Autenticazione - il login, l’accesso al sistema nudo e crudo
• Permessi - ciò che un utente può o non può fare dopo il login

Per ora parleremo solo di autenticazione, accennando appena ai permessi. Peraltro ciò di cui parleremo prima o poi cambierà, in quanto gli sviluppatori hanno in previsione di creare un sistema di plugin anche per l’autenticazione. In questo modo sarà possibile, oltre ad autenticarsi come già si fa oggi, affidare il riconoscimento degli utenti ad altri software, come ad esempio Kerberos. Per ulteriori info, vedi questo worklog.

Creare un utente

Per creare un nuovo utente in genere si usa l’istruzione CREATE USER, la cui sintassi è:

CREATE USER utente [IDENTIFIED BY [PASSWORD] ‘password’] [, user ...]

utente è il nome dell’utente che stiamo creando.
IDENTIFIED BY [PASSWORD] indica la password da abbinargli. La ragione per cui la clausola non è obbligatoria è che un utente MySQL può anche non avere una password (sconsigliatissimo).
Con una sola istruzione è possibile creare più utenti.

Esempi:

CREATE USER pippo
CREATE USER pippo@topolinia.net
CREATE USER pippo IDENTIFIED BY ‘yukgaspgulp’

Questo può essere anche indicato con la formula ‘nome_utente’@'nome_host’; così facendo, l’utente che stiamo creando sarà utilizzanto solo quando il nome_utente viene usato da nome_host. Si può anche utilizzare il carattere ‘%’, che funzionerà esattamente come funziona in LIKE. Per esempio ‘utente1′@’%.php.net’ permette a utente1 di connettersi da qualsiasi host che faccia parte della sottorete di php.net.

Per create un utente abbiamo bisogno del permesso ‘CREATE USER’.

INFORMATION_SCHEMA

Forse il modo più amichevole per vedere gli utenti esistenti è utilizzare la tabella `USER_PRIVILEGES` del database virtuale `INFORMATION_SCHEMA`. Naturalmente si tratta di una tabella a sola lettura, utilizzabile solo per conoscere gli utenti e non per modificarli. Le due colonne sono:

• GRANTEE - contiene il nome utente nella formula ‘nome_utente’@'nome_host’, anche quando il nome dell’host non è stato specificato in fase di creazione;
• TABLE_CATALOG - inutilizzata (è prevista dall’SQL standard ma in MySQL è sempre null perchè allo stato attuale non esistono i cataloghi; vedi questo worklog)
• PRIVILEGE_TYPE - un tipo di privilegio assegnato all’utente: per ogni privilegio assegnato c’è una riga;
• IS_GRANTABLE - vale ‘YES’ o ‘NO’ e indica se l’utente può assegnare o revocare questo permesso ad altri utenti con le istruzioni CREATE USER, GRANT e REVOKE.

Si, ma dove sono gli utenti?

Le tabelle presenti nell’INFORMATION_SCHEMA sono appunto virtuali e di sola lettura. Ma dove sono registrati fisicamente gli utenti e i loro permessi?

Nel database di sistema ‘mysql’. Bisogna fare molta attenzione nel modificarlo direttamente con le istruzioni INSERT, UPDATE e DELETE, perchè creare incongruenze proprio in questo database può pregiudicare il corretto funzionamento di MySQL. Vi ho terrorizzati? No? Peggio per voi, vorrà dire che farete un pasticcio e dovrete reinstallare tutto. Così imparate a non darmi retta. Ed è inutile che mi mandiate le mail per dirmi “avevi ragione, dovevo stare più attento”, perchè ormai il patatrak è fatto. Vabbè, andiamo avanti :)

L’unica tabella che esamineremo in questo articolo è user. Le sue colonne sono:

• Host - il nome dell’host associato all’utente (’%’ significa ‘qualsiasi host’);
• User - il nome utente;
• Password - la sua password criptata con un algoritmo a una via (si può confrontarla con un’altra password criptandola allo stesso modo e vedere se corrisponde, ma non si può decriptarla);
• ssl_type - il tipo di connessione SSL usata dall’utente (connessione criptata), se usata;
• ssl-cipher - la chiave pubblica, se usata;
• x509_issuer - ente emettitore del certificato SSL, se c’è;
• x509_subject - soggetto del certificato SSL, se c’è;
• max_questions - massimo di domande (SELECT, SHOW, DESCRIBE, HELP) eseguibili in un’ora, 0=nessun limite;
• max_updates - numero di scritture sulle tabelle eseguibili in un’ora, 0=nessun limite;
• max_connections - ?
• max_user_connections - numero di connessioni eseguibili in un’ora, 0=nessun limite;

I privilegi

Per non occupare troppo spazio non ho elencato le colonne relative ai privilegi. Di questi non ci occupiamo più di tanto in questo articolo. Per ora, basti dire che per ogni tipo di privilegio esiste una colonna denominata nomeprivilegio_priv. Per esempio: Inser_priv, Update_priv, Delete_priv. Questo colonne possono avere come valore ‘Y’ o ‘N’.

Le risorse

Le ultime colonne servono a dare un limite alle risorse utilizzabili da ogni singolo utente. Se non avete queste colonne significa che state usando una versione piuttosto vecchia di MySQL, passando ad una versione recente potrete aggiungerle con lo script mysql_upgrade. I limiti sono indicati su base oraria, il valore predefinito è 0 (nessun limite). Oltre che agendo direttamente su questa tabella, possono essere impostati con le istruzioni GRANT e REVOKE, di cui non ci occupiamo qui. Il contatore delle risorse utilizzate finora può essere resettato con il comando SQL FLUSH PRIVILEGES o chiamando mysqladmin reload da riga di comando. Entrambi, però, non riguardano il numero massimo di connessioni.

Altre tabelle

• host - permessi assegnati a un host su un certo database o su tutti;
• tables_priv - permessi assegnati su una tabella specifica;
• columns_priv - permessi su una colonna specifica;
• prov_priv - permessi su una procedura.

Eliminare un utente

Eliminare un utente è molto semplice:

DROP USER utente [, utente ...]

utente è naturalmente il nome dell’utente (degli utenti) che intendiamo eliminare. Il comando non elimina automaticamente i permessi associati all’utente, lasciando quindi delle voci orfane nella tabella relativa.

Per compiere questa operazione dobbiamo disporre del permesso ‘CREATE USER’ o del permesso ‘DELETE’ sul database ‘mysql’.

Cambiare un nome utente o una password

Abbiamo a disposizione due istruzioni SQL che ci permettono di cambiare il nome utente o una password senza agire sul database MySQL.

RENAME USER è stata aggiunta in MySQL 5.0.2 e la sua sintassi è:

RENAME USER nome_vecchio TO nome_nuovo [, ...]

Questa istruzione si limita a modificare il nome utente nella tabella user, ma tutti i privilegi resteranno assegnati al vecchio nome utente (che potrebbe essere ricreato).

Per cambiare una password:

SET PASSWORD [FOR 'utente'] = ‘password’

Se FOR ‘utente’ non è specificato, verrà cambiata la propria password.
utente è il nome dell’utente al quale stiamo cambiando la password.
‘password’ è la nuova password. Può essere già criptata (ma bisogna utilizzare lo stesso algoritmo della funzione PASSWORD()!), o si può usare una delle due funzioni PASSWORD() (più recente) o OLD_PASSWORD() (non sicura, ma compatibile con le vecchie versioni di MySQL). Esempio:

SET PASSWORD FOR pippo = PASSWORD(’londonburns999′)

Drizzle

Il sistema di permessi di MySQL dovrebbe essere stato completamente eliminato da Drizzle, mentre è probabile che l’autenticazione e la gestione utenti funzionino più o meno allo stesso modo. Ne sapremo di più dopo la prima release.

Abbiamo già visto in un altro articolo come selezionare record scelti casualmente. Riassumendo velocemente (ma chi non capisse dovrebbe leggere l’articolo in questione, possibilmente, prima di procedere) si trattava di utilizzare la funzione rand(), che genera numeri casuali, dentro la clausola ORDER BY. In questo modo l’ordine in cui i record vengono selezionati (o aggiornati o cancellati) diventa casuale. Se poi aggiungiamo una clausola LIMIT, verranno selezionati solo i primi record di un insieme scelto casualmente… ovvero, avremo X record scelti a casaccio.

Questo approccio ha 2 limitazioni:

• i record non possono essere ordinati: l’ordine è determinato dalla funzione rand(), quindi non vi è ordine;
• siamo obbligati a selezionare un numero di record prefissato (tutti, oppure il numero di record indicato nella LIMIT).

Anche se esula dall’ambito di questo articolo, accenno rapidamente che al primo problema si potrebbe ovviare anche con una subquery di questo tipo:

SELECT * FROM(
SELECT * FROM CHARACTER_SETS ORDER BY rand() LIMIT 10
) unordered ORDER BY 1

Questo metodo, oltre a non essere (a mio parere) pulitissimo, dai miei test risulta meno performante del metodo che illustrerò in questo articolo.

Basta con i preamboli. Il metodo di cui parleremo oggi è una variazione del precedente. Consiste nell’usare la funzione rand() nella clausola WHERE. Poiché rand() restituisce un numero di tipo FLOAT tra 0 (compreso) e 1 (non compreso), noi sappiamo che vi è il 50% delle probabilità che rand()<0.5. Possiamo sfruttare tale consapevolezza per far sì che un particolare record abbia il 50% delle probabilità di non essere trovato:

SELECT * FROM miatabella WHERE id=1 AND rand()<0.5

In questo caso il record non viene selezionato casualmente, anzi è sempre lo stesso dal momento che ne specifichiamo l’id, ma viene deciso casualmente se il record esiste o meno. Chiaramente possiamo aumentare o diminuire le probabilità a nostro piacimento:

SELECT * FROM miatabella WHERE id=1 AND rand()<0.33 -- 33%
SELECT * FROM miatabella WHERE id=1 AND rand()<0.8 -- 80%

Possiamo anche selezionare tutti i record di una tabella, eliminandone una buona parte con il trucchetto appena spiegato. In questo modo avremo un numero di record variabile, perchè non è possibile determinare quanti record otterranno un valore di rand() minore di X. Probabilmente vorremo anche ordinare i risultati. Esempio:

SELECT * FROM CHARACTER_SETS WHERE rand()<0.5 ORDER BY CHARACTER_SET_NAME

Nulla però ci impedisce di ordinarli in maniera casuale:

SELECT * FROM CHARACTER_SETS WHERE rand()<0.5 ORDER BY rand()

Infine possiamo scegliere un numero fisso di record. Si noti che questa tecnica è utilizzabile solo con grandi tabelle, o meglio, con tabelle il cui numero di record è molto superiore al numero di record che desideriamo legere. Questo perchè, basandosi sul caso, non esiste nessuna garanzia che un certo numero di record risponderà alla clausola WHERE. Se trovare quel numero di record è di importanza critica, questo metodo è sconsigliabile. In caso contrario, è sicuramente più performante che non utilizzare una subquery. Ad ogni modo, si fa così:

SELECT * FROM CHARACTER_SETS WHERE rand()<0.5 ORDER BY CHARACTER_SET_NAME LIMIT 5

Divertitevi!

Come già accennato, MySQL non ha un vero tipo booleano. Il tipo BOOL (o BOOLEAN) è in realtà un sinonimo di TINYINY(1). Gli operatori di confronto e le funzioni pseudobooleane restituiscono 0 invece di FALSE e 1 invece di TRUE. Ma veniamo alla stranezza in questione. Provate a eseguire le query seguenti:

SELECT 2 = 1 = 0
SELECT 3 < 2 < 1
SELECT 'a' = 'b' = 'c'
SELECT 0 = 0 != 0

Queste query restituiscono 1. Che cosa succede internamente? Analizziamo solo la prima: una volta capito cosa accade, diverrà chiaro anche per le altre due. Prima MySQL valuta l’espressione più a sinistra, cioè 2=1, la quale ovviamente è falsa, quindi restituisce 0. Dopodichè valuta l’espressione rimanente, cioè 0=0, che essendo vera restituisce 1.
Non si tratta quindi di un vero e proprio bug, ma il risultato è difficile da interpretare per un essere umano. Stando a quanto si può leggere su Launchpad, Drizzle in qualche modo cercherà di evitare situazioni simili.

Per curiosità ho provato la stessa espressione con Php, che si comporta allo stesso modo ma obbliga a utilizzare le parentesi per evitare espressioni poco chiare. Ad esempio la riga seguente è errata:

echo 2==1==0;

Mentre la riga seguente è corretta e restituisce 1:

echo (2==1)==0;

Sono ben felice di pubblicare un articolo inviatomi dall’amico losciuto, buona lettura.

Ciao,

come dicevo prima le info mi sono servite :) Quello che segue è un esempio di import di record da un database MSSQL ad un database MySQL.
Come prima cosa bisogna che ODBC sia correttamente configurato in modo che il relativo collegamento funzioni.
Testato e funzionante su MSSQL 2000 server e MySQL 5.0.51a-3ubuntu5.4. Per quel che ne so funziona i diversi engine senza problemi.

Questo è uno stralcio di odbc.ini:

[ODBC Data Sources]
dbsumssql = Sybase JDBC Server

…

[Default]
Driver = /usr/local/lib/libtdsodbc.so
UsageCount = 3

…

[dbsumssql]
Driver = /usr/local/lib/libtdsodbc.so
Description = Sybase JDBC Server
Trace = No
Server =
Port = 1433
Database = Card
TDS_Version = 8.0
UsageCount = 3

Uso php, ma le query SQL vanno bene per qualsiasi linguaggio, ovviamente.

///////// copia records da db MSSQL a db MySQL

// lista con i nomi dei campi utilizzata per il loop di formazione della query
$digidbnc = array();

// archivio su MSSQL
$myMSSQLdb=’Card’;

// archivio su MySQL
$myMySQLdb = ‘Card_dbo’;

// le tabelle hanno lo stesso nome sia in MSSQL che MySQL
$myMSSQLtab = ‘cards’;
$myMySQLtab = ‘cards’;

// apri database su MySQL
$hostname_myd = “localhost”;
$database_myd = $myMySQLdb;
$username_myd = “utente”;
$password_myd = “password”;
$myMySQL = mysql_pconnect($hostname_myd, $username_myd, $password_myd) or trigger_error(mysql_error(),E_USER_ERROR);

// apre database su MSSQL per la lettura dei dati da inserire
$connect = odbc_connect(’$myMSSQLdb’, ‘utente’, ‘password’);
$query = “SELECT * FROM $myMSSQLtab”;
$rs = odbc_exec($connect, $query);

// acquisizione dinamica dei nomi dei campi
for($j=1;$j<=odbc_num_fields($rs);$j++) {
$digidbnc[] = odbc_field_name($rs,$j);
}

echo “Inizio inserimento…\n”;

// ciclo di lettura righe dal database in MSSQL
while (odbc_fetch_row($rs)) {
//cotatore alla vecchia maniera, ma sempre efficace,
// voi potete utilizzare anche al Key di $digidbnc[]
$conta = 0;
// parte della query con i nomi dei campi
$campi = “”;
// lista col contenuto dei campi
$fields = array();
// formazione della query di inserimento
$sql = “INSERT INTO $myMySQLtab (”;
$valori = ” VALUES (”;
foreach($digidbnc as $campo) {
$fields[$conta] = odbc_result($rs, $campo);
$campi .= $campo . “, “;
$valori .= “‘$fields[$conta]‘, “;
$conta++;
}
// tolgo l’ultima virgola sulla stringa dei nomi dei campi e
// su quella dei relativi valori
$campi = substr($campi, 0, strlen(rtrim($campi))-1);
$valori = substr($valori, 0, strlen(rtrim($valori))-1);
// stringa definitiva per l’inserimento
$sql = $sql . $campi . “) ” . $valori . “)”;
// selezione del database di destinazione su MySQL
mysql_select_db($database_myd, $myMySQL);
// esecuzione query di inserimento
$res_myd = mysql_query($sql, $myMySQL) or die(mysql_error());
}

odbc_close($connect);
mysql_close();

Vista la flessibilità, è possibile usare questa routine per qualunque db e/o tabella.
Le variabili da modificare sono:
// archivio su MSSQL
$myMSSQLdb=’Card’;

// archivio su MySQL
$myMySQLdb = ‘Card_dbo’;

// le tabelle hanno lo stesso nome sia in MSSQL che MySQL
$myMSSQLtab = ‘cards’;
$myMySQLtab = ‘cards’;

oltre a quelle relative ai server ed utenza e password.

A presto.

Vedi anche: Copiare le tabelle di MySQL

Dalla versione 4.1 MySQL fornisce GROUP_CONCAT(). Questa funzione concatena in un’unica stringa un gruppo di valori che normalmente si trovano su record differenti. Questa funzione deve essere utilizzata con la clausola GROUP BY, ma ovviamente l’uso di quest’ultima può essere pretestuoso. Un esempio per chiarire:

SELECT l.titolo AS titolo, GROUP_CONCAT(a.nome) AS autori FROM libro l
LEFT JOIN libro_autore la ON l.id=la.id_libro
LEFT JOIN autore a ON la.id_autore=a.autore
GROUP BY l.id

Come è facile intuire, la tabella libro_autore rappresenta un’associazione “molti a molti” tra i libri e gli autori. La qui presente doppia JOIN restituisce, per ogni libro, tutti i suoi autori. Grazie alla funzione GROUP_CONCAT() però ogni associazione libro-autore non è un singolo record. Vi è un unico record per ogni libro e tutti gli autori sono elencati nel valore “autori” separati da virgole. Un esempio di possibili risultati:

titolo: The C Programmi Language
autore: B. W. Kernighan,Dennis Ritchie
titolo: La gioia armata
autore: A. M. Bonanno

La sintassi

La sintassi completa è:

GROUP_CONCAT([DISTINCT] espressione [, espressione ...]
[ORDER BY {unsigned_integer | nome_colonna | espressione}
[ASC | DESC] [, nome_colonna ...]]
[SEPARATOR stringa])

E' opportuno ribadire che GROUP_CONCAT può essere utilizzata solo in presenza della clausola GROUP BY. E' per questo che nell'esempio è stata utilizzata, nonostante non abbia alcuna utilità su un campo unico (come id). In caso contrario, la query restituirebbe un errore: "Invalid GROUP BY function'.

Il separatore

Il separatore predefinito è la virgola, senza alcuno spazio prima o dopo. Questo va bene nel caso si voglia ottenere una stringa che PHP “esploderà” in un array tramite la funzione explode() (o qualche altro linguaggio la esploderà con qualche altra funzione). Ovviamente, è possibile solo se i valori da concatenare non contengono virgole.

Il seguente esempio mostra come concatenare valori separandoli con una virgola e uno spazio, in modo che possano essere presentati agli esseri umani senza modifiche da parte di PHP o altri linguaggi:

SELECT l.titolo AS titolo, GROUP_CONCAT(a.nome SEPARATOR ‘, ‘) AS autori FROM libro l
LEFT JOIN libro_autore la ON l.id=la.id_libro
LEFT JOIN autore a ON la.id_autore=a.autore
GROUP BY l.id

Ordinamento

Come si è visto nella sintassi, è possibile estrarre con GROUP BY qualsiasi espressione SQL, comprese subquery, variabili di sessione o globali, etc. Tali valori possono essere ordinati con la clausola ORDER BY.

Per quanto riguarda invece l’ordinamento dei record della query esterna, esso è influenzato naturalmente dalla clausola GROUP BY (si veda in proposito l’articolo La clausola ORDER BY in profondità). Se si desidera invertire tale ordinamento, è possibile utilizzare la parola chiave DESC. Se si desidera ordinare i record in base ad altri campi, non presenti nella GROUP BY, è possibile aggiungere una clausola ORDER BY. Se invece non si desidera affatto ordinarli (ottenendo così performance migliori) è possibile annullare l’ordinamento con ORDER BY NULL. Esempio:

SELECT l.titolo AS titolo, GROUP_CONCAT(a.nome) AS autori FROM libro l
LEFT JOIN libro_autore la ON l.id=la.id_libro
LEFT JOIN autore a ON la.id_autore=a.autore
GROUP BY l.id ORDER BY NULL

Prestazioni

Spesso l’uso di GROUP_CONCAT() può ridurre le comunicazioni tra il client e il server MySQL, che generalmente costituiscono uno dei colli di bottiglia. Inoltre la funzione in sè sembra essere abbastanza veloce.

Limiti

Vi è un limite al numero di record che possono essere concatenati. Esso è rappresentato dalla variabile group_concat_max_len, il cui valore predefinito è 1024. La variabile è modificabile a run time. Esempio:

SET GLOBAL group_concat_max_len=4096

Se poi desideriamo modificare in modo permanente tale limite, basta agire sul file my.cnf.

Un semplice esempio di utilizzo del driver mysql. Si tenga presente che, se possibile, sarebbe meglio utilizzare il driver mysqli.

<?php

define('DB_HOST', '127.0.0.1');
define('DB_USER', 'root');
define('DB_PASS', 'root');
define('DB_NAME', 'mysql');

// mi connettoal db; se non riesco stampo un messaggio di errore
if (!(@$con = mysql_connect(DB_HOST, DB_USER, DB_PASS)))
print "Non riesco a connettermi";

// seleziono il db
if (!(@mysql_select_db(DB_NAME)))
print "Impossibile selezionare il database";

// eseguo una query e assegno i risultati a $result
if (!($result = @mysql_query("SELECT User FROM user")))
print "Impossibile ottenere informazioni sugli utenti";

// scorro le righe di $result
while ($row = mysql_fetch_assoc($result)) {
print $row['User'] . “<br />”;
}

// libero la memoria
@mysql_free_result($result);
@mysql_close($con);

?>

Torna agli esempi su MySQL