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 la collating dei database Cloud SQL.

Panoramica

Quando crei un database in Cloud SQL per MySQL, puoi specificare la configurazione del set di caratteri e della collating 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 la collating a diversi livelli di oggetti, inclusi database, tabelle e colonne. Per ulteriori informazioni sull'utilizzo delle configurazioni dei set di caratteri e delle regole di confronto in MySQL, consulta Set di caratteri, regole di confronto, Unicode.

Se vuoi modificare il set di caratteri o la collatorazione 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 del set di caratteri e dell'ordinamento.
2. Determina il livello di aggiornamento da eseguire.
3. Esegui un backup dell'istanza.
4. Genera comandi di ricostruzione per gli oggetti del database archiviati.
5. Esegui aggiornamenti ai database e, se necessario, a tabelle e colonne.
6. Convalida gli aggiornamenti e cerca gli errori.
7. Rigenera gli oggetti del database archiviati.
8. Esegui un altro backup dell'istanza.

Controllare i valori correnti del set di caratteri e della collating

Controlla i valori esistenti configurati per il set di caratteri e la collatorizzazione per il database e gli oggetti del 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 database e i relativi oggetti.

Controlla 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.

Controlla la configurazione del database

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

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

Ad esempio, viene visualizzato 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 in base ai seguenti valori del set di caratteri personalizzato:

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

In questo caso, per ogni database con un valore che vuoi modificare, devi aggiornare ogni oggetto che si trova al suo interno.

Controlla la configurazione delle tabelle

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

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 della collatura si applica solo alle colonne basate su stringhe (ad es. char, varchar, enum, text, mediumtext, longtext e set). Per tutti gli altri tipi di colonne, la configurazione del set di caratteri e della collatura viene visualizzata come NULL.

Controlla lo schema o il database attivo

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

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 della collating 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 dichiarazione:

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 dichiarazione:

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 procedura memorizzata non utilizza il valore del set di caratteri o della collating previsto, potrebbe essere necessario eliminarla e ricrearla con il nuovo set di caratteri o la nuova collating.

Controllare la mappatura della concatenazione del database

Nell'output dell'esempio precedente, la procedura memorizzata viene creata con latin1_swedish_ci come ordinamento predefinito e il set di caratteri corrispondente è latin1. Ciò significa che l'intera elaborazione avviene utilizzando il set di caratteri latin1 (a meno che i parametri specifici della procedura non siano contrassegnati con un set di caratteri personalizzato). Se modifichi solo l'insieme di caratteri e la collation a livello di database, l'aggiornamento a livello di database non aggiorna automaticamente la procedura memorizzata. La procedura continua a funzionare con il set di caratteri latin1 e la rispettiva collating. L'eliminazione e la successiva creazione della procedura memorizzata assicurano che la procedura inizi a utilizzare il nuovo set di caratteri e la nuova concatenazione del database appena aggiornato.

Puoi scoprire la mappatura tra la collation 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             |
+--------------------+

Controlla la configurazione degli attivatori

Per controllare i valori degli attivatori, utilizza la seguente dichiarazione 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 dichiarazione di esempio.

SHOW CREATE trigger DB_NAME.TRIGGER_NAME;

Sostituisci quanto segue:

• DB_NAME: il nome del database MySQL
• TRIGGER_NAME: il nome dell'attivatore

Se l'attivatore non utilizza il valore del set di caratteri o della collating previsto, potrebbe essere necessario eliminarlo e ricrearlo con il nuovo set di caratteri o la nuova collating. Verifica che l'attivatore utilizzi la mappatura della concatenazione del database corretta. Per ulteriori informazioni su come controllare la collation del database, consulta Controllare la mappatura della collation del database.

Controllare la configurazione degli eventi

Per controllare i valori degli eventi, utilizza la seguente dichiarazione 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 dichiarazione 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 valore del set di caratteri o della collating previsto, potrebbe essere necessario rimuoverlo e ricrearlo con il nuovo set di caratteri o la nuova collating. Verifica che l'evento utilizzi la mappatura della concatenazione del database corretta. Per ulteriori informazioni su come controllare la collation del database, consulta Controllare la mappatura della collation del database.

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

Controllare la configurazione delle funzioni

Per verificare i valori delle funzioni, utilizza la seguente dichiarazione 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 l'istruzione di esempio seguente.

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 ordinamento previsto, potrebbe essere necessario eliminarlo e ricrearlo con il nuovo set di caratteri o con il nuovo ordinamento. Verifica che la funzione utilizzi la mappatura della concatenazione del database corretta. Per ulteriori informazioni sul controllo della collation del database, consulta Controllare la mappatura della collation del database.

Il set di caratteri o la collation. Verifica che la funzione utilizzi la mappatura della concatenazione del database corretta. Per ulteriori informazioni sul controllo della collation del database, consulta Controllare la mappatura della collation del database.

Determina il livello di aggiornamento da eseguire

Dopo aver controllato la configurazione del set di caratteri e della collating del database, hai un elenco di database e, eventualmente, un elenco di oggetti del database da aggiornare.

Il numero di attività da eseguire dipende dal tipo di oggetti 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 ricreare le tabelle e gli oggetti del database. Questi aggiornamenti possono richiedere molto tempo, ma 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 il backup della vostra istanza. Per ulteriori informazioni, consulta Creare un backup on demand. Ti consigliamo di eseguire il backup senza eseguire operazioni DDL sull'istanza.

Genera comandi di ricostruzione per gli oggetti del database archiviati nel database

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

