Aggiornare il set di caratteri e le regole di confronto per un database

MySQL   |  PostgreSQL   |  SQL Server

Questa pagina descrive come aggiornare il set di caratteri e le regole di confronto dei database Cloud SQL.

Panoramica

Quando crei un database in Cloud SQL per MySQL, puoi specificare la configurazione di set di caratteri e regole di confronto per un database. Se non specifichi valori personalizzati per queste impostazioni, il database utilizza il valore predefinito. In MySQL, puoi specificare valori personalizzati per il set di caratteri e le regole di confronto a diversi livelli di oggetto, inclusi database, tabelle e colonne. Per ulteriori informazioni sull'utilizzo delle configurazioni di set di caratteri e regole di confronto in MySQL, consulta Set di caratteri, regole di confronto, Unicode.

Se vuoi modificare il set di caratteri o le regole di confronto del database dopo aver già creato il database e alcuni oggetti al suo interno, puoi farlo seguendo la procedura descritta in questo documento.

Questa procedura prevede i seguenti passaggi:

1. Controlla i valori attuali di set di caratteri e regole di confronto.
2. Determina il livello di aggiornamento da eseguire.
3. Esegui un backup dell'istanza.
4. Genera comandi di ricompilazione per gli oggetti di database archiviati.
5. Esegui aggiornamenti ai tuoi database e, se necessario, a tabelle e colonne.
6. Convalida gli aggiornamenti e cerca errori.
7. Ricrea gli oggetti di database archiviati.
8. Esegui un altro backup dell'istanza.

Controlla i valori attuali di set di caratteri e regole di confronto

Controlla i valori esistenti configurati per il set di caratteri e le regole di confronto per il database e gli oggetti di database. Quando controlli il database, devi anche controllare la configurazione delle tabelle, delle colonne e degli oggetti archiviati. Puoi utilizzare le seguenti istruzioni per controllare i tuoi database e i relativi oggetti.

Controllare la configurazione dell'istanza

Per controllare i valori globali predefiniti a livello di istanza (server), avvia una shell MySQL e inserisci il seguente comando:

mysql> SELECT @@character_set_server,@@collation_server;

Output di esempio:

+------------------------+--------------------+
| @@character_set_server | @@collation_server |
+------------------------+--------------------+
| utf8mb4                | utf8mb4_0900_ai_ci |
+------------------------+--------------------+
1 row in set (0.00 sec)

Puoi verificare che i valori globali predefiniti siano diversi da quelli personalizzati che fornirai in un secondo momento per il tuo database.

Controllare la configurazione del database

Per controllare i valori esistenti di set di caratteri e regole di confronto a livello di database, utilizza la seguente istruzione. Dopo aver eseguito ogni istruzione, crea un elenco di tutti i database e gli oggetti di database che devi aggiornare con nuovi set di caratteri o valori di regole di confronto. L'elenco dipende dai database che vuoi modificare e dal numero di tabelle, colonne e oggetti di database che richiedono aggiornamenti della configurazione.

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE schema_name
NOT IN ('performance_schema', 'mysql', 'sys', 'information_schema');

Ad esempio, ricevi il seguente output:

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

Tuttavia, vuoi modificare i database con i seguenti valori del set di caratteri personalizzati:

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

Quindi, in questo caso, per ogni database con un valore che vuoi modificare, devi aggiornare ogni oggetto che si trova nel database.

Controllare la configurazione delle tabelle

Per controllare i valori esistenti per le tabelle, utilizza la seguente istruzione:

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');

Output di esempio:

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

Controllare la configurazione delle colonne della tabella

Per controllare i valori delle colonne, utilizza la seguente istruzione:

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');

Output di esempio:

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

Nota :la configurazione del set di caratteri e delle regole di confronto si applica solo alle colonne basate su stringhe (ad esempio char, varchar, enum, text, mediumtext, longtext e set). Per tutti gli altri tipi di colonne, la configurazione del set di caratteri e delle regole di confronto viene visualizzata come NULL.

Controllare lo schema o il database attivo

Per controllare i dettagli dello schema o del database attivo, utilizza la seguente istruzione.

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;

Output di esempio:

+------------+
| DATABASE() |
+------------+
| test3      |
+------------+
+--------------+------------+------------------+-------------+-------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | DEFAULT_CHAR_SET | COLUMN_NAME | COLUMN_TYPE | CHARACTER_SET_NAME |
+--------------+------------+------------------+-------------+-------------+--------------------+
| test3        | t3         | utf16            | summary     | text        | utf16              |
+--------------+------------+------------------+-------------+-------------+--------------------+

