Site icon Ryadel

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   utf8_general_ci  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.

 

Exit mobile version