Guide de changement de jeu de caractères MySQL : Convertir en utf8mb4 (corriger le mojibake)

1. Introduction

Pourquoi vous pourriez avoir besoin de changer le jeu de caractères MySQL

Un jeu de caractères de base de données est un paramètre important qui détermine comment les données textuelles sont encodées et traitées pour le stockage. Dans MySQL, le jeu de caractères par défaut est souvent latin1, ce qui peut poser des problèmes lorsqu’on travaille avec le japonais ou d’autres caractères spéciaux. En particulier lors d’une migration de données ou d’une standardisation du système, passer à un jeu de caractères approprié devient essentiel.

Problèmes courants et leurs causes

Les problèmes typiques liés aux jeux de caractères MySQL comprennent les suivants.

1. Mojibake (caractères corrompus)

• utf8 et latin1 sont mélangés dans le même environnement
• Les paramètres de jeu de caractères du client et du serveur ne correspondent pas

1. Problèmes lors de la recherche

• En raison de différences de collation, les résultats de recherche attendus ne sont pas renvoyés
• L’ordre de tri diffère de ce que vous attendez

1. Problèmes lors de la migration de données

• Les emojis et symboles spéciaux ne peuvent pas être enregistrés parce que utf8mb4 n’est pas utilisé
• La conversion du jeu de caractères n’est pas correctement gérée lors de l’export/import

Objectifs de l’article et structure

Cet article fournit une explication complète des changements de jeu de caractères MySQL, des concepts fondamentaux aux modifications de paramètres et au débogage.

Plan

1. Connaissances de base sur les jeux de caractères MySQL
2. Comment vérifier le jeu de caractères actuel
3. Comment changer le jeu de caractères MySQL
4. Débogage après le changement
5. Impact des changements de jeu de caractères sur les performances
6. Paramètres recommandés (meilleures pratiques)
7. FAQ (questions fréquemment posées)

En lisant ce guide, vous approfondirez votre compréhension des jeux de caractères MySQL et serez capable de choisir les bons paramètres et d’éviter les problèmes courants.

2. Qu’est‑ce qu’un jeu de caractères MySQL ? Comprendre les bases

Qu’est‑ce qu’un jeu de caractères ?

Un jeu de caractères (Character Set) est un ensemble de règles utilisé pour stocker et traiter les caractères sous forme de données numériques. Par exemple, lorsqu’on stocke le caractère japonais « あ », UTF-8 le représente par la séquence d’octets E3 81 82, tandis que Shift_JIS utilise 82 A0.

Dans MySQL, vous pouvez spécifier différents jeux de caractères au niveau de la base de données ou de la table. En choisissant un jeu de caractères approprié, vous pouvez éviter le mojibake et faciliter l’internationalisation.

Jeux de caractères courants

Qu’est‑ce qu’une collation ?

La collation est l’ensemble de règles utilisé pour comparer et trier les données au sein d’un jeu de caractères. Par exemple, elle définit si « A » et « a » sont traités comme le même caractère et comment l’ordre est déterminé.

Collations couramment utilisées

Différences entre utf8 et utf8mb4

Le utf8 de MySQL ne peut en réalité stocker que 3 octets maximum par caractère, il ne peut donc pas gérer certains caractères spéciaux (comme les emojis ou certains caractères CJK étendus). En revanche, utf8mb4 prend en charge jusqu’à 4 octets par caractère, c’est pourquoi les applications modernes sont encouragées à utiliser utf8mb4.

Pourquoi passer de utf8 à utf8mb4

1. Compatibilité future : les systèmes modernes se standardisent de plus en plus sur utf8mb4.
2. Stockage de caractères spéciaux et d’emojis : avec utf8mb4, vous pouvez gérer en toute sécurité les données des publications SNS et des applications de messagerie.
3. Internationalisation : pour les systèmes multilingues, cela réduit le risque de mojibake.

Résumé

