Guida al cambio del set di caratteri MySQL: Converti a utf8mb4 (Correggi il Mojibake)

1. Introduzione

Perché potresti aver bisogno di cambiare il set di caratteri MySQL

Un set di caratteri del database è un’impostazione importante che determina come i dati testuali vengono codificati e processati per la memorizzazione. In MySQL, il set di caratteri predefinito è spesso latin1, il che può causare problemi quando si lavora con il giapponese o altri caratteri speciali. Soprattutto durante la migrazione dei dati o la standardizzazione del sistema, passare a un set di caratteri appropriato diventa essenziale.

Problemi comuni e le loro cause

I problemi tipici relativi ai set di caratteri MySQL includono i seguenti.

1. Mojibake (caratteri illeggibili)

• utf8 e latin1 sono mescolati nello stesso ambiente
• Le impostazioni del set di caratteri del client e del server non corrispondono

1. Problemi durante la ricerca

• A causa di differenze di collazione, i risultati di ricerca attesi non vengono restituiti
• L’ordine di ordinamento differisce da quello previsto

1. Problemi durante la migrazione dei dati

• Emoji e simboli speciali non possono essere salvati perché non viene usato utf8mb4
• La conversione del set di caratteri non è gestita correttamente durante l’esportazione/importazione

Obiettivi dell’articolo e struttura

Questo articolo fornisce una spiegazione completa dei cambiamenti del set di caratteri MySQL, dai concetti fondamentali a come modificare le impostazioni e risoluzione dei problemi.

Struttura

1. Conoscenze di base sui set di caratteri MySQL
2. Come verificare il set di caratteri corrente
3. Come cambiare il set di caratteri MySQL
4. Risoluzione dei problemi dopo la modifica
5. Come i cambiamenti del set di caratteri influenzano le prestazioni
6. Impostazioni consigliate (best practice)
7. FAQ (domande frequenti)

Leggendo questa guida, approfondirai la tua comprensione dei set di caratteri MySQL e sarai in grado di scegliere le impostazioni corrette ed evitare problemi comuni.

2. Cos’è un set di caratteri MySQL? Comprendere le basi

Cos’è un set di caratteri?

Un set di caratteri (Character Set) è un insieme di regole utilizzate per memorizzare e processare i caratteri come dati digitali. Per esempio, quando si memorizza il carattere giapponese “あ”, UTF-8 lo rappresenta come la sequenza di byte E3 81 82, mentre Shift_JIS utilizza 82 A0.

In MySQL, è possibile specificare set di caratteri diversi a livello di database o di tabella. Scegliendo un set di caratteri appropriato, è possibile prevenire il mojibake e rendere più fluida l’internazionalizzazione.

Set di caratteri comuni

Cos’è la collazione?

La collazione è l’insieme di regole usate per confrontare e ordinare i dati all’interno di un set di caratteri. Per esempio, definisce se “A” e “a” sono trattati come lo stesso carattere e come viene determinato l’ordinamento.

Collazioni comunemente usate

Differenze tra utf8 e utf8mb4

Il utf8 di MySQL può effettivamente memorizzare solo fino a 3 byte per carattere, quindi non può gestire alcuni caratteri speciali (come emoji o alcuni caratteri CJK estesi). Al contrario, utf8mb4 supporta fino a 4 byte per carattere, motivo per cui le applicazioni moderne sono consigliate a utilizzare utf8mb4.

Perché dovresti passare da utf8 a utf8mb4

1. Compatibilità futura: i sistemi moderni stanno standardizzando sempre più su utf8mb4.
2. Memorizzazione di caratteri speciali ed emoji: con utf8mb4 è possibile gestire in modo sicuro dati provenienti da post sui social network e app di messaggistica.
3. Internazionalizzazione: per sistemi multilingue, riduce il rischio di mojibake.

Riepilogo

• Il set di caratteri determina come i dati vengono memorizzati e processati.
• La collazione determina come i caratteri vengono confrontati.
• Il utf8 di MySQL è limitato a 3 byte, quindi si raccomanda utf8mb4.
• utf8mb4_unicode_ci è una collazione comunemente consigliata per un uso generale.

3. Come verificare il set di caratteri corrente

Prima di cambiare il set di caratteri MySQL, è importante verificare le impostazioni correnti.
Poiché i set di caratteri possono essere impostati a più livelli (database, tabella, colonna), è necessario capire esattamente dove sono necessarie le modifiche.

Come verificare il set di caratteri corrente

Verifica il set di caratteri a livello di server MySQL

First, check the default character set and collation settings for the entire MySQL server.

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Example output:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                        |
+--------------------------+----------------------------+

Check the Character Set per Database

To check the character set for a specific database, use the following query.

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';

Example output

+----------------+----------------------+----------------------+
| SCHEMA_NAME    | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------+----------------------+----------------------+
| my_database   | utf8mb4               | utf8mb4_unicode_ci   |
+----------------+----------------------+----------------------+

Check a Table’s Character Set

This is how to check the character set for a specific table.

SHOW CREATE TABLE table_name;

Example output

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

Checkpoints

