Come fare query case sensitive con MySql

Oggi ho scoperto l'acqua calda, ovvero che le query su mysql sono case insensitive.
Praticamente, ho salvato nel mio database un bel codice alfanumerico, composto da numeri e cifre maiuscole e minuscole, e quando sono andato a controllare il tutto tramite query, ho scoperto che anche se inserivo il codice tutto in minuscolo, mysql restituiva lo stesso il risultato.

Vediamo dunque come fare query case sensitive in mysql, in modo tale da sfruttare appieno le potenzialità dei codici complessi, come ad esempio quelli usati nelle password e nelle codici d'accesso in generale.

Come fare query case sensitive con MySql

Per poter effettuare delle query mysql che riconoscano che il dato immesso sia una lettera maiuscola o minuscola (case sensitive), occorre utilizzare la seguente sintassi sql:

SELECT * FROM tabella WHERE BINARY nome like '%paroLa%' 

Utilizzando la sintassi mysql binary, andremo a specificare che vogliamo fare una ricerca nella tabella tenendo conto delle maiuscole e delle minuscole.
Nell'esempio citato infatti, verrà cercato esattamente il termine paroLa, con la elle maiuscola, tutti gli altri risultati verranno scartati.

Ecco due immagini che mostrano le mysql query case sensitive all'opera, con o senza binary:

Ma se invece volessimo che uno specifico campo mysql sia sempre case sensitive, in modo da non doverlo specificare in ogni query?
Semplice, basta settare BINARY come proprietà del campo.

E con le query case sensitive sul database mysql, direi che è proprio tutto... anche se sì, è vero, ci sarebbe anche il metodo COLLATE, che però non è argomento di questo post (cit.).

Ottenere i nomi colonne delle tabelle mysql

Prendiamo ad esempio che vogliate creare delle tabelle dinamicamente con il vostro prode mysql, magari partendo da una tabella con una struttura da utilizzare come base, come potreste mai fare?
Quello che vi serve di sicuro conoscere per questo esercizio, sono i nomi delle colonne di tabella, in modo da sapere come si chiamano i vari campi, e che attributi hanno.

In questo breve articolo vi spiegherò dunque quale sintassi sql utilizzare per poter ottenere la struttura di una tabella mysql, in modo tale da poterne fare quello che vi pare.

Come ottenere i nomi delle colonne delle tabelle Mysql

La sintassi mysql da utilizzare per ricavare la struttura di una tabella, è la seguente:

SHOW COLUMNS FROM tabella

Lanciando questa semplice query infatti, si otterranno a video le seguenti colonne:

• Field: il nome del campo
• Type: il tipo del campo (es. varchar)
• Null: se il dato accetta i valori nulli
• Key: il tipo di chiave utilizzata
• Default: il valore di default
• Extra: es l'attributo auto_increment

A questa sintassi è ovviamente possibile passare dei parametri, tramite il comando WHERE, in modo tale da ottenere ad esempio solo i campi (field) che si chiamano in un certo modo, o di un certo tipo (type).

Tornando all'esempio citato ad inizio post, se volessimo creare dinamicamente una tabella mysql, magari facendo scegliere all'utente solo determinati campi dalla lista, potremmo anche giocarcela inserendo un valore nei commenti dei campi.
Se ad esempio decidiamo di inserire nei campi da non mostrare, il commento nonmostrare, potremmo mostrare solo i nomi dei campi di una tabella mysql che non hanno quel commento.

Ad un osservatore attento a questo punto non sarà sfuggita una cosa: con la sintassi spiegata poco sopra la query non riporta i commenti (comments) ed altri parametri della struttura della tabella.
Per mostrare tutte le colonne di una tabella mysql, anche quelle nascoste, occorre aggiungere il comando FULL.
Ecco come verrebbe la query che mostra anche i commenti presenti nelle colonne della tabella mysql:

SHOW FULL COLUMNS FROM tabella

Bene, ora sapete come ottenere i nomi di tutte le colonne presenti in una tabella mysql, anche quelli normalmente nascosti.
Ciclando queste informazioni vi sarà possibile farci un po' quello che vi pare, anche ricreare una tabella mysql partendo da un'altra che fa da stampo, il tutto senza conoscerne a priori i nomi ed i tipi di campi utilizzati.

Oppure, un'altra possibile applicazione che mi viene in mente che è possibile fare con questa "show columus", è quella di esportare dinamicamente il contenuto di una tabella in formato csv.
Se infatti la desiderata è quella di creare un csv che abbia in testata il nome di colonna di ogni campo che poi verrà esportato, ecco che questa sintassi fa proprio al caso nostro.