Nota: l'output include solo la configurazione del set di caratteri e delle regole di confronto per le colonne basate su stringhe applicabili per il database attivo corrente.

Controlla la configurazione delle stored procedure

Per controllare i valori delle stored procedure, utilizza la seguente istruzione:

SHOW PROCEDURE STATUS WHERE db = 'DB_NAME';

Sostituisci DB_NAME con il nome del database.

Output di esempio:

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

Per ottenere la definizione di una stored procedure, utilizza la seguente istruzione:

SHOW CREATE PROCEDURE DB_NAME.SP_NAME;

Sostituisci quanto segue:

• DB_NAME: il nome del database MySQL
• SP_NAME: il nome della stored procedure

Se una stored procedure non utilizza il set di caratteri o il valore di confronto che intendi utilizzare, potrebbe essere necessario eliminarla e ricrearla con il nuovo set di caratteri o confronto.

Controllare la mappatura delle regole di confronto del database

Nell'output dell'esempio precedente, la stored procedure viene creata con latin1_swedish_ci come regole di confronto predefinite e il set di caratteri corrispondente è latin1. Ciò significa che tutta l'elaborazione avviene utilizzando il set di caratteri latin1 (a meno che parametri specifici della procedura non siano taggati con un set di caratteri personalizzato). Se modifichi solo il set di caratteri e le regole di confronto a livello di database, l'aggiornamento a livello di database non aggiorna automaticamente la stored procedure. La procedura continua a funzionare con il set di caratteri latin1 e le regole di confronto corrispondenti. L'eliminazione e la ricreazione della stored procedure garantiscono che la procedura inizi a utilizzare il nuovo set di caratteri e la nuova collation del database appena aggiornato.

Puoi scoprire la mappatura tra le regole di confronto del database e il set di caratteri eseguendo una query sulla tabella information_schema.COLLATION_CHARACTER_SET_APPLICABILITY.

Utilizza la seguente dichiarazione come esempio:

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");

Output di esempio:

+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| latin1             |
+--------------------+

Controllare la configurazione dei trigger

Per controllare i valori dei trigger, utilizza la seguente istruzione come esempio.

SHOW TRIGGERS FROM DB_NAME;

Sostituisci DB_NAME con il nome del database MySQL.

Per ottenere la definizione di un trigger, utilizza la seguente istruzione di esempio.

SHOW CREATE trigger DB_NAME.TRIGGER_NAME;

Sostituisci quanto segue:

• DB_NAME: il nome del database MySQL
• TRIGGER_NAME: il nome del trigger

Se l'attivatore non utilizza il set di caratteri o il valore di confronto che intendi usare, potresti doverlo eliminare e ricreare con il nuovo set di caratteri o il nuovo confronto. Verifica che il trigger utilizzi il mapping delle regole di confronto del database corretto. Per ulteriori informazioni sul controllo delle regole di confronto del database, vedi Controllare la mappatura delle regole di confronto del database.

Controllare la configurazione degli eventi

Per controllare i valori degli eventi, utilizza la seguente istruzione come esempio.

SHOW EVENTS FROM DB_NAME;

Sostituisci DB_NAME con il nome del database.

Per ottenere la definizione di un evento, utilizza la seguente istruzione di esempio.

SHOW CREATE EVENT DB_NAME.EVENT_NAME;

Sostituisci quanto segue:

• DB_NAME: il nome del database
• EVENT_NAME: il nome dell'evento

Se l'evento non utilizza il set di caratteri o il valore di confronto che intendi utilizzare, potrebbe essere necessario eliminarlo e ricrearlo con il nuovo set di caratteri o confronto. Verifica che l'evento utilizzi il mapping di confronto del database corretto. Per ulteriori informazioni sul controllo delle regole di confronto del database, vedi Controllare la mappatura delle regole di confronto del database.

Attenzione: la ricreazione degli eventi può causare la mancata esecuzione degli eventi programmati.

Controllare la configurazione delle funzioni

Per controllare i valori delle funzioni, utilizza la seguente istruzione come esempio.

SHOW FUNCTION STATUS WHERE db = 'DB_NAME';

Sostituisci DB_NAME con il nome del database.

Per ottenere la definizione di una funzione, utilizza la seguente istruzione di esempio.

