Cosa sono le Character Set e le Collation in MySQL e MariaDB?

Quando si lavora con MySQL o derivati come Percona Server o MariaDB, ci si imbatte spesso nei concetti di character set e collation, che sono fondamentali per gestire correttamente il salvataggio e la manipolazione dei dati testuali all’interno dei database. Tuttavia, per molti sviluppatori che iniziano a utilizzare questi DBMS, tali concetti possono risultare complessi o poco chiari.

In questo articolo, esploreremo nel dettaglio cosa sono i character set e le collation in MySQL e MariaDB, perché sono importanti e come influenzano l’archiviazione e la gestione dei dati. Affronteremo i principali caratteri come UTF8, UTF8MB3, UTF8MB4, parleremo dell’importanza delle collation come utf8mb4_general_ci, utf8mb4_unicode_ci, e utf8mb4_unicode_520_ci e capiremo come queste impostazioni possono impattare sulla velocità delle query.

Cos’è un Character Set?

Un character set (insieme di caratteri) è un insieme di simboli e la loro rappresentazione binaria. Ogni database relazionale come MySQL o MariaDB utilizza i character set per gestire come i caratteri vengono codificati e salvati nei campi delle tabelle.

Esempi di Character Set

Ci sono diversi character set utilizzati nei database, alcuni dei più comuni includono:

• latin1: un insieme di caratteri a singolo byte che rappresenta la codifica ISO-8859-1 (comune nelle lingue europee occidentali).
• utf8: un insieme di caratteri che codifica i dati utilizzando la codifica UTF-8. Ogni carattere può richiedere tra 1 e 3 byte. Tuttavia, in MySQL, il nome “utf8” è un po’ fuorviante perché rappresenta solo i caratteri fino a 3 byte (ne parleremo più avanti).
• utf8mb4: una variante di UTF-8 che supporta completamente tutti i caratteri Unicode, inclusi emoji e simboli che richiedono fino a 4 byte.

UTF8 vs UTF8MB4: Qual è la differenza?

Uno dei punti più importanti da capire è la differenza tra utf8 e utf8mb4 in MySQL e MariaDB.

• utf8: è un character set che supporta i caratteri UTF-8, ma solo fino a 3 byte per carattere. Questo significa che può rappresentare solo un sottoinsieme dei caratteri Unicode (approssimativamente 1.112.064 caratteri in totale), ma non supporta caratteri come molte emoji e alcuni simboli asiatici che richiedono 4 byte.
• utf8mb4: è l’implementazione completa della codifica UTF-8 in MySQL e MariaDB. utf8mb4 supporta tutti i caratteri Unicode, inclusi quelli che richiedono 4 byte. Questo è il character set che devi utilizzare se il tuo database deve gestire correttamente emoji o altri caratteri che richiedono più di 3 byte.

Esempio pratico:

Se tenti di salvare un’emoji (ad esempio 😊) in una colonna che utilizza il character set utf8, riceverai un errore o i dati verranno troncati, poiché quel carattere richiede 4 byte, mentre utf8 supporta solo fino a 3 byte. Utilizzando utf8mb4, invece, l’emoji sarà correttamente salvata.

Utilizzo di UTF8MB3

A volte si può vedere il termine utf8mb3, che è una denominazione alternativa per il character set utf8 in MySQL. Questo nome è stato introdotto per rendere più chiaro il fatto che utf8 in MySQL supporta solo caratteri fino a 3 byte, in contrasto con utf8mb4, che supporta l’intero set di caratteri Unicode, inclusi quelli a 4 byte, come le emoji o alcuni caratteri asiatici più complessi. Quindi, in sostanza, utf8mb3 e utf8 sono equivalenti, ma l’uso di utf8mb3 serve a sottolineare la limitazione intrinseca di MySQL nel supportare solo un sottoinsieme di caratteri Unicode con la vecchia denominazione utf8.

Negli ultimi anni, il panorama tecnologico si sta spostando sempre più verso il supporto completo dei caratteri Unicode, inclusi quelli a 4 byte. Per questo motivo, il mondo sta andando verso l’adozione universale di utf8mb4, sia per ragioni di compatibilità con i nuovi standard che per garantire una gestione dei caratteri più completa.

Il “cambio di marcia” verso utf8mb4

In alcune configurazioni, specialmente nelle versioni più recenti di MariaDB, è possibile osservare un “cambio di marcia” nella gestione dei character set. Tradizionalmente, utf8 (o utf8mb3) veniva considerato sufficiente per la maggior parte delle applicazioni che non richiedevano la gestione di caratteri complessi. Tuttavia, con l’aumento della necessità di gestire contenuti multilingue, emoji e altri caratteri speciali, il set di caratteri utf8mb4 ha cominciato a prendere piede come nuova norma.

Un esempio di questo cambiamento si può osservare nel comportamento predefinito dei database. Mentre in passato il character set utf8 era largamente utilizzato, molte delle configurazioni predefinite delle nuove versioni di MySQL e MariaDB stanno passando a utf8mb4 come opzione di default per garantire un supporto più ampio e moderno dei caratteri.