• Le jeu de caractères détermine comment les données sont stockées et traitées.
• La collation détermine comment les caractères sont comparés.
• Le utf8 de MySQL est limité à 3 octets, donc utf8mb4 est recommandé.
• utf8mb4_unicode_ci est une collation couramment recommandée pour un usage général.

3. Comment vérifier le jeu de caractères actuel

Avant de changer le jeu de caractères MySQL, il est important de vérifier les paramètres actuels.
Comme les jeux de caractères peuvent être définis à plusieurs niveaux (base de données, table, colonne), vous devez comprendre exactement où les modifications sont nécessaires.

Comment vérifier le jeu de caractères actuel

Vérifier le jeu de caractères au niveau du serveur MySQL

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

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

Exemple de sortie :

+--------------------------+----------------------------+
| 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                        |
+--------------------------+----------------------------+

Vérifier le jeu de caractères par base de données

Pour vérifier le jeu de caractères d’une base de données spécifique, utilisez la requête suivante.

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

Exemple de sortie

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

Vérifier le jeu de caractères d’une table

Voici comment vérifier le jeu de caractères d’une table spécifique.

SHOW CREATE TABLE table_name;

Exemple de sortie

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;

Points de contrôle

• DEFAULT CHARSET=latin1 → Pas utf8mb4, donc un changement est nécessaire
• COLLATE=latin1_swedish_ci → Passer à utf8mb4_unicode_ci est généralement plus approprié

Vérifier le jeu de caractères d’une colonne

Pour inspecter le jeu de caractères au niveau de la colonne, exécutez le SQL suivant.

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

Exemple de sortie

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

Dans cet exemple, la colonne name utilise latin1, il est donc recommandé de la changer en utf8mb4.

Résumé

• Les jeux de caractères MySQL sont configurés à plusieurs niveaux (serveur, base de données, table, colonne).
• En vérifiant le jeu de caractères à chaque niveau, vous pouvez appliquer les modifications appropriées.
• Utilisez des commandes comme SHOW VARIABLES et SHOW CREATE TABLE pour bien comprendre la configuration actuelle.

4. Comment changer le jeu de caractères MySQL

En changeant correctement le jeu de caractères MySQL, vous pouvez éviter le mojibake et prendre en charge les données multilingues plus facilement.
Dans cette section, nous expliquons comment mettre à jour les paramètres à chaque niveau : à l’échelle du serveur, de la base de données, de la table et de la colonne.

Modifier le jeu de caractères par défaut à l’échelle du serveur

Pour modifier le jeu de caractères par défaut à l’échelle du serveur, vous devez éditer le fichier de configuration MySQL (my.cnf ou my.ini).

Étapes

1. Ouvrir le fichier de configuration

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

• Sous Windows : wp:list /wp:list

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

1. Ajouter ou modifier les paramètres du jeu de caractères Ajoutez ou mettez à jour les lignes suivantes sous la section mysqld.

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

3. Redémarrer MySQL

   sudo systemctl restart mysql
   

Sous Windows :

net stop MySQL && net start MySQL

4. Vérifier le changement

   SHOW VARIABLES LIKE 'character_set_server';
   

Modifier le jeu de caractères au niveau de la base de données

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Vérifier le changement

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

Modifier le jeu de caractères au niveau de la table

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Vérifier le changement

SHOW CREATE TABLE users;

Modifier le jeu de caractères au niveau de la colonne

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

Vérifier le changement

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

Vérification après modification et importance des sauvegardes

Pour préserver l’intégrité des données après avoir changé le jeu de caractères, suivez ces étapes.

Sauvegardez vos données

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

Revérifiez les paramètres

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

Insérez et affichez des données de test

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

Résumé

• Changement du jeu de caractères au niveau du serveur : Modifier my.cnf et définir character-set-server=utf8mb4
• Changement du jeu de caractères de la base de données : ALTER DATABASE mydatabase CHARACTER SET utf8mb4
• Changement du jeu de caractères de la table : ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4
• Changement du jeu de caractères de la colonne : ALTER TABLE users MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4
• Après les changements, vérifiez toujours les paramètres et testez vos données