• DEFAULT CHARSET=latin1 → Not utf8mb4 , so a change is needed
• COLLATE=latin1_swedish_ci → Switching to utf8mb4_unicode_ci is usually more appropriate

Check a Column’s Character Set

To inspect the character set at the column level, run the following SQL.

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'database_name' 
AND TABLE_NAME = 'table_name';

Example output

+-------------+--------------------+----------------------+
| COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME       |
+-------------+--------------------+----------------------+
| name        | latin1             | latin1_swedish_ci    |
| email       | utf8mb4            | utf8mb4_unicode_ci   |
+-------------+--------------------+----------------------+

In this example, the name column uses latin1, so it is recommended to change it to utf8mb4.

Summary

• MySQL character sets are configured at multiple levels (server, database, table, column).
• By checking the character set at each level, you can apply the right changes.
• Use commands like SHOW VARIABLES and SHOW CREATE TABLE to fully understand the current configuration.

4. How to Change the MySQL Character Set

By changing the MySQL character set appropriately, you can prevent mojibake and support multilingual data more smoothly.
In this section, we explain how to update settings at each level: server-wide, database, table, and column.

Change the Server-Wide Default Character Set

To change the server-wide default character set, you need to edit the MySQL configuration file (my.cnf or my.ini).

Steps

1. Open the configuration file

• On Linux: bash sudo nano /etc/mysql/my.cnf

• On Windows: wp:list /wp:list

  • Open C:\ProgramData\MySQL\MySQL Server X.X\my.ini

1. Add or change the character set settings Add or update the following lines under the mysqld section.

   [mysqld]
   character-set-server=utf8mb4
   collation-server=utf8mb4_unicode_ci
   

3. Riavvia MySQL

   sudo systemctl restart mysql
   

Su Windows:

net stop MySQL && net start MySQL

4. Verifica la modifica

   SHOW VARIABLES LIKE 'character_set_server';
   

Modifica il set di caratteri a livello di database

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Verifica la modifica

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME = 'mydatabase';

Modifica il set di caratteri a livello di tabella

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Verifica la modifica

SHOW CREATE TABLE users;

Modifica il set di caratteri a livello di colonna

ALTER TABLE users MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Verifica la modifica

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'mydatabase' 
AND TABLE_NAME = 'users';

Verifica post-modifica e importanza dei backup

Per preservare l’integrità dei dati dopo aver cambiato il set di caratteri, segui questi passaggi.

Esegui il backup dei tuoi dati

mysqldump -u root -p --default-character-set=utf8mb4 mydatabase > backup.sql

Ricontrolla le impostazioni

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SHOW CREATE TABLE users;

Inserisci e visualizza dati di test

INSERT INTO users (name, email) VALUES ('Test User', 'test@example.com');
SELECT * FROM users;

Riepilogo

• Modifica del set di caratteri a livello di server : Modifica my.cnf e imposta character-set-server=utf8mb4
• Modifica del set di caratteri del database : ALTER DATABASE mydatabase CHARACTER SET utf8mb4
• Modifica del set di caratteri della tabella : ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4
• Modifica del set di caratteri della colonna : ALTER TABLE users MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4
• Dopo le modifiche, verifica sempre le impostazioni e testa i tuoi dati

5. Risoluzione dei problemi dopo aver cambiato il set di caratteri

Dopo aver cambiato il set di caratteri di MySQL, potresti incontrare situazioni in cui il sistema non si comporta correttamente o i dati memorizzati diventano illeggibili.
In questa sezione, spieghiamo in dettaglio i problemi comuni e come risolverli.

Cause del mojibake e come risolverlo

Se si verifica mojibake dopo aver cambiato il set di caratteri, le seguenti cause sono comuni.

Correzione #1: Impostare correttamente il set di caratteri del client

SET NAMES utf8mb4;

Correzione #2: Convertire correttamente i dati esistenti

UPDATE users SET name = CONVERT(CAST(CONVERT(name USING latin1) AS BINARY) USING utf8mb4);

Note sulla conversione da latin1 a utf8mb4

Procedura sicura

1. Esegui il backup dei dati attuali

   mysqldump -u root -p --default-character-set=latin1 mydatabase > backup.sql
   

2. Modifica il set di caratteri del database

   ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   

3. Modifica il set di caratteri della tabella

   ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   

4. Reimporta i dati

   mysql -u root -p --default-character-set=utf8mb4 mydatabase < backup.sql
   

I dati non sono ricercabili dopo la modifica

Caso #1: La ricerca LIKE non funziona

SELECT * FROM users WHERE name COLLATE utf8mb4_unicode_ci LIKE '%Tanaka%';

Caso #2: L’ordine di ordinamento è cambiato

SELECT * FROM users ORDER BY BINARY name;

Misure lato applicazione

Per PHP

mysqli_set_charset($conn, 'utf8mb4');

Per Python (MySQL Connector)

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="mydatabase",
    charset="utf8mb4"
)

For Node.js (MySQL2)

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydatabase',
  charset: 'utf8mb4'
});

Summary

