La modalità SQL (sql_mode) è forse una delle variabili di sistema del server MySQL più importanti in assoluto. Per mezzo di questa variabile viene controllata la modalità SQL di MySQL o MariaDB (essendo quest’ultima derivata proprio da MySQL). Molte caratteristiche operative di MySQL possono essere configurate impostando la modalità SQL. Impostando la modalità SQL in modo appropriato, un programma client può istruire il server su quanto sia “rigoroso” (STRICT) o “indulgente” nell’accettare i dati di input, abilitare o disabilitare i comportamenti relativi alla conformità SQL standard o fornire una migliore compatibilità con altri sistemi di database (ad esempio l’ambiente di sviluppo in locale che è solitamente meno restrittivo dell’ambiente di produzione finale).
Di default, MySQL utilizza un valore sql_mode uguale a ‘ ‘ (stringa vuota), che equivale a nessuna restrizione. Pertanto, il server funziona in modalità indulgente. In questa modalità, MySQL produrrà meno errori possibile in fase di inserimento/modifica dei record in quanto converte i valori di input errati nei valori legali più vicini (come determinato dalle definizioni delle colonne) e continua per la sua strada. Ad esempio, se si tenta di memorizzare un valore negativo in una colonna UNSIGNED, MySQL lo converte in zero, che è il valore legale più vicino per la colonna.
La modalità SQL è controllata tramite la variabile di sistema sql_mode. Il modo più semplice per impostare un valore a questa variabile, è quella di utilizzare l’istruzione SET dalla shell di MySQL oppure tramite PhpMyAdmin o qualsiasi altro manager SQL che riesca ad eseguire delle query SQL. In alternativa, ma non sempre è possibile, si può impostare un valore modificando il file di configurazione /etc/my.cnf.
sql_mode può accettare una stringa vuota oppure uno o più modalità separati da virgola.
- Se il valore è la stringa vuota o contiene più modalità separate da virgola, deve essere racchiuso tra virgolette.
- Se il valore contiene una sola modalità, le virgolette non sono necessarie.
I valori della modalità SQL non fanno distinzione tra maiuscole e minuscole. Ecco alcuni esempi:
Per cancellare la modalità SQL o per impostare la modalità non rigorosa:
SET sql_mode = '';
Viceversa, per impostare una modalità rigorosa, occorre specificare delle restrizioni attraverso delle opportune parole chiave. Il modo più generale per abilitare le restrizioni sui valori di input consiste nell’utilizzare le modalità STRICT_TRANS_TABLES o STRICT_ALL_TABLES:
SET sql_mode = STRICT_TRANS_TABLES; SET sql_mode = STRICT_ALL_TABLES;
Queste modalità impediscono l’immissione di valori non validi come quelli fuori intervallo o NULL specificato per le colonne NOT NULL. In tali casi, MySql restituirebbe un errore (qualora ovviamente si verificasse) in fase di inserimento o aggiornamento delle informazioni.
Per impostare la modalità SQL utilizzando una singola modalità:
SET sql_mode = ANSI_QUOTES; oppure, ad esempio: SET sql_mode = TRADITIONAL;
La modalità TRADITIONAL, abilita diverse restrizioni sul controllo della data e sulla divisione per zero. In particolare fa sì che MySQL si comporti come server di database più tradizionali nella gestione dei dati di input. Se vuoi che il tuo server MySQL sia il più restrittivo possibile riguardo al controllo dei dati di input, il modo più semplice per raggiungere questo obiettivo è abilitare la modalità TRADITIONAL piuttosto che un elenco di singole modalità più specifiche.
Per impostare la modalità SQL utilizzando più modalità:
SET sql_mode = 'IGNORE_SPACE, ANSI_QUOTES'; oppure, ad esempio: SET sql_mode = 'STRICT_ALL_TABLES, ERROR_FOR_DIVISION_BY_ZERO';
Per controllare l’attuale impostazione sql_mode, bisogna interrogare MySQL in questo modo:
SELECT @@sql_mode;
Alcune modalità SQL sono composite, cioè abilitano effettivamente un set di modalità, come ad esempio ANSI e TRADITIONAL. Per vedere quante modalità è composta una modalità composita, basta recuperare il valore dopo averlo impostato:
SET sql_mode=TRADITIONAL; SELECT @@sql_mode;
Se desideri un controllo semplificato della data che richieda solo che i valori di mese e giorno siano compresi nei rispettivi intervalli da 1 a 12 e da 1 a 31, bisogna abilitare la modalità ALLOW_INVALID_DATES:
SET sql_mode = ALLOW_INVALID_DATES;
Altrimenti, possiamo utilizzare ALLOW_INVALID_DATES per un controllo “più permissivo” della data anche in modalità rigorosa (STRICT). Quindi impostiamo la modalità rigorosa, ma permettiamo l’uso di date “non valide”
SET sql_mode = 'STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
L’elenco seguente descrive brevemente alcuni dei valori:
- ANSI_QUOTES
Questa modalità fa sì che il carattere di doppia virgoletta (“) venga interpretato come un carattere di citazione dell’identificatore piuttosto che come un carattere di citazione della stringa. - IGNORE_SPACE
Di default, le funzioni devono essere scritte senza spazi tra il nome della funzione e la parentesi successiva. L’abilitazione di questa modalità fa sì che il server ignori gli spazi dopo i nomi delle funzioni. Ciò consente la visualizzazione di spazi tra il nome e la parentesi, ma fa anche in modo che i nomi delle funzioni siano parole riservate. - ERROR_FOR_DIVISION_BY_ZERO
Di default, la divisione per zero produce un risultato NULL e non viene trattata in modo speciale. L’abilitazione di questa modalità fa sì che la divisione per zero nel contesto dell’inserimento di dati nelle tabelle produca un avviso o un errore in modalità Strict. - STRICT_TRANS_TABLES, STRICT_ALL_TABLES
Questi valori abilitano la “modalità rigorosa” che impone determinate restrizioni sui valori accettabili come input del database. Di default, MySQL è indulgente nell’accettare valori mancanti, fuori intervallo o malformati. L’abilitazione della modalità rigorosa fa sì che i valori errati vengano trattati come errori.
STRICT_TRANS_TABLES abilita la modalità rigorosa per le tabelle transazionali e STRICT_ALL_TABLES abilita la modalità rigorosa per tutte le tabelle. - TRADITIONAL
Si tratta di una modalità composita che abilita entrambe le modalità rigorose oltre a diverse restrizioni aggiuntive sull’accettazione dei dati di input. - ANSI
Questa è una modalità composita che rende MySQL Server più “simile ad ANSI”. Cioè, abilita comportamenti che sono più simili a SQL standard, come ANSI_QUOTES (descritto in precedenza) e PIPES_AS_CONCAT, dove il simbolo || viene trattato come operatore di concatenazione di stringhe piuttosto che come OR logico.
L’elenco completo delle modalità può essere consultato da qui:
https://mariadb.com/kb/it/sql_mode/