Esegui il seguente comando per poter utilizzare l'output per ricreare in un secondo momento tutte le procedure, le funzioni e gli eventi archiviati di un determinato database (quando ricrei gli oggetti del 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 database e, se necessario, a tabelle e colonne.

In questo passaggio, aggiorni i valori del set di caratteri e della collating dei 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 dichiarazione 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 collating da applicare al database

Esegui aggiornamenti a livello di database e tabella

Se esegui aggiornamenti a livello di database e tabella, svolgi quanto segue:

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 collating 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 ordinamento da applicare alla tabella

Esegui aggiornamenti a livello di database, tabella e colonna

A questo livello, devi ricodificare i dati esistenti in modo che rispettino le nuove impostazioni di collation e del set di caratteri. Se esegui aggiornamenti a livello di database, tabella e colonna:

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

   • La conversione del set di caratteri e delle regole di confronto ALTER TABLE provoca 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 pool di buffer vengono utilizzate per la tabella aggiornata.
   • Se esistono query che accedono a più tabelle e alcune di queste vengono aggiornate mentre altre non sono ancora aggiornate, le query potrebbero produrre risultati incoerenti durante questo periodo di transazione.
   • La procedura di esecuzione di una conversione del set di caratteri e della collatorizzazione ALTER TABLE crea una tabella shadow, quindi pianifica di conseguenza l'utilizzo del disco.
   • La conversione del set di caratteri e delle regole di confronto ALTER TABLE provoca anche la ricostruzione di eventuali indici nella tabella.
   • Non è necessario aggiornare separatamente le istanze di replica. I comandi ALTER, DROP e CREATE vengono replicati automaticamente. Tuttavia, l'esecuzione di comandi DDL può causare un aumento del tempo di latenza della replica durante l'operazione.
   • Se ripristini uno dei database da un backup eseguito prima dell'applicazione di 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, riscontrerai errori durante il processo di conversione. Puoi utilizzare la seguente dichiarazioneSELECT 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 il seguente statement SELECT CONVERT per controllare le tuple di dati prima della conversione al 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, ci sono 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 hanno bisogno di conversione. Puoi anche combinare più colonne nella stessa query SELECT.

3. Scegli un'opzione per aggiornare il set di caratteri e la collatorizzazione delle tue 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, dove dovrai solo ricodificare le colonne selezionate. In caso contrario, segui i passaggi descritti nella scheda A livello di tabella per ricodificare la tabella completa.

   A livello di tabella

   Suggerimento: se hai molte tabelle, puoi anche utilizzare 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 collating 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 ordinamento da applicare alla tabella

   3. Esegui l'aggiornamento a livello di tabella in modo che tutte le colonne che ne fanno parte vengano aggiornate con il nuovo set di caratteri e la nuova ordinamento. Questa opzione ricostruisce completamente la tabella. Per utilizzare questa opzione, esegui il seguente comando:

      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 collating da applicare alla tabella
      Nota: con questa opzione, se MySQL non riesce a adattare i dati al nuovo set di caratteri, potrebbe modificare il tipo di dati. Questa modifica è improbabile, ma ti consigliamo di controllare le tabelle dopo l'aggiornamento delle conversioni.

   A livello di colonna

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

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

   Per utilizzare questa opzione, esegui il seguente comando:

   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 collating 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 ordinamento da applicare alla tabella
   3. Esegui la seguente istruzione per l'aggiornamento 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 vecchi valori del set di caratteri e dell'ordinamento. 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 ordinamento da applicare alla colonna della tabella

   Ripeti questa istruzione per tutte le colonne di stringhe presenti nella tabella che richiedono un aggiornamento del set di caratteri o della collating.

Convalida gli aggiornamenti e cerca gli 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 riscontri errori di compatibilità dei dati, ti consigliamo di controllare di nuovo le tabelle per verificare la presenza di errori di compatibilità dei dati e di correggerli prima di eseguire di nuovo le istruzioni ALTER TABLE per ricodificare i dati.

Inoltre, durante l'operazione ALTER TABLE, le tabelle (la tabella della chiave esterna e la tabella a cui fa riferimento) potrebbero non superare il controllo della chiave esterna durante la ricodifica della colonna. 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 il database, puoi utilizzare il file mysqldump generato per ricreare gli oggetti del database archiviati con i valori del set di caratteri e della collating.

Per ricreare gli oggetti del database archiviati:

1. Verifica che gli oggetti del database archiviati siano presenti nel database.
2. Assicurati che non ci siano carichi paralleli che prevedono la presenza degli oggetti del database archiviati durante questo calo e il tempo di ricreazione.

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

   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 gli attivatori per il database in una shell MySQL o in uno script.

6. Dopo aver inserito gli attivatori, applica il file dump che hai generato con lo strumento mysqldump. Per applicare il file 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

Al termine degli aggiornamenti, esegui un altro backup dell'istanza. Per ulteriori informazioni, consulta Creare un backup on demand.

Script di esempio

Se devi aggiornare database, tabelle e colonne, in questa sezione sono disponibili 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

Lo script seguente 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 ordinamento 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

Lo script seguente 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 ordinamento 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 comandi 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');

   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 da ogni query, sostituire con la definizione della colonna per ogni colonna (le definizioni delle colonne possono variare da una colonna all'altra)
   • NEW_CHARSET: il nuovo set di caratteri da applicare alla colonna della tabella
   • NEW_COLLATION: la nuova ordinamento 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
• Visualizzare i log delle istanze