Cette page explique comment mettre à jour le jeu de caractères et l'interclassement des bases de données Cloud SQL.
Présentation
Lorsque vous créez une base de données dans Cloud SQL pour MySQL, vous pouvez spécifier la configuration du jeu de caractères et du classement pour une base de données. Si vous ne spécifiez pas de valeurs personnalisées pour ces paramètres, la base de données utilise la valeur par défaut. Dans MySQL, vous pouvez spécifier des valeurs personnalisées pour le jeu de caractères et le classement à différents niveaux d'objet, y compris les bases de données, les tables et les colonnes. Pour en savoir plus sur l'utilisation des configurations de jeu de caractères et de classement dans MySQL, consultez Jeux de caractères, classements, Unicode.
Si vous souhaitez modifier le jeu de caractères ou le classement de votre base de données après avoir déjà créé la base de données et certains objets dans celle-ci, vous pouvez le faire en suivant la procédure décrite dans ce document.
Cette procédure comprend les étapes suivantes :
- Vérifiez les valeurs actuelles du jeu de caractères et du classement.
- Déterminez le niveau de mise à jour à effectuer.
- Effectuez une sauvegarde de l'instance.
- Générez des commandes de reconstruction pour les objets de base de données stockés.
- Mettez à jour vos bases de données, et si nécessaire, vos tables et vos colonnes.
- Validez les modifications et recherchez les erreurs.
- Recréez vos objets de base de données stockés.
- Effectuez une autre sauvegarde de l'instance.
Vérifier les valeurs actuelles du jeu de caractères et du classement
Vérifiez les valeurs existantes configurées pour le jeu de caractères et le classement de votre base de données et de vos objets de base de données. Lorsque vous vérifiez votre base de données, vous devez également vérifier la configuration de ses tables, colonnes et objets stockés. Vous pouvez utiliser les instructions suivantes pour vérifier vos bases de données et leurs objets.
Vérifier la configuration de votre instance
Pour vérifier les valeurs globales par défaut au niveau de l'instance (serveur), démarrez un shell MySQL et saisissez la commande suivante :
mysql> SELECT @@character_set_server,@@collation_server;
Exemple de résultat :
+------------------------+--------------------+
| @@character_set_server | @@collation_server |
+------------------------+--------------------+
| utf8mb4 | utf8mb4_0900_ai_ci |
+------------------------+--------------------+
1 row in set (0.00 sec)
Vous pouvez vérifier que les valeurs globales par défaut sont différentes des valeurs personnalisées que vous fournirez ultérieurement pour votre base de données.
Vérifier la configuration de votre base de données
Pour vérifier les valeurs de jeu de caractères et de classement existantes au niveau de la base de données, utilisez l'instruction suivante. Après avoir exécuté chaque instruction, dressez la liste de toutes les bases de données et de tous les objets de base de données que vous devez mettre à jour avec de nouveaux jeux de caractères ou de nouvelles valeurs de classement. La liste dépend des bases de données que vous souhaitez modifier et du nombre de tables, de colonnes et d'objets de base de données qui nécessitent des mises à jour de configuration.
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE schema_name NOT IN ('performance_schema', 'mysql', 'sys', 'information_schema');
Par exemple, vous obtenez le résultat suivant :
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| def | test1 | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | test2 | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | test3 | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | test4 | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
Toutefois, vous souhaitez remplacer les bases de données par les valeurs de jeu de caractères personnalisées suivantes :
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| def | test1 | ascii | ascii_general_ci | NULL | NO |
| def | test2 | latin1 | latin1_swedish_ci | NULL | NO |
| def | test3 | utf16 | utf16_general_ci | NULL | NO |
| def | test4 | ucs2 | ucs2_general_ci | NULL | NO |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
Dans ce cas, pour chaque base de données dont vous souhaitez modifier la valeur, vous devez mettre à jour chaque objet qui y réside.
Vérifier la configuration de vos tables
Pour vérifier les valeurs existantes des tables, utilisez l'instruction suivante :
SELECT T.table_name, T.table_schema, CCSA.character_set_name, CCSA.collation_name FROM information_schema.`tables` T, information_schema.`collation_character_set_applicability` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema NOT IN ('performance_schema', 'mysql', 'sys','information_schema');
Exemple de résultat :
+------------+--------------+--------------------+-------------------+
| TABLE_NAME | TABLE_SCHEMA | CHARACTER_SET_NAME | COLLATION_NAME |
+------------+--------------+--------------------+-------------------+
| t1 | test1 | ascii | ascii_general_ci |
| t2 | test2 | latin1 | latin1_swedish_ci |
| t3 | test3 | utf16 | utf16_general_ci |
| t4 | test4 | ucs2 | ucs2_general_ci |
+------------+--------------+--------------------+-------------------+
Vérifier la configuration des colonnes de votre tableau
Pour vérifier les valeurs des colonnes, utilisez l'instruction suivante :
SELECT table_schema, table_name, column_name, character_set_name, collation_name FROM information_schema.columns WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'mysql');
Exemple de résultat :
+--------------+------------+-------------+--------------------+------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME |
+--------------+------------+-------------+--------------------+------------------+
| test1 | t1 | c | ascii | ascii_general_ci |
| test1 | t1 | i | NULL | NULL |
| test2 | t2 | j | NULL | NULL |
| test2 | t2 | v | ascii | ascii_general_ci |
| test3 | t3 | k | NULL | NULL |
| test3 | t3 | summary | utf16 | utf16_general_ci |
| test4 | t4 | col | ucs2 | ucs2_general_ci |
| test4 | t4 | p | NULL | NULL |
+--------------+------------+-------------+--------------------+------------------+
Vérifier le schéma ou la base de données actifs
Pour vérifier les détails du schéma ou de la base de données actifs, utilisez l'instruction suivante.
SELECT TABLE_SCHEMA, TABLE_NAME, CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET, COLUMN_NAME, COLUMN_TYPE, C.CHARACTER_SET_NAME FROM information_schema.TABLES AS T JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME) JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME) WHERE TABLE_SCHEMA=SCHEMA() AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext', 'set' ) ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;
Exemple de résultat :
+------------+
| DATABASE() |
+------------+
| test3 |
+------------+
+--------------+------------+------------------+-------------+-------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | DEFAULT_CHAR_SET | COLUMN_NAME | COLUMN_TYPE | CHARACTER_SET_NAME |
+--------------+------------+------------------+-------------+-------------+--------------------+
| test3 | t3 | utf16 | summary | text | utf16 |
+--------------+------------+------------------+-------------+-------------+--------------------+
Vérifier la configuration de vos procédures stockées
Pour vérifier les valeurs des procédures stockées, utilisez l'instruction suivante :
SHOW PROCEDURE STATUS WHERE db = 'DB_NAME';
Remplacez DB_NAME par le nom de la base de données.
Exemple de résultat :
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test2 | p1 | PROCEDURE | root@% | 2024-08-09 11:47:05 | 2024-08-09 11:47:05 | DEFINER | | utf8mb4 | utf8mb4_0900_ai_ci | latin1_swedish_ci |
| test2 | p2 | PROCEDURE | root@% | 2024-08-09 11:48:36 | 2024-08-09 11:48:36 | DEFINER | | utf8mb4 | utf8mb4_0900_ai_ci | latin1_swedish_ci |
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
Pour obtenir la définition d'une procédure stockée, utilisez l'instruction suivante :
SHOW CREATE PROCEDURE DB_NAME.SP_NAME;
Remplacez les éléments suivants :
- DB_NAME : nom de la base de données MySQL
- SP_NAME : nom de la procédure stockée
Si une procédure stockée n'utilise pas le jeu de caractères ou la valeur de classement souhaités, vous devrez peut-être la supprimer et la recréer avec le nouveau jeu de caractères ou classement.
Vérifier le mappage du classement de la base de données
Dans l'exemple de résultat précédent, la procédure stockée est créée avec latin1_swedish_ci comme classement par défaut et le jeu de caractères correspondant est latin1. Cela signifie que tout le traitement s'effectue à l'aide du jeu de caractères latin1 (sauf si des paramètres spécifiques de la procédure sont associés à un jeu de caractères personnalisé). Si vous ne modifiez que le jeu de caractères et le classement au niveau de la base de données, la mise à jour au niveau de la base de données ne met pas automatiquement à jour la procédure stockée. La procédure continue de fonctionner avec le jeu de caractères latin1 et le classement correspondant.
Supprimer et recréer la procédure stockée permet de s'assurer qu'elle commence à utiliser le nouveau jeu de caractères et le nouveau classement de la base de données que vous venez de mettre à jour.
Vous pouvez déterminer le mappage entre le classement et le jeu de caractères de la base de données en interrogeant la table information_schema.COLLATION_CHARACTER_SET_APPLICABILITY.
Utilisez l'instruction suivante comme exemple :
SELECT CHARACTER_SET_NAME
FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
WHERE COLLATION_NAME = (SELECT database_collation FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = "test2" and ROUTINE_NAME = "p1");
Exemple de résultat :
+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| latin1 |
+--------------------+
Vérifier la configuration de vos déclencheurs
Pour vérifier les valeurs des déclencheurs, utilisez l'instruction suivante comme exemple.
SHOW TRIGGERS FROM DB_NAME;
Remplacez DB_NAME par le nom de la base de données MySQL.
Pour obtenir la définition d'un déclencheur, utilisez l'exemple d'instruction suivant.
SHOW CREATE trigger DB_NAME.TRIGGER_NAME;
Remplacez les éléments suivants :
- DB_NAME : nom de la base de données MySQL
- TRIGGER_NAME : nom du déclencheur
Si le déclencheur n'utilise pas le jeu de caractères ou la valeur de classement souhaités, vous devrez peut-être le supprimer et le recréer avec le nouveau jeu de caractères ou classement. Vérifiez que le déclencheur utilise le mappage de classement de base de données approprié. Pour en savoir plus sur la vérification du classement de la base de données, consultez Vérifier le mappage du classement de la base de données.
Vérifier la configuration de vos événements
Pour vérifier les valeurs des événements, utilisez l'instruction suivante comme exemple.
SHOW EVENTS FROM DB_NAME;
Remplacez DB_NAME par le nom de la base de données.
Pour obtenir la définition d'un événement, utilisez l'exemple d'instruction suivant.
SHOW CREATE EVENT DB_NAME.EVENT_NAME;
Remplacez les éléments suivants :
- DB_NAME : nom de la base de données
- EVENT_NAME : nom de l'événement
Si l'événement n'utilise pas le jeu de caractères ou la valeur de classement souhaités, vous devrez peut-être le supprimer et le recréer avec le nouveau jeu de caractères ou classement. Vérifiez que l'événement utilise le mappage de classement de base de données approprié. Pour savoir comment vérifier le classement de la base de données, consultez Vérifier le mappage du classement de la base de données.
Vérifier la configuration de vos fonctions
Pour vérifier les valeurs des fonctions, utilisez l'instruction suivante comme exemple.
SHOW FUNCTION STATUS WHERE db = 'DB_NAME';
Remplacez DB_NAME par le nom de la base de données.
Pour obtenir la définition d'une fonction, utilisez l'exemple d'instruction suivant.
SHOW CREATE FUNCTION DB_NAME.FUNCTION_NAME;
Remplacez les éléments suivants :
- DB_NAME : nom de la base de données MySQL
- FUNCTION_NAME : nom de la fonction.
Si la fonction n'utilise pas le jeu de caractères ou la valeur de classement souhaités, vous devrez peut-être la supprimer et la recréer avec le nouveau jeu de caractères ou classement. Vérifiez que la fonction utilise le mappage de classement de base de données approprié. Pour en savoir plus sur la vérification du classement de la base de données, consultez Vérifier le mappage du classement de la base de données.
Jeu de caractères ou collation Vérifiez que la fonction utilise le mappage de classement de base de données approprié. Pour en savoir plus sur la vérification du classement de la base de données, consultez Vérifier le mappage du classement de la base de données.
Déterminer le niveau de mise à jour à effectuer
Après avoir vérifié la configuration du jeu de caractères et du classement de votre base de données, vous disposez d'une liste de bases de données et éventuellement d'une liste d'objets de base de données que vous devez mettre à jour.
Le nombre de tâches à effectuer dépend du type d'objets de base de données qui nécessitent des modifications de configuration.
- Effectuez des mises à jour au niveau de la base de données. Vous n'avez besoin de mettre à jour que les métadonnées MySQL.
- Effectuez des mises à jour au niveau de la base de données et de la table. Vous n'avez besoin de mettre à jour que les métadonnées MySQL.
- Effectuez des mises à jour au niveau de la base de données, des tables et des colonnes. Vous devez mettre à jour les métadonnées, puis recréer les tables et les objets de base de données. Ces mises à jour peuvent prendre beaucoup de temps. Toutefois, vous pouvez utiliser l'un des exemples de scripts fournis pour mettre à jour toutes les tables et colonnes d'une base de données donnée.
Sauvegarder l'instance
Avant d'effectuer l'une de ces mises à jour, effectuez une sauvegarde de votre instance. Pour en savoir plus, consultez Créer une sauvegarde à la demande. Nous vous recommandons d'effectuer la sauvegarde sans aucune opération LDD en cours d'exécution sur l'instance.
Générer des commandes de reconstruction pour les objets de base de données stockés dans votre base de données
Si vous devez supprimer et recréer des objets de base de données stockés dans vos bases de données avec le nouveau jeu de caractères et le nouveau classement, utilisez l'outil mysqldump pour générer les commandes maintenant. Après avoir mis à jour les colonnes de votre base de données et de votre tableau, vous utiliserez le fichier mysqldump généré pour reconstruire les objets de base de données stockés dans votre base de données. Cette commande génère des instructions pour toutes les procédures stockées, fonctions, déclencheurs et événements.
Exécutez la commande suivante pour pouvoir utiliser la sortie afin de recréer ultérieurement toutes les procédures stockées, fonctions et événements d'une base de données spécifique (lorsque vous recréerez les objets de base de données stockés).
$ mysqldump -uDBAuser -p -h IP_ADDRESS \ -P 3306 DB_NAME --no-data --no-create-db \ --no-create-info --routines --triggers --events \ --set-gtid-purged=OFF --events > dump_objects.sql
Remplacez les éléments suivants :
- IP_ADDRESS : adresse IP de l'instance Cloud SQL pour MySQL
- DB_NAME : nom de la base de données MySQL
Mettez à jour vos bases de données, et si nécessaire, vos tables et vos colonnes.
Dans cette étape, vous allez mettre à jour les valeurs du jeu de caractères et du classement de vos bases de données. Vous devrez peut-être également reconstruire vos tables et vos colonnes.
Effectuer des mises à jour au niveau de la base de données
Si vous n'effectuez que des mises à jour au niveau de la base de données, exécutez l'instruction suivante pour mettre à jour votre base de données :
ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Remplacez les éléments suivants :
- DB_NAME : nom de la base de données à mettre à jour
- NEW_CHARSET : nouveau jeu de caractères à appliquer à la base de données
- NEW_COLLATION : nouvelle collation à appliquer à la base de données
Effectuer des mises à jour au niveau de la base de données et des tables
Si vous effectuez des mises à jour au niveau de la base de données et de la table, procédez comme suit :
Exécutez l'instruction suivante pour mettre à jour votre base de données :
ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Remplacez les éléments suivants :
- DB_NAME : nom de la base de données à mettre à jour
- NEW_CHARSET : nouveau jeu de caractères à appliquer à la base de données
- NEW_COLLATION : nouvelle collation à appliquer à la base de données
Pour chaque table que vous souhaitez mettre à jour, exécutez l'instruction suivante :
ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Remplacez les éléments suivants :
- TABLE_NAME : nom de la table à mettre à jour
- NEW_CHARSET : nouveau jeu de caractères à appliquer à la table
- NEW_COLLATION : nouvelle collation à appliquer à la table
Effectuer des mises à jour au niveau des bases de données, des tables et des colonnes
À ce niveau, vous devez réencoder les données existantes pour qu'elles soient conformes aux nouveaux paramètres de jeu de caractères et de collation. Si vous effectuez des mises à jour au niveau de la base de données, de la table et de la colonne, procédez comme suit :
Avant de modifier les colonnes du tableau, tenez compte des points suivants :
- La conversion du jeu de caractères et du classement
ALTER TABLEentraîne la reconstruction et le verrouillage de la table. Par conséquent, toute requête active qui tente d'accéder à la table est bloquée. - La conversion du jeu de caractères et du classement
ALTER TABLEprend du temps, car elle récupère la table complète en mémoire. Cette opération peut augmenter la latence de toute charge de travail exécutée en parallèle, car les pages du pool de mémoire tampon sont utilisées pour la table mise à jour. - Si des requêtes accèdent à plusieurs tables et que certaines d'entre elles sont en cours de mise à jour tandis que d'autres ne le sont pas encore, les requêtes peuvent produire des résultats incohérents pendant cette période de transaction.
- L'exécution d'une conversion de jeu de caractères et de classement
ALTER TABLEcrée une table fantôme. Planifiez donc l'utilisation du disque en conséquence. - La conversion du jeu de caractères et du classement
ALTER TABLEentraîne également la reconstruction de tous les index de la table. - Vous n'avez pas besoin de mettre à jour les instances répliquées séparément. Les commandes
ALTER,DROPetCREATEsont répliquées automatiquement. Toutefois, l'exécution de commandes LDD peut entraîner une augmentation du temps de latence de la réplique pendant l'opération. - Si vous restaurez l'une des bases de données à partir d'une sauvegarde effectuée avant l'application de ces mises à jour, vous devrez les appliquer à nouveau.
- La conversion du jeu de caractères et du classement
Vérifiez si votre tableau contient des tuples de données qui pourraient être incompatibles avec le nouvel ensemble de caractères. S'il y en a, vous devez les corriger avant de mettre à jour le jeu de caractères. Sinon, vous rencontrerez des erreurs lors du processus de conversion. Vous pouvez utiliser l'instruction
SELECT CONVERTsuivante pour valider vos données avec le nouvel ensemble de caractères.SELECT COLUMN_NAME,CONVERT(COLUMN_NAME USING NEW_CHARSET) FROM TABLE_NAME WHERE COLUMN_NAME != CONVERT(COLUMN_NAME USING NEW_CHARSET);
SELECT c,CONVERT(c USING 'ascii') FROM t WHERE c != CONVERT(c USING ascii);Exemple de résultat :
+------+--------------------------+ | c | convert(c using 'ascii') | +------+--------------------------+ | é | ? | | é | ? | | £ | ? | +------+--------------------------+Dans cet exemple, la sortie renvoie ces trois tuples. Il existe donc trois valeurs incohérentes qui doivent être corrigées. Avant de passer à l'étape suivante, examinez le résultat de votre requête et corrigez les données jusqu'à ce que votre requête renvoie zéro tuple pour toutes les colonnes qui doivent être converties. Vous pouvez également combiner plusieurs colonnes dans la même requête
SELECT.Choisissez une option pour mettre à jour le jeu de caractères et le classement de vos tables au niveau des colonnes.
Si vous souhaitez mettre à jour le jeu de caractères pour une colonne de table très spécifique, suivez les étapes de l'onglet "Niveau de la colonne". Vous n'aurez qu'à réencoder les colonnes sélectionnées. Sinon, suivez les étapes de l'onglet "Au niveau de la table" pour réencoder la table complète.
Au niveau des tables
Exécutez l'instruction suivante pour mettre à jour votre base de données :
ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Remplacez les éléments suivants :
- DB_NAME : nom de la base de données à mettre à jour
- NEW_CHARSET : nouveau jeu de caractères à appliquer à la base de données
- NEW_COLLATION : nouvelle collation à appliquer à la base de données
Pour chaque table concernée, exécutez l'instruction suivante pour la mettre à jour :
ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Remplacez les éléments suivants :
- TABLE_NAME : nom de la table à mettre à jour
- NEW_CHARSET : nouveau jeu de caractères à appliquer à la table
- NEW_COLLATION : nouvelle collation à appliquer à la table
Mettez à jour au niveau de la table afin que toutes les colonnes qui en font partie soient mises à jour avec le nouvel ensemble de caractères et le nouveau classement. Cette option reconstruit complètement le tableau. Pour utiliser cette option, exécutez l'instruction suivante :
ALTER TABLE DB_NAME.TABLE_NAME CONVERT TO CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Remplacez les éléments suivants :
- TABLE_NAME : nom de la table à mettre à jour
- NEW_CHARSET : nouveau jeu de caractères à appliquer à la table
- NEW_COLLATION : nouvelle collation à appliquer à la table
Au niveau des colonnes
Mettez à jour les données au niveau des colonnes. Cette option reconstruit également complètement la table. Si plusieurs colonnes sont mises à jour, cette option peut entraîner plusieurs reconstructions.
Pour utiliser cette option, exécutez l'instruction suivante :
- Exécutez l'instruction suivante pour mettre à jour votre base de données :
- DB_NAME : nom de la base de données à mettre à jour
- NEW_CHARSET : nouveau jeu de caractères à appliquer à la base de données
- NEW_COLLATION : nouvelle collation à appliquer à la base de données
- Pour chaque table concernée, exécutez l'instruction suivante pour la mettre à jour :
- TABLE_NAME : nom de la table à mettre à jour
- NEW_CHARSET : nouveau jeu de caractères à appliquer à la table
- NEW_COLLATION : nouvelle collation à appliquer à la table
- Exécutez l'instruction suivante pour effectuer une mise à jour au niveau de la colonne :
- DB_NAME : nom de la base de données à mettre à jour
- TABLE_NAME : nom de la table à mettre à jour
- COLUMN_DEFINITION : spécifiez une définition exacte de colonne qui inclut des contraintes. La définition de colonne peut contenir les anciennes valeurs du jeu de caractères et du classement. Vous devez mettre à jour la définition de la colonne avec les nouvelles valeurs.
- NEW_CHARSET : nouveau jeu de caractères à appliquer à la colonne de la table
- NEW_COLLATION : nouvelle collation à appliquer à la colonne de la table
ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Remplacez les éléments suivants :
ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Remplacez les éléments suivants :
ALTER TABLE DB_NAME.TABLE_NAME MODIFY COLUMN_DEFINITION CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
Remplacez les éléments suivants :
Répétez cette instruction pour toutes les colonnes de chaîne présentes dans la table qui nécessitent une mise à jour du jeu de caractères ou du classement.
Valider les modifications et rechercher les erreurs
Lors de l'opération ALTER TABLE, recherchez les erreurs indiquant que les données existantes dans la table ne sont pas compatibles avec le nouveau jeu de caractères. Exemple :
ERROR 1366 (HY000): Incorrect string value: '\xC3\xA9' for column 'c' at row 1
Si vous rencontrez des erreurs de compatibilité des données, nous vous recommandons de vérifier à nouveau vos tables pour détecter d'éventuelles erreurs de compatibilité des données et de les corriger avant de réexécuter les instructions ALTER TABLE pour réencoder les données.
De plus, lors de l'opération ALTER TABLE, il est possible qu'une vérification de clé étrangère échoue pendant que la colonne est réencodée. Dans ce cas, vous pouvez désactiver la vérification des clés étrangères (SET FOREIGN_KEY_CHECKS=0;) et exécuter à nouveau la mise à jour.
Recréer vos objets de base de données stockés
Une fois votre base de données mise à jour, vous pouvez utiliser le fichier mysqldump généré pour recréer les objets de base de données stockés avec les valeurs de jeu de caractères et de classement.
Pour recréer vos objets de base de données stockés, procédez comme suit :
- Vérifiez que les objets de base de données stockés sont présents dans la base de données.
- Assurez-vous qu'aucune charge parallèle n'attend la présence des objets de base de données stockés pendant cette période de suppression et de recréation.
Le fichier
mysqldumpque vous avez généré avec l'outilmysqldumpn'inclut pas l'instruction permettant de supprimer les déclencheurs. Si vous devez supprimer des déclencheurs, ouvrez un terminal MySQL pour générer l'instructionDROPpour les déclencheurs.mysql> SELECT CONCAT('DROP TRIGGER ', TRIGGER_NAME, ';') AS drop_trigger_statements FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = DB_NAME;
Copiez le résultat.
Exécutez l'instruction pour supprimer les déclencheurs de votre base de données, soit dans un shell MySQL, soit dans un script.
Après avoir supprimé les déclencheurs, appliquez le fichier de dump que vous avez généré avec l'outil
mysqldump. Pour appliquer le fichier de dump et recréer les objets, saisissez la commande suivante :$ mysql -uDBAuser -p -h IP_ADDRESS \ -P 3306 DB_NAME < dump_objects.sql
Remplacez les éléments suivants :
- IP_ADDRESS : adresse IP de l'instance Cloud SQL pour MySQL
- DB_NAME : nom de la base de données
Effectuer une autre sauvegarde de l'instance
Une fois les mises à jour effectuées, effectuez une autre sauvegarde de votre instance. Pour en savoir plus, consultez Créer une sauvegarde à la demande.
Exemples de scripts
Si vous devez mettre à jour des bases de données, des tables et des colonnes, cette section fournit des exemples de scripts pour vous aider à réencoder vos données.
Script permettant de mettre à jour plusieurs tables
Le script suivant génère une instruction pour toutes les tables de la base de données donnée. Cette instruction vous permet de réencoder les données existantes pour qu'elles soient conformes aux nouveaux paramètres de jeu de caractères et de classement.
SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION; ", "ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION; ") AS alter_sql FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_TYPE='BASE TABLE';
Remplacez les éléments suivants :
- DB_NAME : nom de la base de données à mettre à jour
- NEW_CHARSET : nouveau jeu de caractères à appliquer à la table
- NEW_COLLATION : nouvelle collation à appliquer à la table
Exemple de résultat :
+--------------------------------------------------------------------------------------------+
|alter_sql
+--------------------------------------------------------------------------------------------+
| ALTER TABLE test1.t1 CHARACTER SET <new-charset> COLLATE <new-collation>;
ALTER TABLE test1.t1 CONVERT TO CHARACTER SET <new-charset> COLLATE <new-collation>; |
+--------------------------------------------------------------------------------------------+
Script permettant de mettre à jour plusieurs colonnes dans un tableau
Le script suivant génère une instruction pour toutes les colonnes d'une table donnée. Cette instruction vous permet de réencoder les données existantes pour qu'elles soient conformes aux nouveaux paramètres de jeu de caractères et de classement.
Générer des instructions
ALTER TABLEpour toutes les tables de la base de donnéesSELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION; ") AS alter_table_statements FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_TYPE='BASE TABLE';
Remplacez les éléments suivants :
- DB_NAME : nom de la base de données à mettre à jour
- NEW_CHARSET : nouveau jeu de caractères à appliquer à la table
- NEW_COLLATION : nouvelle collation à appliquer à la table
Exemple de résultat :
+------------------------------------------------------------------------------+ | alter_table_statements | +------------------------------------------------------------------------------+ | ALTER TABLE test1.t1 CHARACTER SET <new-charset> COLLATE <new-collation>; | +------------------------------------------------------------------------------+Pour chaque tableau, générez des commandes
ALTER TABLE MODIFY.SELECT CONCAT( "ALTER TABLE ", table_name, " MODIFY ", column_name, 'COLUMN_DEFINITION' , " CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION ") AS alter_table_column_statements FROM information_schema.columns WHERE table_schema = 'DB_NAME' AND table_name = 'TABLE_NAME' AND data_type IN ('char', 'varchar', 'tinytext', 'text', 'mediumtext', 'longtext','set','enum');
Remplacez les éléments suivants :
- DB_NAME : nom de la base de données à mettre à jour
- TABLE_NAME : nom de la table à mettre à jour
- COLUMN_DEFINITION : après avoir obtenu les résultats de chaque requête, remplacez-le par la définition de colonne pour chaque colonne (les définitions de colonne peuvent différer d'une colonne à l'autre).
- NEW_CHARSET : nouveau jeu de caractères à appliquer à la colonne de la table
- NEW_COLLATION : nouvelle collation à appliquer à la colonne de la table
Exemple de résultat :
+-------------------------------------------------------------------------------------------------------------------------------------+ |alter_table_column_statements | +-------------------------------------------------------------------------------------------------------------------------------------+ | ALTER TABLE t1 MODIFY c <column-definition-replaced-after-obtaining-result-set> CHARACTER SET <new-charset> COLLATE <new-collation> | +-------------------------------------------------------------------------------------------------------------------------------------+