Contare numero totale record in query con Limit in mysql

Una necessità che potrebbe insorgere quando si sviluppa un'applicazione che mostra a video solo un numero limitato di risultati (usando la paginazione), è quella ottenere lo stesso il numero totale di record nella query con limit.
Se infatti si usa la sintassi mysql limit e poi si contano i record estratti utilizzando la classica sintassi num_rows, il risultato ottenuto sarà legato al limite impostato nella query.

Ecco dunque che sql ci viene in contro con un'apposita sintassi che risolve questo annoso problema.

Come contare il numero totale di record in una query che usa Limit in mysql

La sintassi mysql da utilizzare per ottenere a video solo un numero limitato di risultati da una query, ma allo stesso tempo sapere anche quanti sono i record totali della query senza limit, è la seguente:

SELECT SQL_CALC_FOUND_ROWS * FROM tabella LIMIT n;
SELECT FOUND_ROWS();

Grazie infatti alla funzione SQL_CALC_FOUND_ROWS, si predispone la query al conteggio totale dei record nonostante la presenza del limit, ed i dati vengono poi pescati dalla funzione FOUND_ROWS().

Il risultato ottenuto sarà il totale dei record presenti nella tabella, pescati con la query originale, senza però tener presente del limit.

Questa sintassi SELECT FOUND_ROWS abbinata al FOUND_ROWS funziona perfettamente dalla console di sql, se però la lanciate dal phpmyadmin, vi restituirà come risultato 1, che è il numero delle righe interessate e non il conteggio richiesto.
Questo perchè probabilmente phpmyadmin considera le due query come scollegate, mentre la console mysql no.

Come fare il backup su MySql

Se hai un server con su più database mysql, una delle cose che sicuramente devi prevedere, è la possibilità di fare il backup di tutti i database e le tabelle mysql presenti sulla tua macchina.
Questo perchè, facciamo le corna, se si dovesse brasare il server che ospita il tuo database, byebye dati.

In questo breve articolo ti mostrerò la sintassi da utilizzare per fare backup mysql, completi o mirati.
Infatti, ci sono anche dei casi in cui basta solo una copia di uno specifico database, e non di tutto quanto, oppure addirittura anche solo il backup di una singola tabella mysql.

Vediamo quindi come fare tutto ciò.

Come fare il backup su MySql

Innanzitutto va precisata una cosa: il backup su mysql non si fa dentro il client sql, o da shell mysql, ma semplicemente da prompt dei comandi di dos (se si è su windows), o da shell linux.

Vediamo subito la sintassi da utilizzare per fare un backup completo di tutti i dabase mysql presenti su una macchina:

mysqldump --all-databases > nome-file.sql

Come dicevo poco sopra, questo comando è da lanciare dal promp di dos o da shell.

Alla sintassi sopra indicata, bisogna ricordarsi anche di passare le credenziali d'accesso come root mysql, ovvero con un utente che sia amministratore.
Ecco quindi una sintassi generale da usare per gli script automatici di backup, ai quali si passa già tutto affinchè possano girare in completa autonomia:

mysqldump -u username --password=password --all-databases > nome-file.sql

Vediamo invece come fare il backup di uno specifico database mysql.
Ecco la sintassi da utilizzare:

mysqldump --databases db1 db2 db3 db4 > nome-file.sql

Ovviamente, per fare quanto scritto sopra, va passato solo il nome di un database mysql.
Altrimenti, basta passare tutti i nomi dei db dei quali si vuole fare la copia, separati da uno spazio.

Infine, per fare un backup di una singola tabella mysql, ecco cosa bisogna scrivere:

mysqldump nome-database nome-tabella > nome-file.sql

Anche in questo caso, è possibile elencare i nomi di più tabelle separati da spazio, per poter fare la copia mirata di più tabelle di uno specifico database.

Come si può ben capire, il comando mysqldump mysql è davvero potente ed efficace, perchè oltre che fare la copia di interi database da una semplice riga di comando, si occupa anche di fare un backup consinstente dei dati, grazie a comandi come lock_tables, che di fatto impediscono che altri utenti possano scrivere nelle tabelle mentre si sta facendo la copia del database.

Estrarre un valore casuale da tabella MySql

Su mysql oltre a poter ordinare i valori presenti in una tabella in ordine crescente o decrescente, è anche possibile ottenere un ordine random, in modo tale che i record presentati a video appaiano in maniera casuale.

Questo tipo di ordinamento può servire sia per avere un elenco di valori random, ma anche ad esempio, per poter estrarre un valore casuale da una tabella.
Vediamo dunque subito come compiere questa semplice operazione col sql.