In alcune versioni recenti, può succedere che, senza una configurazione esplicita, un database che storicamente utilizzava utf8 per archiviare stringhe, possa passare implicitamente a utf8mb4. Questo può portare a cambiamenti imprevisti nella gestione dei dati, come una maggiore dimensione di archiviazione delle colonne VARCHAR o TEXT, e potenzialmente impatti sulle prestazioni per quanto riguarda l’indicizzazione e le operazioni di confronto su caratteri complessi.

Implicazioni della Configurazione di MySQL e MariaDB

Per gestire correttamente questo passaggio, è fondamentale controllare e configurare attentamente le impostazioni del database, sia a livello di server che di singola tabella o colonna. In MySQL e MariaDB, molte delle impostazioni riguardanti i character set e le collation possono essere definite nei file di configurazione principali, come my.cnf in MySQL o server.cnf in MariaDB.

Cos’è una Collation?

Una collation è un insieme di regole che determinano come confrontare e ordinare i caratteri in un database. Ogni character set ha una o più collation associate, che specificano come i caratteri vengono confrontati per operazioni come ORDER BY, GROUP BY o per eseguire confronti di uguaglianza.

Principali Collation in MySQL

Le collation hanno nomi che seguono una convenzione specifica. Ad esempio, utf8mb4_general_ci si divide in tre parti:

• utf8mb4: indica il character set a cui appartiene la collation.
• general: indica il tipo di regole di confronto.
• ci: sta per case insensitive, cioè la collation non distingue tra maiuscole e minuscole.

Ecco alcune delle principali collation utilizzate in MySQL e MariaDB:

• utf8mb4_general_ci: Questa è una delle collation predefinite per utf8mb4 e non distingue tra maiuscole e minuscole (case insensitive). Utilizza regole di confronto generali e semplificate, che la rendono particolarmente efficiente in termini di velocità per operazioni come ordinamento e confronto di stringhe. Tuttavia, proprio per la sua natura semplificata, è meno rigorosa e precisa nel trattare alcune complessità linguistiche rispetto allo standard Unicode. Per applicazioni in cui la velocità è critica e la precisione linguistica non è fondamentale, è spesso la scelta preferita.
• utf8mb4_unicode_ci: Questa collation segue rigorosamente le regole Unicode standard per il confronto dei caratteri. È più accurata rispetto a utf8mb4_general_ci quando si lavora con lingue diverse, accenti, simboli complessi e caratteri speciali. Tuttavia, la sua accuratezza ha un costo in termini di prestazioni: può risultare leggermente più lenta nelle query, specialmente su dataset di grandi dimensioni, a causa delle regole di confronto più dettagliate. È consigliata per applicazioni che richiedono una precisione linguistica elevata.
• utf8mb4_unicode_520_ci: Questa è una variante aggiornata di utf8mb4_unicode_ci che implementa le regole dello standard Unicode 5.2. Oltre a mantenere le caratteristiche della versione precedente, supporta nuovi caratteri e simboli introdotti con questa versione del protocollo Unicode, rendendola una scelta adatta per gestire caratteri recenti o speciali. Anche in questo caso, l’accuratezza comporta un possibile rallentamento delle query rispetto alle collation meno precise.

Differenze tra le Collation

utf8mb4_general_ci vs utf8mb4_unicode_ci

utf8mb4_general_ci è più veloce perché applica regole di confronto più semplici, specialmente per le lingue europee. Tuttavia, non gestisce bene tutte le complessità linguistiche. Ad esempio, non distingue correttamente alcune variazioni di caratteri nelle lingue non europee, come le ligature o certi accenti nelle lingue asiatiche.

D’altra parte, utf8mb4_unicode_ci segue strettamente le regole Unicode, gestendo correttamente caratteri speciali, accenti e simboli, il che la rende più adatta a situazioni in cui la precisione linguistica è essenziale.

Impatto sulle prestazioni

L’uso di una collation può avere un impatto significativo sulle prestazioni delle query. Collation più complesse, come utf8mb4_unicode_ci o utf8mb4_unicode_520_ci, possono richiedere più tempo per eseguire confronti e ordinamenti, poiché devono seguire regole più dettagliate.

Ad esempio, se hai una tabella con milioni di righe e stai eseguendo un’operazione di ORDER BY su una colonna con la collation utf8mb4_unicode_ci, potrebbe richiedere più tempo rispetto a una tabella che utilizza utf8mb4_general_ci. Questo è dovuto al fatto che la collation Unicode deve gestire correttamente caratteri complessi, accenti e altri simboli speciali, mentre utf8mb4_general_ci applica regole di confronto più semplici.

Il grafico mostra un confronto delle prestazioni tra diverse collation in MySQL 5.7, misurate in throughput (tps) rispetto al numero di thread utilizzati (4, 24, 64, 128). Le collation confrontate sono:

