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:
|
1 |
ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci; |
Inutile dirlo, è necessario sostituire DBNAME con il nome del Database da modificare e utf8_general_ci con la Collation che si desidera impostare.
Modificare la Collation di tutte le Tabelle di uno o più Database
|
1 2 |
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME,' COLLATE utf8_general_ci;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='DBNAME' AND TABLE_TYPE = 'BASE TABLE'; |
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:
|
1 2 |
SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') AS mySQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= "DBNAME" AND TABLE_TYPE="BASE TABLE" |
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:
|
1 |
SET foreign_key_checks = 0; |
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.
