Aggiorna 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 personalizzati per queste impostazioni, il database utilizza il valore predefinito. Nella MySQL, puoi specificare valori personalizzati per il set di caratteri e le regole di confronto in diversi livelli di oggetti, tra cui database, tabelle e colonne. Per ulteriori informazioni sull'utilizzo delle configurazioni del set di caratteri e delle regole di confronto in MySQL, vedi Set di caratteri, collazioni, Unicode.

Se vuoi modificare il set di caratteri o le regole di confronto del database dopo che hai 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 ricreazione per oggetti di database archiviati.
5. Esegui aggiornamenti ai database e, se necessario, a tabelle e colonne.
6. Convalida gli aggiornamenti e cerca gli errori.
7. Ricrea 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, non è necessario controllare la configurazione delle tabelle, delle colonne e degli oggetti archiviati. Puoi utilizzare le seguenti dichiarazioni per verificare e i relativi oggetti di database.

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 il set di caratteri e i valori di confronto esistenti nella a livello di database, usa la seguente istruzione. 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 da modificare e il numero di tabelle, colonne e oggetti di database che richiedono gli aggiornamenti della 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 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                 |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+

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

Verificare la configurazione delle tabelle

Per verificare i valori esistenti per le tabelle, utilizza quanto segue :

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

Controlla la configurazione delle colonne della tabella

Per verificare 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 verificare i dettagli relativi schema o database attivi, 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 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 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 le regole di confronto previsti potrebbe essere necessario rilasciarlo e ricrearlo il nuovo set di caratteri o le nuove regole di confronto.

Controlla il mapping delle regole di confronto del database