SHOW CREATE FUNCTION DB_NAME.FUNCTION_NAME;

Sostituisci quanto segue:

• DB_NAME: il nome del database MySQL
• FUNCTION_NAME: il nome della funzione

Se la funzione non utilizza il set di caratteri o il valore di confronto che intendi utilizzare, potresti doverla eliminare e ricreare con il nuovo set di caratteri o confronto. Verifica che la funzione utilizzi il mapping delle regole di confronto del database corretto. Per saperne di più sul controllo delle regole di confronto del database, vedi Controllare la mappatura delle regole di confronto del database.

I character set o le regole di confronto. Verifica che la funzione utilizzi il mapping delle regole di confronto del database corretto. Per saperne di più sul controllo delle regole di confronto del database, vedi Controllare la mappatura delle regole di confronto del database.

Determina il livello di aggiornamenti da eseguire

Dopo aver controllato la configurazione del set di caratteri e delle regole di confronto del database, avrai un elenco di database e, possibilmente, un elenco di oggetti di database che devi aggiornare.

Il numero di attività da eseguire dipende dal tipo di oggetti di database che richiedono modifiche alla configurazione.

• Esegui aggiornamenti a livello di database. Devi aggiornare solo i metadati MySQL.
• Esegui aggiornamenti a livello di database e tabella. Devi aggiornare solo i metadati MySQL.
• Esegui aggiornamenti a livello di database, tabella e colonna. Devi aggiornare i metadati e ricompilare le tabelle e gli oggetti di database. Questi aggiornamenti possono richiedere più tempo; tuttavia, puoi utilizzare uno degli script di esempio forniti per aggiornare tutte le tabelle e le colonne di un determinato database.

Esegui un backup dell'istanza

Importante: NON saltare questo passaggio.

Prima di eseguire uno di questi aggiornamenti, esegui un backup della tua istanza. Per ulteriori informazioni, vedi Creare un backup on demand. Ti consigliamo di eseguire il backup senza operazioni DDL in esecuzione sull'istanza.

Genera comandi di ricompilazione per gli oggetti di database archiviati nel database

Se devi eliminare e ricreare gli oggetti di database archiviati nei tuoi database con il nuovo set di caratteri e regole di confronto, utilizza lo strumento mysqldump per generare i comandi ora. Dopo aver aggiornato le colonne del database e della tabella, utilizzerai il file mysqldump generato per ricompilare gli oggetti del database archiviati nel tuo database. Questo comando genera istruzioni per tutte le stored procedure, funzioni, trigger ed eventi.

Esegui il comando seguente per poter utilizzare l'output per ricreare tutte le procedure, le funzioni e gli eventi archiviati di un determinato database in un secondo momento (quando ricrei gli oggetti di database archiviati).

$ 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

Sostituisci quanto segue:

• IP_ADDRESS: indirizzo IP dell'istanza Cloud SQL per MySQL
• DB_NAME: il nome del database MySQL

Esegui aggiornamenti ai tuoi database e, se necessario, a tabelle e colonne.

In questo passaggio, aggiorna i valori del set di caratteri e delle regole di confronto dei tuoi database. Inoltre, potresti dover ricreare le tabelle e le colonne.

Eseguire aggiornamenti a livello di database

Se esegui solo aggiornamenti a livello di database, esegui la seguente istruzione per aggiornare il database:

ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

Sostituisci quanto segue:

• DB_NAME: il nome del database da aggiornare
• NEW_CHARSET: il nuovo set di caratteri da applicare al database
• NEW_COLLATION: la nuova collation da applicare al database

Esegui aggiornamenti a livello di database e tabella

Se esegui aggiornamenti a livello di database e tabella, procedi nel seguente modo:

1. Esegui la seguente istruzione per aggiornare il database:

   ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

   Sostituisci quanto segue:

   • DB_NAME: il nome del database da aggiornare
   • NEW_CHARSET: il nuovo set di caratteri da applicare al database
   • NEW_COLLATION: la nuova collation da applicare al database

2. Per ogni tabella da aggiornare, esegui la seguente istruzione:

   ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

   Sostituisci quanto segue:

   • TABLE_NAME: il nome della tabella da aggiornare
   • NEW_CHARSET: il nuovo set di caratteri da applicare alla tabella
   • NEW_COLLATION: la nuova collation da applicare alla tabella

Eseguire aggiornamenti a livello di database, tabella e colonna