• Post-change issues generally fall into three categories: client settings, data conversion, and application settings.
• To prevent mojibake, standardize the client character set using SET NAMES utf8mb4.
• Watch for LIKE search and sort order changes, and specify COLLATE when needed.
• Set utf8mb4 in your application as well to avoid encoding mismatches.

6. How Character Set Changes Affect Performance

When changing the MySQL character set to utf8mb4, there are several performance considerations, such as increased storage usage and index limitations.
In this section, we explain the impact and the best countermeasures.

Increased Storage Usage

Compared to MySQL’s utf8, utf8mb4 can use up to 4 bytes per character,
so the overall table size may increase.

Max bytes per character by character set

For example, with utf8, VARCHAR(255) is up to 765 bytes (255×3),
but with utf8mb4, it becomes up to 1020 bytes (255×4).

Countermeasure

ALTER TABLE posts MODIFY COLUMN title VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Increased Index Size

MySQL enforces a maximum index key length.
After switching to utf8mb4, index entries become larger, and you may hit the limit—making indexes unusable.

Check index impact

SHOW INDEX FROM users;

Example error

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Countermeasure

ALTER TABLE users MODIFY COLUMN email VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Impact on Query Performance

Changing the character set to utf8mb4 may affect query execution speed.

Operations that may be affected

• LIKE searches over large datasets
• ORDER BY processing
• JOIN query performance

Countermeasure

CREATE INDEX idx_name ON users(name(100));

Memory Usage and Buffer Tuning

With utf8mb4, memory usage may increase.

Recommended settings

[mysqld]
innodb_buffer_pool_size = 1G
query_cache_size = 128M

Summary

• Switching to utf8mb4 increases storage usage.
• Index sizes increase and may exceed limits.
• Query performance can be affected.
• Because memory usage may increase, buffer sizes may need tuning.

7. Recommended Settings (Best Practices)

By setting MySQL character sets appropriately, you can maintain data integrity while optimizing performance.
In this section, we present recommended MySQL character set configurations and explain key points for an optimal setup.

Recommended MySQL Character Set Configuration

Recommended my.cnf Settings

1. MySQL Server Character Set Settings

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
skip-character-set-client-handshake
innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
query_cache_size = 128M

2. Client-Side Character Set Settings

[client]
default-character-set = utf8mb4

Recommended Database Settings

CREATE DATABASE mydatabase DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

To change an existing database character set:

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Recommended Table Settings

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  email VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Modifica del set di caratteri per le tabelle esistenti

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Differenze tra utf8mb4_general_ci e utf8mb4_unicode_ci

✅ Se hai bisogno di supporto multilingue o di ordinamento accurato, scegli utf8mb4_unicode_ci.

Ottimizzazione degli indici

CREATE FULLTEXT INDEX idx_fulltext ON articles(content);

Riepilogo

• Si raccomanda la combinazione di utf8mb4 + utf8mb4_unicode_ci.
• Standardizza le impostazioni del server (my.cnf) e i set di caratteri di connessione.
• Specifica esplicitamente utf8mb4 a livello di database, tabella e colonna.
• Usa VARCHAR(191) per evitare limitazioni sulla lunghezza delle chiavi di indice.
• Usa utf8mb4_unicode_ci per confronti accurati.

8. FAQ

Ecco le domande più comuni sul cambiamento dei set di caratteri MySQL.
Trattiamo anche come gestire gli errori e come scegliere le impostazioni migliori.

Qual è la differenza tra utf8 e utf8mb4?

SHOW VARIABLES LIKE 'character_set_server';

Cambiare il set di caratteri MySQL causerà perdita di dati?

mysqldump -u root -p --default-character-set=utf8mb4 mydatabase > backup.sql

Come risolvere il mojibake se si verifica?

UPDATE users SET name = CONVERT(CAST(CONVERT(name USING latin1) AS BINARY) USING utf8mb4);

Quali sono i rischi nella conversione da latin1 a utf8mb4?

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Il passaggio a utf8mb4 influisce sulle prestazioni?

ALTER TABLE users MODIFY COLUMN email VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Quale dovrei usare: utf8mb4_general_ci o utf8mb4_unicode_ci?

Le query diventeranno più lente dopo il passaggio a utf8mb4?

CREATE FULLTEXT INDEX idx_fulltext ON articles(content);

Riepilogo

✅ utf8mb4 è consigliato. utf8 non è consigliato a causa delle sue limitazioni.
✅ Prima di apportare modifiche, controlla sempre le impostazioni con SHOW VARIABLES.
✅ Usa flussi di lavoro di esportazione/importazione per prevenire il mojibake.
✅ Considera le limitazioni degli indici e usa VARCHAR(191) dove opportuno.
✅ Per le prestazioni, aggiungi gli indici appropriati.

Note finali

Modificare il set di caratteri MySQL non è solo una semplice modifica di configurazione—è un’operazione critica che può influire su integrità dei dati e prestazioni.
Seguendo le impostazioni e le procedure corrette, puoi migrare a utf8mb4 in modo sicuro ed efficace.

🔹 Segui i passaggi di questo articolo e configura correttamente il tuo set di caratteri! 🔹