• utf8mb4_general_ci (default) (in blu)
• utf8mb4_bin (in rosso)
• utf8mb4_unicode_ci (in giallo)
• utf8mb4_unicode_520_ci (in verde)

Osservazioni:

1. utf8mb4_bin (rosso) ha il throughput più alto con tutte le quantità di thread, mostrando le migliori prestazioni.
2. utf8mb4_general_ci (blu), la collation predefinita, è la seconda più veloce, con prestazioni che rimangono costanti e molto vicine a quelle di utf8mb4_bin con 128 thread.
3. utf8mb4_unicode_ci (giallo) ha prestazioni inferiori rispetto a utf8mb4_bin e utf8mb4_general_ci, con un throughput visibilmente inferiore soprattutto a partire dai 24 thread.
4. utf8mb4_unicode_520_ci (verde) è la collation con le prestazioni peggiori, in particolare quando il numero di thread aumenta, confermando un notevole calo nel throughput.

Se si utilizza una collation come utf8mb4_unicode_ci o utf8mb4_unicode_520_ci, ci sarà un impatto significativo sulle prestazioni, specialmente in situazioni con un elevato numero di thread, rispetto all’uso di collation più leggere come utf8mb4_general_ci o utf8mb4_bin.

Casi d’uso pratici

Se stai sviluppando un’applicazione che deve supportare lingue europee occidentali e non ti preoccupi troppo della precisione nelle regole di confronto per altre lingue, utf8mb4_general_ci potrebbe essere una scelta ragionevole. Se, invece, il tuo database deve supportare più lingue e devi essere sicuro che i confronti tra caratteri siano fatti secondo le regole Unicode standard, allora utf8mb4_unicode_ci o utf8mb4_unicode_520_ci sono scelte migliori.

Scegliere il Character Set e la Collation Giusta

La scelta del character set e della collation dipende fortemente dai requisiti della tua applicazione e dal tipo di dati che prevedi di gestire nel database.

Quando usare UTF8MB4

In generale, se stai lavorando su un progetto nuovo, dovresti usare utf8mb4 come carattere predefinito. Anche se non pensi di gestire emoji o simboli Unicode a 4 byte al momento, usare utf8mb4 ti dà la flessibilità di gestire qualsiasi tipo di carattere Unicode in futuro. Non ci sono svantaggi significativi nell’uso di utf8mb4 rispetto a utf8, tranne una leggera maggiorazione nello spazio di archiviazione per i caratteri che richiedono più byte.

Esempio pratico di implementazione:

CREATE DATABASE testdb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

In questo esempio, stai creando un database chiamato testdb con il character set utf8mb4 e la collation utf8mb4_unicode_ci. Questa configurazione garantisce che il database supporti tutti i caratteri Unicode, inclusi emoji, e che segua le regole Unicode standard per confrontare e ordinare i caratteri.

Collation e prestazioni

Come abbiamo già accennato, l’uso di una collation più complessa può impattare le prestazioni. Pertanto, se stai sviluppando un’applicazione in cui la velocità delle query è critica e non ti preoccupi troppo della precisione linguistica, potresti voler scegliere una collation più semplice come utf8mb4_general_ci.

D’altra parte, se la tua applicazione deve gestire diverse lingue e richiede una precisione linguistica rigorosa, dovresti optare per una collation più complessa come utf8mb4_unicode_ci.

Impatto delle Collation su Indici e Ricerche

Un’altra area in cui le collation possono influire è la creazione di indici. Quando crei un indice su una colonna che utilizza una collation, le regole della collation determinano come l’indice viene ordinato. Questo può influire sulle prestazioni delle ricerche nel database come possiamo vedere nell’esempio sotto tratto dal blog di Percona in cui si parla di performance delle collation.

Ad esempio, un indice creato su una colonna con utf8mb4_general_ci potrebbe essere più efficiente rispetto a un indice su una colonna con utf8mb4_unicode_ci, poiché le regole di confronto della collation generale sono più semplici.

CREATE INDEX idx_name ON users (name COLLATE utf8mb4_general_ci);

In questo esempio, l’indice sulla colonna name utilizza la collation utf8mb4_general_ci, il che potrebbe offrire prestazioni migliori nelle ricerche rispetto a un indice che utilizza utf8mb4_unicode_ci.

Conclusioni

I character set e le collation sono componenti cruciali per gestire correttamente i dati testuali in MySQL e MariaDB. Scegliere il character set corretto (preferibilmente utf8mb4 per nuovi progetti) e la collation adeguata può avere un impatto significativo sulla capacità del database di gestire caratteri complessi, come emoji, e su come vengono eseguite operazioni come l’ordinamento e il confronto dei dati.

Essere consapevoli delle implicazioni di queste scelte ti aiuterà a ottimizzare la gestione dei dati testuali e a garantire che la tua applicazione funzioni correttamente e in modo efficiente.

Se il tuo Database o la tua installazione WordPress non riesce a salvare caratteri speciali, contattaci pure per una consulenza e risolvere il problema.