A questo livello, devi ricodificare i dati esistenti per rispettare le nuove impostazioni di set di caratteri e regole di confronto. Se esegui aggiornamenti a livello di database, tabella e colonna, procedi nel seguente modo:

1. Prima di procedere con gli aggiornamenti alle colonne della tabella, esamina le seguenti considerazioni:

   • La conversione del set di caratteri e delle regole di confronto ALTER TABLE causa la ricostruzione della tabella e la blocca. Di conseguenza, qualsiasi query attiva che tenta di accedere alla tabella viene bloccata.
   • La conversione del set di caratteri e delle regole di confronto ALTER TABLE richiede tempo perché recupera la tabella completa in memoria. Questa operazione potrebbe aumentare la latenza di qualsiasi workload in esecuzione in parallelo perché le pagine del buffer pool vengono utilizzate per la tabella aggiornata.
   • Se ci sono query che accedono a più tabelle e alcune tabelle vengono aggiornate mentre altre non sono ancora aggiornate, le query potrebbero produrre risultati incoerenti durante il periodo di questa transazione.
   • La procedura di esecuzione di una conversione di set di caratteri e regole di confronto di ALTER TABLE crea una tabella shadow, quindi pianifica l'utilizzo del disco di conseguenza.
   • La conversione del set di caratteri e delle regole di confronto ALTER TABLE comporta anche la ricompilazione di tutti gli indici della tabella.
   • Non è necessario aggiornare le istanze di replica separatamente. I comandi ALTER, DROP e CREATE vengono replicati automaticamente. Tuttavia, l'esecuzione di comandi DDL può causare un aumento del tempo di ritardo della replica durante l'operazione.
   • Se ripristini uno dei database da un backup eseguito prima di applicare questi aggiornamenti, dovrai applicarli di nuovo.

2. Controlla la tabella per verificare la presenza di tuple di dati che potrebbero non essere compatibili con il nuovo set di caratteri. Se sono presenti incompatibilità, devi correggerle prima di aggiornare il set di caratteri. In caso contrario, si verificheranno errori durante il processo di conversione. Puoi utilizzare la seguente istruzione SELECT CONVERT per convalidare i dati con il nuovo set di caratteri.

   SELECT COLUMN_NAME,CONVERT(COLUMN_NAME USING NEW_CHARSET)
   FROM TABLE_NAME
   WHERE COLUMN_NAME != CONVERT(COLUMN_NAME USING NEW_CHARSET);

   Nota :se hai un set di dati molto grande, l'esecuzione della query SELECT CONVERT potrebbe richiedere più tempo. Ad esempio, se hai una tabella t con una colonna char c, puoi eseguire la seguente istruzione SELECT CONVERT per controllare le tuple di dati prima della conversione nel set di caratteri ASCII.

   SELECT c,CONVERT(c USING 'ascii')
   FROM t WHERE c != CONVERT(c USING ascii);
   

   Output di esempio:

   +------+--------------------------+
   | c    | convert(c using 'ascii') |
   +------+--------------------------+
   | é    | ?                        |
   | é    | ?                        |
   | £    | ?                        |
   +------+--------------------------+
   

   In questo esempio, poiché l'output restituisce queste tre tuple, sono presenti tre valori incoerenti che devono essere corretti. Prima di passare al passaggio successivo, esamina l'output della query e correggi i dati finché la query non restituisce zero tuple per tutte le colonne che devono essere convertite. Puoi anche combinare più colonne nella stessa query SELECT.

3. Scegli un'opzione per aggiornare il set di caratteri e le regole di confronto per le tabelle a livello di colonna.