Come estrarre un valore casuale da una tabella sql con MySql

La sintassi sql da utilizzare per ordinare in maniera random una tabella mysql è la seguente:

SELECT nome-campo FROM nome-tabella ORDER BY RAND() LIMIT 1

La funzione utilizzate per ordinare i risultati sql a caso, è per l'appunto la RAND(), abbinata alla ORDER BY.
Nell'esempio specifico però, ho estratto un campo in maniera casuale, limitando anche i risultati ad uno solo con la LIMIT 1.
Quindi questo esempio estrae un valore casuale da una tabella mysql.

Se invece è tua intenzione avere tutti i dati presenti in una tabella, senza però mettere alcun filtro, ordinati in maniera casuale; quello che devi fare è semplicemente usare la sintassi indicata sopra, senza però mettere il limit.

Ovviamente anche per ordinare casualmente su mysql occorre un po' di criterio.
Se ad esempio hai una tabella con milioni di record e pretendi di ordinarli in maniera random e mostrarli tutti a video, la query potrebbe richiedere bel un po' di tempo.

Limitare risultati tabella MySql

Se utilizzi il database mysql e vuoi fare delle query limitandone però in numero i risultati estratti, devi utilizzare una specifica sintassi sql.
In questo breve articolo ti spiegherò come limitare i risultati di una tabella su mysql, in modo tale da poter filtrare in numero i record da estrarre.

Questo genere di operazioni può essere molto utile per non impastare il database con richieste sql troppo pesanti, o anche per poter creare script che gestiscono la paginazione dei risultati.

Vediamo subito come fare (cit.).

Come limitare i risultati di una tabella su MySql

Ecco la sintassi sql da utilizzare per estrarre solo un numero limitato di record da una tabella di un database mysql:

SELECT campo FROM tabella LIMIT numero

Grazie alla funzione LIMIT infatti, è possibile dire al mysql di estrarre solo tot risultati.
Se si passa un solo numero a questa funzione, verranno estratti tutti i primi n record.

Se invece si passano due numeri alla limit, verranno estratti i record successivi dalla posizione indicata dal primo numero, alla posizione indicata dal secondo numero.

Se ad esempio si vuole pescare tutti i record dalla posizione 11 alla posizione 13, bisogna usare la seguente sintassi:

SELECT campo FROM tabella LIMIT 10,3

Mettendo infatti limit 10,3, si va a dire di pescare i dati dalla posizione 11, per tre posizioni consecutive, quindi fino alla 13.

Bene, ora conosci che conosci i limit di mysql, non ti resta che superarli :p

Come creare un file csv con mysql

Lo sapevi che tramite mysql è anche possibile creare dei file fisici?
Esatto, ad esempio puoi estrarre i risultati di una tabella e salvarli in un file csv, o anche in un altro formato volendo.

Nello specifico dunque, vediamo un esempio di come creare file csv da mysql, tramite uno script sql che salva il risultato di una query in un file fisico presente una specifica cartella del server.
Ecco come fare.

Come creare un file csv da mysql

Per poter generare e salvare un file di tipo csv da mysql, basta utilizzare la seguente sintassi:

SELECT campi INTO OUTFILE 'C:/cartella/nome-file.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM nome-tabella

Questa query infatti, si occupa di pescare i dati da una determinata tabella mysql, e di salvarli dentro un percorso specifico.
Da notare che volendo si può anche specificare tramite sql il delimitatore da usare (in questo esempio la virgola), il carattere per chiudere i risultati (in questo caso le virgolette), ed il delimitatore di riga (\n).

Attenzione però, tutto questo potrebbe non funzionare, e potreste ricevere l'errore:
"The MySQL server is running with the --secure-file-priv option so it cannot execute this statement".
In questo caso per risolvere potete agire in due modi: o salvate il file dentro il percorso indicato nella variabile secure_file_priv del my.ini, oppure la disattivate (sempre da my.ini, il file di configurazione di mysql) e poi riavviate.

Un'ultima considerazione va fatta su un altro possibile errore, il "File nome-file already exists".
Questo errore significa semplicemente che il file che state provando a salvare con la funzione OUTFILE MYSQL esiste già.
Purtroppo questo limite non sembra essere superabile, se non agendo d'astuzia.
In mysql infatti non si possono sovrascrivere i file già esistenti (ma come pro c'è il fatto che è possibile creare nuovi file senza bisogno che questi esistano già), quindi l'unica soluzione è, o creare uno script che prima cancella il file e poi lo ricrea, oppure salvare il file con nomi diversi, magari mettendoci data ed ora nel nome.