Come controllare se una tabella di MySQL è codificata in UTF8

Molto semplicemente eseguendo (cambiando ovviamente dbname e tablename) questa query potete capire la codifica:

select c.character_set_name
from information_schema.tables as t,
     information_schema.collation_character_set_applicability as c
where c.collation_name = t.table_collation
  and t.table_schema = "dbname"
  and t.table_name = "tablename";

Tabella in utf8

Se vi rendete conto che non è in utf8, vi basta eseguire (prima fate un backup):

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Altrimenti se la volete in utf8mb4:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Altri suggerimenti su come fare conversioni potete trovarne qui, oppure qui.

Come convertire un database di MySQL da utf8 a utf8mb4

Se avete finalmente reso compatibile il vostro database a utf8mb4 e lo avete anche configurato correttamente, che ne dite di convertirlo anche? Comprese le tabelle e colonne?

Ovviamente prima fatevi un bel backup di tutto quanto… ora andiamo alla conversione!

Per ogni database (cambiate dbname):

ALTER DATABASE dbname CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Per ogni tabella (cambiate tablename):

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Per ogni colonna (cambiate nome e grandezza delle colonne):

ALTER TABLE tablename CHANGE column_name column_name VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Voilà, ora dovreste essere fully compatible con utf8mb4.

Come configurare MySQL con utf8 o utf8mb4 correttamente

Se utilizzate un database a utf8mb4 (come vi ho spiegato qui) ma nel client, come ad esempio PhpStorm o altri servizi simili, non vi funziona correttamente la codifica dei caratteri… probabilmente è perché la versione in locale che viene scaricata per gestire tutti gli schemes non è correttamente funzionante.

Potete verificarlo facendo:

show variables like "%collation%";
show variables like '%character%';

Oppure con:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Database configurato correttamente a utf8mb4

E se per caso character_set_database o collation_database non risulta utf8, eseguite (modificando il dbname):

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

Se invece volete utf8mb4 come vi avevo indicato nella guida precedente, vi basterà eseguire:

ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Ora rieseguendo la query precedente dovreste avere tutto correttamente configurato.

Come rendere MySQL compatibile con utf8mb4

Di default MySQL supporta utf8 solo a 3 byte, in realtà per avere massima compatibilità con tutte le tipologie di caratteri (quali ad esempio emojis e altri caratteri non latini) bisogna utilizzare utf8mb4 invece di utf8 normale.

E per farlo dovete semplicemente incollare queste righe all’interno del file di configurazione, che potete trovare qui:

sudo nano /etc/mysql/mysql.cnf oppure dentro /etc/my.cnf.

Al fondo inserite:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Poi riavviate il servizio semplicemente con sudo service mysql restart.

Come esportare un db in latin 1 ed importare in utf8 su MySQL

Come spiegato anche in questa guida su come convertire un database da latin 1 a utf8, le chance di successo non sono granché e dipende molto dalla tipologia di database, dalla complessità (quindi anche dal sistema che state utilizzando) e dalla mole di dati che avete da convertire.

Un’altra tecnica può essere quella di esportare il db e di reimportarlo con una codifica diversa. Vediamo come fare…

Come al solito ho supposto che il database si chiami dbname, l’utente con permessi si chiami root e che abbia come password password. I quattro comandi da eseguire sono i seguenti (tutti eseguiti come amministratore):

sudo su

mysqldump --user=root --password=password --default-character-set=latin1 --skip-set-charset dbname > dump.sql

sed -r 's/latin1/utf8/g' dump.sql > dump_utf.sql

mysql --user=root --password=password --execute="DROP DATABASE dbname; CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;"

mysql --user=root --password=password --default-character-set=utf8 dbname < dump_utf.sql

Mi ripeto nel dirvi che la probabilità che questo funzioni non è altissima ma dipende sempre da caso a caso, voi effettuate comunque un backup di tutto.

Come convertire un intero database da latin1 a utf8

Facendola semplice: presupponiamo che il vostro db si chiami dbname ed è codificato come latin1 ma voi volete convertirlo in utf8 in quanto è ovviamente compatibile con più caratteri, le due query in questione sono:

SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "dbname";

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

Se poi volete provare (mi raccomando fate un backup di tutto) a convertire ogni singola tabella… beh ecco a voi questo bello script:

sudo mysql -u root -p --database=dbname -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' | mysql -u root -p --database=dbname

C’è una bella probabilità che non funzioni e che vi riporti errore o rimanga fermo. Nel caso stoppatelo con il solito ctrl+c e provate in qualche altro modo ad effettuare la conversione.