5. Dépannage après modification du jeu de caractères

Après avoir changé le jeu de caractères MySQL, vous pouvez rencontrer des cas où le système ne se comporte pas correctement ou les données stockées deviennent corrompues.
Dans cette section, nous expliquons en détail les problèmes courants et comment les résoudre.

Causes du mojibake et comment le corriger

Si le mojibake apparaît après avoir changé le jeu de caractères, les causes suivantes sont courantes.

Correction n°1 : Définir correctement le jeu de caractères du client

SET NAMES utf8mb4;

Correction n°2 : Convertir correctement les données existantes

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

Remarques lors de la conversion de latin1 vers utf8mb4

Procédure sûre

1. Sauvegarder les données actuelles

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

2. Modifier le jeu de caractères de la base de données

   ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   

3. Modifier le jeu de caractères de la table

   ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   

4. Réimporter les données

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

Les données ne sont pas recherchables après la modification

Cas n°1 : La recherche LIKE ne fonctionne pas

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

Cas n°2 : L’ordre de tri a changé

SELECT * FROM users ORDER BY BINARY name;

Mesures côté application

Pour PHP

mysqli_set_charset($conn, 'utf8mb4');

Pour 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;

Modifier le jeu de caractères pour les tables existantes

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Différences entre utf8mb4_general_ci et utf8mb4_unicode_ci

✅ Si vous avez besoin d’un support multilingue ou d’un tri précis, choisissez utf8mb4_unicode_ci.

Optimisation des index

CREATE FULLTEXT INDEX idx_fulltext ON articles(content);

Résumé

• La combinaison de utf8mb4 + utf8mb4_unicode_ci est recommandée.
• Standardisez les paramètres du serveur (my.cnf) et les jeux de caractères de connexion.
• Spécifiez explicitement utf8mb4 au niveau de la base de données, de la table et de la colonne.
• Utilisez VARCHAR(191) pour éviter les limitations de longueur des clés d’index.
• Utilisez utf8mb4_unicode_ci pour des comparaisons précises.

8. FAQ

Voici des questions courantes du monde réel concernant le changement de jeux de caractères MySQL.
Nous couvrons également comment gérer les erreurs et comment choisir les meilleurs paramètres.

Quelle est la différence entre utf8 et utf8mb4 ?

SHOW VARIABLES LIKE 'character_set_server';

Le changement du jeu de caractères MySQL entraînera-t-il une perte de données ?

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

Comment corriger le mojibake s’il se produit ?

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

Quels sont les risques lors de la conversion de latin1 vers utf8mb4 ?

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Le passage à utf8mb4 affecte-t-il les performances ?

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

Lequel devrais-je utiliser : utf8mb4_general_ci ou utf8mb4_unicode_ci ?

Les requêtes deviendront-elles plus lentes après le passage à utf8mb4 ?

CREATE FULLTEXT INDEX idx_fulltext ON articles(content);

Résumé

✅ utf8mb4 est recommandé. utf8 n’est pas recommandé en raison de ses limitations.
✅ Avant d’apporter des modifications, vérifiez toujours les paramètres avec SHOW VARIABLES.
✅ Utilisez des flux de travail d’exportation/importation pour éviter le mojibake.
✅ Prenez en compte les limitations d’index et utilisez VARCHAR(191) lorsque cela est approprié.
✅ Pour les performances, ajoutez les index appropriés.

Notes finales

Modifier le jeu de caractères MySQL n’est pas simplement un ajustement de configuration—c’est une tâche critique qui peut affecter l’intégrité des données et les performances.
En suivant les bons paramètres et procédures, vous pouvez migrer vers utf8mb4 en toute sécurité et efficacité.

🔹 Suivez les étapes de cet article et configurez correctement votre jeu de caractères ! 🔹