Nell'output di esempio precedente, i valori archiviati viene creata con latin1_swedish_ci come regola di confronto predefinita e il set di caratteri corrispondente è latin1. Ciò significa che tutta l'elaborazione avviene il set di caratteri latin1 (a meno che non vengano utilizzati parametri specifici della procedura con un set di caratteri personalizzato. Se modifichi solo il carattere a livello di database l'impostazione e le regole di confronto, l'aggiornamento a livello di database non aggiorna automaticamente. 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 trovare il mapping tra le regole di confronto del database e il set di caratteri utilizzando 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 degli attivatori

Per verificare i valori per i trigger, utilizza la seguente istruzione come un 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 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 il trigger utilizzi il mapping delle regole di confronto del database corretto. Per ulteriori informazioni sul controllo le regole di confronto del database, vedi Controllare il mapping delle regole di confronto 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 sul controllo del database le regole di confronto, Controlla il mapping delle regole di confronto del database.

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

Controlla la configurazione delle funzioni

Per verificare 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 previsto, potresti dover rilasciarlo e ricrearlo con il nuovo set di caratteri oppure le regole di confronto. Verifica che la funzione utilizzi il mapping delle regole di confronto del database corretto. Per ulteriori informazioni sul controllo le regole di confronto del database, Controlla il mapping delle regole di confronto del database.

I o le regole di confronto. Verifica che la funzione utilizzi il mapping delle regole di confronto del database corretto. Per ulteriori informazioni su come controllare la collation del database, consulta Controllare la mappatura della collation del database.

Determina il livello di aggiornamento da eseguire

Dopo aver controllato il set di caratteri e la configurazione delle regole di confronto database, hai un elenco di database e possibilmente un elenco di oggetti di database da aggiornare.

Il numero di attività che dovrai eseguire dipende dal tipo di oggetti del 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 gli aggiornamenti possono richiedere più tempo; ma puoi usare uno dei metodi forniti script di esempio 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 del tuo in esecuzione in un'istanza Compute Engine. 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 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 il database e le colonne della tabella, utilizzerai il generato il file mysqldump per ricreare gli oggetti del database archiviati per configurare un database. Questo comando genera istruzioni per tutte le stored procedure, di funzioni, trigger ed 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

Aggiorna i database e, se necessario, le tabelle e le colonne.

In questo passaggio, aggiorni il set di caratteri e i valori delle regole di confronto del tuo o Microsoft SQL Server. Inoltre, potresti dover ricreare le tabelle colonne.

Esegui 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: le nuove regole di confronto 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 questa 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: le nuove regole di confronto da applicare alla tabella

Esegui aggiornamenti a livello di database, tabella e colonna

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

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

   • La tabella è causata dal set di caratteri ALTER TABLE e dalla conversione delle regole di confronto ricreare la tabella e bloccarla. Di conseguenza, qualsiasi query attiva che tenta di l'accesso alla tabella è bloccato.
   • Il set di caratteri ALTER TABLE e la conversione delle regole di confronto richiedono tempo perché recupera la tabella completa in memoria. Questa operazione potrebbe aumentare la latenza di qualsiasi carico di lavoro in esecuzione in parallelo perché il buffer pagine del pool utilizzate per la tabella aggiornata.
   • In caso di query che accedono a più tabelle e ad alcune tabelle vengono aggiornate mentre altre non lo sono ancora, producono risultati incoerenti durante questo momento della transazione.
   • Il processo di esecuzione di un set di caratteri ALTER TABLE e delle regole di confronto la conversione 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 provoca anche la ricostruzione di eventuali indici nella tabella.
   • Non è necessario aggiornare separatamente le istanze di replica. ALTER, DROP e I comandi 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 se nella tabella sono presenti tuple di dati che potrebbero non essere compatibili con nuovo set di caratteri. In caso di incompatibilità, è necessario correggere 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, la classe SELECT CONVERT l'esecuzione della query potrebbe richiedere più tempo. Ad esempio, se hai una tabella t con una colonna di caratteri c, puoi esegui la seguente istruzione SELECT CONVERT per controllare le tuple di dati prima 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 tre valori incoerenti che devono essere corretti. Prima di passare alla successiva passaggio, esaminare l'output della query e correggere i dati finché la query non restituisce zero tuple per tutte le colonne richiedono una conversione. Puoi anche combinare più colonne nella stessa risorsa SELECT query.

3. Scegli un'opzione per aggiornare il set di caratteri e le regole di confronto per il tuo 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 usa 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: le nuove regole di confronto 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: le nuove regole di confronto 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 l'istruzione seguente:

      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: le nuove regole di confronto da applicare alla tabella
      Nota: con questa opzione, se MySQL non è in grado di inserire i dati nella nuova di caratteri, MySQL potrebbe modificare il suo tipo di dati. Questa modifica è improbabile, ma ti consigliamo di controllare le tabelle dopo l'aggiornamento delle conversioni.

   A livello di colonna

   Aggiorna a livello di colonna. Questa opzione consente inoltre di ricostruire completamente la tabella. Se sono presenti più colonne questa opzione può causare più build.

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

   Per utilizzare questa opzione, esegui l'istruzione seguente:

   1. Esegui questa 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: le nuove regole di confronto da applicare al database
   2. Per ogni tabella interessata, esegui la seguente istruzione per aggiornare la tabella:

   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: le nuove regole di confronto 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 stringa 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. Per 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: Controlla di nuovo le tabelle per verificare che non ci siano errori di compatibilità dei dati e correggili prima eseguire nuovamente le istruzioni ALTER TABLE per ricodificare i dati.

Inoltre, durante l'operazione ALTER TABLE, le tabelle (la chiave esterna tabella e la tabella a cui viene fatto riferimento), un controllo della chiave esterna potrebbe non riuscire mentre la colonna venga sottoposto a ricodifica. In questo caso, puoi disabilitare la chiave esterna seleziona (SET FOREIGN_KEY_CHECKS=0;) ed esegui 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 esista un carico parallelo che prevede che gli oggetti del database archiviati durante il lancio e la nuova creazione.

3. Il file mysqldump che hai generato con lo strumento mysqldump non include l'istruzione per l'eliminazione dei trigger. Se devi rimuovere gli attivatori, quindi 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 gli attivatori per il database in una shell MySQL o in uno script.

6. Dopo aver rilasciato i trigger, applica il file di dump che hai generati 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, vedi Crea 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 in per configurare un database. L'istruzione consente di ricodificare i dati esistenti per renderli conformi alle nuove 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: le nuove regole di confronto 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 nel 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: le nuove regole di confronto 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 regola di confronto 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 dell'istanza