Cette page explique comment mettre à jour le jeu de caractères et la collation 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 de la collation. 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'objets, y compris pour 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 Jeu 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 dedans, 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 mises à jour et recherchez les erreurs.
- Récré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 ses objets. 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 de base de données.
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 pour votre base de données ultérieurement.
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 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 nécessitant 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é 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 une 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 active, 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 que vous avez prévus, vous devrez peut-être la supprimer et la recréer avec le nouveau jeu de caractères ou le nouveau 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 collation par défaut et l'ensemble de caractères correspondant est latin1. Cela signifie que tout le traitement est effectué à l'aide du jeu de caractères latin1 (sauf si des paramètres spécifiques de la procédure sont tagués avec 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 respectif.
Supprimer et recréer la procédure stockée garantit que la procédure commence à utiliser le nouveau jeu de caractères et la nouvelle collation de la base de données que vous venez de mettre à jour.
Vous pouvez déterminer la mise en correspondance entre la collation de la base de données et le jeu de caractères 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 que vous avez prévus, vous devrez peut-être le supprimer et le recréer avec le nouveau jeu de caractères ou le nouveau classement. Vérifiez que le déclencheur utilise le mappage de collation de base de données approprié. Pour en savoir plus sur la vérification du classement de la base de données, consultez la section 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 que vous avez prévus, vous devrez peut-être le supprimer et le recréer avec le nouveau jeu de caractères ou le nouveau classement. Vérifiez que l'événement utilise le mappage de collation de base de données approprié. Pour en savoir plus sur la vérification du classement de la base de données, consultez la section 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 que vous avez prévus, vous devrez peut-être la supprimer et la recréer avec le nouveau jeu de caractères ou le nouveau classement. Vérifiez que la fonction utilise le mappage de mise en forme de la base de données approprié. Pour en savoir plus sur la vérification du classement de la base de données, consultez la section Vérifier le mappage du classement de la base de données.
Jeu de caractères ou classement Vérifiez que la fonction utilise le mappage de mise en forme de la base de données approprié. Pour en savoir plus sur la vérification du classement de la base de données, consultez la section Vérifier le mappage du classement de la base de données.
Déterminer le niveau de mises à 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 que vous devrez effectuer dépend du type d'objets de base de données nécessitant des modifications de configuration.
- Effectuer des mises à jour au niveau de la base de données Vous devez mettre à jour les métadonnées MySQL uniquement.
- Effectuez des mises à jour au niveau de la base de données et de la table. Vous devez mettre à jour les métadonnées MySQL uniquement.
- Effectuez des mises à jour au niveau de la base de données, de la table et des colonnes. Vous devez mettre à jour les métadonnées et recréer les tables et les objets de base de données. Ces mises à jour peuvent prendre le plus 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.
Effectuer une sauvegarde de l'instance
Avant d'effectuer l'une de ces mises à jour, effectuez une sauvegarde de votre instance. Pour en savoir plus, consultez la section Créer une sauvegarde à la demande. Nous vous recommandons de créer 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 dès maintenant. Après avoir mis à jour votre base de données et les colonnes de votre table, 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 afin de pouvoir utiliser la sortie pour recréer ultérieurement toutes les procédures, fonctions et événements stockés d'une base de données spécifique (lorsque vous recréez des 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
Effectuez des mises à jour de vos bases de données, et si nécessaire, de vos tables et de vos colonnes.
Dans cette étape, vous mettez à jour les valeurs du jeu de caractères et du classement de vos bases de données. Vous devrez peut-être également recréer vos tables et vos colonnes.
Effectuer des mises à jour au niveau de la base de données
Si vous effectuez uniquement 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 de la base de données, de la table et des colonnes
À ce niveau, vous devez réencoder les données existantes pour qu'elles respectent les nouveaux paramètres de jeu de caractères et de mise en forme. 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 mettre à jour les colonnes du tableau, consultez les points suivants:
- La conversion du jeu de caractères et du classement
ALTER TABLEentraîne la recompilation 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 extrait 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 tampons sont utilisées pour la table mise à jour. - Si des requêtes accèdent à plusieurs tables et que certaines d'entre elles sont mises à jour alors que d'autres ne le sont pas encore, les requêtes peuvent produire des résultats incohérents pendant cette transaction.
- L'exécution d'une conversion de jeu de caractères et de collation
ALTER TABLEcrée une table d'ombre. Planifiez donc l'utilisation du disque en conséquence. - La conversion du jeu de caractères et du classement
ALTER TABLEentraîne également la recompilation de tous les index de la table. - Vous n'avez pas besoin de mettre à jour les instances de réplication séparément. Les commandes
ALTER,DROPetCREATEsont répliquées automatiquement. Toutefois, l'exécution de commandes LDD peut entraîner une augmentation du délai de réplication pendant l'opération. - Si vous restaurez l'une des bases de données à partir d'une sauvegarde effectuée avant d'appliquer ces mises à jour, vous devrez les réappliquer.
- La conversion du jeu de caractères et du classement
Recherchez dans votre table les tupels de données susceptibles d'être incompatibles avec le nouveau jeu de caractères. En cas d'incompatibilité, vous devez les corriger avant de mettre à jour l'ensemble de caractères. Sinon, vous rencontrerez des erreurs au cours du processus de conversion. Vous pouvez utiliser l'instruction
SELECT CONVERTsuivante pour valider vos données avec un nouveau jeu 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, comme la sortie renvoie ces trois tuplets, trois valeurs incohérentes doivent être corrigées. Avant de passer à l'étape suivante, examinez la sortie de votre requête et corrigez les données jusqu'à ce que votre requête renvoie zéro tuple pour toutes les colonnes nécessitant une conversion. 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 de la colonne.
Si vous souhaitez mettre à jour l'ensemble de caractères d'une colonne de table très spécifique, suivez les étapes de l'onglet "Au niveau de la colonne", où vous n'avez qu'à réencoder les colonnes sélectionnées. Sinon, suivez la procédure de l'onglet "Au niveau de la table", qui réencode 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
Effectuez la mise à jour au niveau de la table afin que toutes les colonnes qui en font partie soient mises à jour avec le nouveau jeu de caractères et la nouvelle collation. 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 au niveau de la colonne. Cette option reconstruit également complètement le tableau. Si plusieurs colonnes sont mises à jour, cette option peut entraîner plusieurs recompilations.
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 la 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 de colonne exacte qui inclut des contraintes. La définition de la 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 mises à jour et rechercher des erreurs
Lors de l'opération ALTER TABLE, recherchez les erreurs indiquant que les données existantes de 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 et de les corriger avant de réexécuter les instructions ALTER TABLE pour réencoder les données.
En outre, lors de l'opération ALTER TABLE, les tables (la table de clé étrangère et la table référencée) peuvent échouer lors de la vérification de la clé étrangère pendant le réencodage de la colonne. 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 ne s'attend à ce que les objets de base de données stockés soient présents pendant cette suppression et cette 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 la sortie.
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 nouvelle sauvegarde de votre instance. Pour en savoir plus, consultez la section 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 pour mettre à jour plusieurs tables
Le script suivant génère une instruction pour toutes les tables de la base de données donnée. L'instruction vous permet de réencoder les données existantes pour qu'elles respectent le nouveau jeu de caractères et les nouveaux paramètres 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 pour mettre à jour plusieurs colonnes d'un tableau
Le script suivant génère une instruction pour toutes les colonnes d'une table donnée. L'instruction vous permet de réencoder les données existantes pour qu'elles respectent le nouveau jeu de caractères et les nouveaux paramètres 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 table, 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-les par la définition de la colonne pour chaque colonne (les définitions de colonne peuvent varier 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> | +-------------------------------------------------------------------------------------------------------------------------------------+