MySQL – Come modificare la Collation di tutte le Tabelle e/o Colonne di un Database

MySQL - How to convert all Database Tables and Columns to a specific Collation

Qualche mese fa ho pubblicato un articolo in cui spiegavo come convertire tutte le tabelle di un Database da MyISAM a InnoDB e viceversa mediante un semplice query builder basato sull’istruzione CONCAT. Lo stesso approccio può essere utilizzato per modificare la Collation di uno o più Database, con a tutte le tabelle e/o colonne contenute.

Prima di proseguire, è opportuno fare chiarezza sul significato del termine Collation, che spesso viene impropriamente confuso con il Character Set. Il modo migliore per farlo è leggere l’illuminante definizione presente nella documentazione ufficiale MySQL:

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.

Quando si lavora con un Database MySQL è buona norma utilizzare la stessa Collation su tutte le tabelle che abbiamo la necessità di collegare tra loro attraverso query JOIN, UNION, ricerche FULL-TEXT, ricerche basate su LIKE e via dicendo. Quando questo non accade, c’è un concreto rischio di ricevere errori come il seguente:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation

Lo scopo di questo articolo è proprio quello di risolvere questa tipologia di errori, spesso dovuti al fatto che un Database è stato aggiornato, spostato o modificato in modo improprio o attraverso una serie di operazioni non organiche, presentando tabelle aventi Collation diverse. Nello specifico, verranno presentate delle query che consentiranno di modificare la Collation di uno o più Databases, di tutte le Tabelle di uno o più Database, e di tutte le Colonne testuali (varchar, text et al.) ivi contenute.

Modificare la Collation di un Database

Cominciamo dalla più semplice di queste operazioni:

Inutile dirlo, è necessario sostituire DBNAME con il nome del Database da modificare e     con la Collation che si desidera impostare.

Modificare la Collation di tutte le Tabelle di uno o più Database

Anche in questo caso è necessario effettuare le modifiche di cui sopra: se si desidera modificare la Collation su più di un Database è sufficiente aggiungere una o più condizioni OR alla WHERE di cui sopra, specificando i nomi dei Database su cui effettuare l’operazione.

IMPORTANTE: La query di cui sopra non farà altro che generare una serie di query, che poi sarà necessario eseguire a loro volta per effettuare la modifica sul Database.

Modificare la Collation di tutte le Tabelle e di tutte le Colonne di uno o più Database

Il comando di cui sopra modificherà la Collation di tutte le tabelle, ma non il character set utilizzato per memorizzare i valori delle colonne testuali (varchar, text et al.) ivi contenute. Per modificare anche il contenuto delle colonne, è necessario eseguire anche questa query:

Anche in questo caso, la query produrrà una serie di query da eseguire per effettuare la modifica: prima di eseguirl, in questo, potrebbe essere necessario disabilitare temporaneamente il controllo delle chiavi esterne nel seguente modo:

Se lo fate, ricordatevi di ripristinarlo al termine dell’operazione.

In ogni caso, prima di effettuare ciascuna di queste operazioni è fortemente consigliabile eseguire un backup completo del Database: è opportuno ricordare che l’istruzione ALTER TABLE (per convertire il valore delle colonne testuali da un character set all’altro), MySQL proverà ad effettuare la conversione attraverso un mapping dei caratteri: qualora i set di caratteri risultassero incompatibili, si potrebbe verificare una perdita di dati. Per maggiori informazioni su questo specifico aspetto, consigliamo di leggere la documentazione ufficiale di MySQL.

 

About Ryan

IT Project Manager, Web Interface Architect e Lead Developer di numerosi siti e servizi web ad alto traffico in Italia e in Europa. Dal 2010 si occupa anche della progettazione di App e giochi per dispositivi Android, iOS e Mobile Phone per conto di numerose società italiane. Microsoft MVP for Development Technologies dal 2018.

View all posts by Ryan

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.