4. Se vuoi aggiornare il set di caratteri per una colonna della tabella molto specifica, segui i passaggi nella scheda a livello di colonna, in cui devi codificare nuovamente solo le colonne selezionate. In caso contrario, segui i passaggi nella scheda a livello di tabella che ricodificano l'intera tabella.

   A livello di tabella

   Suggerimento: se hai molte tabelle, puoi utilizzare anche lo script per aggiornare più tabelle.

   1. Esegui la seguente istruzione per aggiornare il database:

      ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      Sostituisci quanto segue:

      • DB_NAME: il nome del database da aggiornare
      • NEW_CHARSET: il nuovo set di caratteri da applicare al database
      • NEW_COLLATION: la nuova collation da applicare al database

   2. Per ogni tabella interessata, esegui la seguente istruzione per aggiornarla:

      ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      Sostituisci quanto segue:

      • TABLE_NAME: il nome della tabella da aggiornare
      • NEW_CHARSET: il nuovo set di caratteri da applicare alla tabella
      • NEW_COLLATION: la nuova collation da applicare alla tabella

   3. Aggiorna a livello di tabella in modo che tutte le colonne che fanno parte della tabella vengano aggiornate con il nuovo set di caratteri e regole di confronto. Questa opzione ricrea completamente la tabella. Per utilizzare questa opzione, esegui la seguente istruzione:

      ALTER TABLE DB_NAME.TABLE_NAME
      CONVERT TO CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      Sostituisci quanto segue:

      • TABLE_NAME: il nome della tabella da aggiornare
      • NEW_CHARSET: il nuovo set di caratteri da applicare alla tabella
      • NEW_COLLATION: la nuova collation da applicare alla tabella
      Nota: con questa opzione, se MySQL non riesce a inserire i dati nel nuovo set di caratteri, potrebbe modificarne il tipo di dati. Questa modifica è improbabile, ma ti consigliamo di controllare le tabelle dopo l'aggiornamento della conversione.

   A livello di colonna

   Aggiornamento a livello di colonna. Questa opzione ricostruisce anche completamente la tabella. Se vengono aggiornate più colonne, questa opzione può causare più ricostruzioni.

   Suggerimento: se hai molte colonne, puoi utilizzare anche lo script per aggiornare più colonne in una tabella.

   Per utilizzare questa opzione, esegui la seguente istruzione:

   1. Esegui la seguente istruzione per aggiornare il database:

   ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

   Sostituisci quanto segue:

   • DB_NAME: il nome del database da aggiornare
   • NEW_CHARSET: il nuovo set di caratteri da applicare al database
   • NEW_COLLATION: la nuova collation da applicare al database
   2. Per ogni tabella interessata, esegui la seguente istruzione per aggiornarla:

   ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
    

   Sostituisci quanto segue:

   • TABLE_NAME: il nome della tabella da aggiornare
   • NEW_CHARSET: il nuovo set di caratteri da applicare alla tabella
   • NEW_COLLATION: la nuova collation da applicare alla tabella
   3. Esegui la seguente istruzione per aggiornare a livello di colonna:

   ALTER TABLE DB_NAME.TABLE_NAME
   MODIFY COLUMN_DEFINITION CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

   Nota: devi ottenere la definizione esatta della colonna. Per ottenere la definizione esatta della colonna, utilizza SHOW CREATE TABLE TABLE_NAME.

   Sostituisci quanto segue:

   • DB_NAME: il nome del database da aggiornare
   • TABLE_NAME: il nome della tabella da aggiornare
   • COLUMN_DEFINITION: specifica una definizione esatta della colonna che includa i vincoli. La definizione della colonna potrebbe contenere i valori precedenti di set di caratteri e regole di confronto. Devi aggiornare la definizione della colonna con i nuovi valori.
   • NEW_CHARSET: il nuovo set di caratteri da applicare alla colonna della tabella
   • NEW_COLLATION: la nuova collation da applicare alla colonna della tabella

   Ripeti questa istruzione per tutte le colonne stringa presenti nella tabella che richiedono un aggiornamento del set di caratteri o delle regole di confronto.

Convalidare gli aggiornamenti e cercare errori

Durante l'operazione ALTER TABLE, cerca eventuali errori che indicano che i dati esistenti nella tabella non sono compatibili con il nuovo set di caratteri. Ad esempio:

ERROR 1366 (HY000): Incorrect string value: '\xC3\xA9' for column 'c' at row 1

Se si verificano errori di compatibilità dei dati, ti consigliamo di controllare di nuovo le tabelle per verificare la presenza di errori di compatibilità dei dati e correggerli prima di eseguire di nuovo le istruzioni ALTER TABLE per ricodificare i dati.

Inoltre, durante l'operazione ALTER TABLE, il controllo della chiave esterna potrebbe non riuscire per le tabelle (la tabella della chiave esterna e la tabella a cui viene fatto riferimento) mentre la colonna viene ricodificata. In questo caso, puoi disattivare il controllo della chiave esterna (SET FOREIGN_KEY_CHECKS=0;) ed eseguire di nuovo l'aggiornamento.

Ricrea gli oggetti di database archiviati

Dopo aver aggiornato correttamente il database, puoi utilizzare il file mysqldump generato per ricreare gli oggetti del database archiviati con i valori di set di caratteri e regole di confronto.

Per ricreare gli oggetti del database archiviati:

1. Verifica che gli oggetti di database archiviati siano presenti nel database.
2. Assicurati che non ci sia un caricamento parallelo che prevede la presenza degli oggetti di database archiviati durante questo periodo di eliminazione e ricreazione.

3. Il file mysqldump che hai generato con lo strumento mysqldump non include l'istruzione per l'eliminazione dei trigger. Se devi eliminare i trigger, apri un terminale MySQL per generare l'istruzione DROP per i trigger.

   mysql> SELECT CONCAT('DROP TRIGGER ', TRIGGER_NAME, ';') AS
   drop_trigger_statements FROM information_schema.TRIGGERS
   WHERE TRIGGER_SCHEMA = DB_NAME;

4. Copia l'output.

5. Esegui l'istruzione per eliminare i trigger per il tuo database in una shell MySQL o in uno script.

6. Dopo aver rilasciato i trigger, applica il file di dump che hai generato con lo mysqldump strumento. Per applicare il file di dump e ricreare gli oggetti, inserisci quanto segue:

   $ mysql -uDBAuser -p -h IP_ADDRESS \
     -P 3306 DB_NAME < dump_objects.sql

   Sostituisci quanto segue:

   • IP_ADDRESS: indirizzo IP dell'istanza Cloud SQL per MySQL
   • DB_NAME: il nome del database

Esegui un altro backup dell'istanza

Dopo aver completato gli aggiornamenti, esegui un altro backup dell'istanza. Per ulteriori informazioni, vedi Creare un backup on demand.

Script di esempio

Se devi aggiornare database, tabelle e colonne, questa sezione fornisce script di esempio per aiutarti a ricodificare i dati.

Suggerimento :puoi provare a testare la conversione dei dati su un'istanza clone prima di ricodificare i dati in produzione.

Script per aggiornare più tabelle

Il seguente script genera un'istruzione per tutte le tabelle del database specificato. L'istruzione consente di ricodificare i dati esistenti in modo che siano conformi alle nuove impostazioni di set di caratteri e regole di confronto.

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

Sostituisci quanto segue:

• DB_NAME: il nome del database da aggiornare
• NEW_CHARSET: il nuovo set di caratteri da applicare alla tabella
• NEW_COLLATION: la nuova collation da applicare alla tabella

Output di esempio:

+--------------------------------------------------------------------------------------------+
|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 per aggiornare più colonne in una tabella

Il seguente script genera un'istruzione per tutte le colonne di una determinata tabella. L'istruzione consente di ricodificare i dati esistenti in modo che siano conformi alle nuove impostazioni di set di caratteri e regole di confronto.

1. Genera istruzioni ALTER TABLE per tutte le tabelle del database

   SELECT 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';

   Sostituisci quanto segue:

   • DB_NAME: il nome del database da aggiornare
   • NEW_CHARSET: il nuovo set di caratteri da applicare alla tabella
   • NEW_COLLATION: la nuova collation da applicare alla tabella

   Output di esempio:

   +------------------------------------------------------------------------------+
   | alter_table_statements                                                       |
   +------------------------------------------------------------------------------+
   | ALTER TABLE test1.t1 CHARACTER SET <new-charset> COLLATE <new-collation>;    |
   +------------------------------------------------------------------------------+
   

2. Per ogni tabella, genera ALTER TABLE MODIFY comandi.

   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');

   Sostituisci quanto segue:

   • DB_NAME: il nome del database da aggiornare
   • TABLE_NAME: il nome della tabella da aggiornare
   • COLUMN_DEFINITION: dopo aver ottenuto i risultati di ogni query, sostituisci con la definizione di colonna per ogni colonna (le definizioni di colonna possono variare a seconda della colonna)
   • NEW_CHARSET: il nuovo set di caratteri da applicare alla colonna della tabella
   • NEW_COLLATION: la nuova collation da applicare alla colonna della tabella

   Output di esempio:

   +-------------------------------------------------------------------------------------------------------------------------------------+
   |alter_table_column_statements                                                                                                        |
   +-------------------------------------------------------------------------------------------------------------------------------------+
   | ALTER TABLE t1 MODIFY c <column-definition-replaced-after-obtaining-result-set> CHARACTER SET <new-charset> COLLATE <new-collation>  |
   +-------------------------------------------------------------------------------------------------------------------------------------+
   

Passaggi successivi

• Monitora le istanze Cloud SQL
• Visualizza i log delle istanze