Zeichensatz und Sortierung einer Datenbank aktualisieren

MySQL   |  PostgreSQL   |  SQL Server

Auf dieser Seite wird beschrieben, wie Sie den Zeichensatz und die Sortierung von Cloud SQL-Datenbanken aktualisieren.

Übersicht

Wenn Sie eine Datenbank in Cloud SQL for MySQL erstellen, können Sie die Konfiguration für Zeichensatz und Sortierung für eine Datenbank angeben. Wenn Sie keine benutzerdefinierten Werte für diese Einstellungen angeben, wird der Standardwert verwendet. In MySQL können Sie benutzerdefinierte Werte für den Zeichensatz und die Sortierung auf verschiedenen Objektebenen angeben, wie auf Datenbank-, Tabellen- und Spaltenebene. Weitere Informationen zur Verwendung von Zeichensatz- und Sortierungskonfigurationen in MySQL finden Sie unter Zeichensätze, Sortierungen, Unicode.

Wenn Sie den Zeichensatz oder die Sortierung Ihrer Datenbank ändern möchten, nachdem Sie die Datenbank und einige Objekte in der Datenbank bereits erstellt haben, können Sie dazu der Anleitung in diesem Dokument folgen.

Dieses Verfahren umfasst die folgenden Schritte:

1. Die aktuellen Werte für den Zeichensatz und die Sortierung prüfen
2. Bestimmen, auf welcher Ebene aktualisiert werden soll
3. Instanz sichern
4. Befehle für die Neuerstellung gespeicherter Datenbankobjekte generieren
5. Datenbanken und gegebenenfalls Tabellen und Spalten aktualisieren
6. Aktualisierungen prüfen und nach Fehlern suchen
7. Gespeicherte Datenbankobjekte neu erstellen
8. Eine weitere Sicherung der Instanz erstellen

Aktuelle Werte für Zeichensatz und Sortierung prüfen

Prüfen Sie die vorhandenen Werte, die für den Zeichensatz und die Sortierung Ihrer Datenbank und Datenbankobjekte konfiguriert sind. Wenn Sie Ihre Datenbank prüfen, müssen Sie auch die Konfiguration der Tabellen, Spalten und gespeicherten Objekte prüfen. Mit den folgenden Anweisungen können Sie Ihre Datenbanken und deren Datenbankobjekte prüfen.

Instanzkonfiguration prüfen

Wenn Sie die globalen Standardwerte auf Instanzebene (Serverebene) prüfen möchten, starten Sie eine MySQL-Shell und geben Sie den folgenden Befehl ein:

mysql> SELECT @@character_set_server,@@collation_server;

Beispielausgabe:

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

Prüfen Sie, ob sich die globalen Standardwerte von den benutzerdefinierten Werten unterscheiden, die Sie später für Ihre Datenbank angeben.

Datenbankkonfiguration prüfen

Verwenden Sie die folgende Anweisung, um die vorhandenen Werte für Zeichensatz und Sortierung auf Datenbankebene zu prüfen. Erstellen Sie nach dem Ausführen jeder Anweisung eine Liste aller Datenbanken und Datenbankobjekte, die Sie mit neuen Werten für die Zeichensätze oder die Sortierung aktualisieren müssen. Die Liste hängt davon ab, welche Datenbanken Sie ändern möchten und wie viele Tabellen, Spalten und Datenbankobjekte Konfigurationsupdates erfordern.

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

Sie erhalten beispielsweise die folgende Ausgabe:

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

Sie möchten in den Datenbanken jedoch die folgenden Werte für benutzerdefinierte Zeichensätze ändern:

+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| 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 diesem Fall müssen Sie für jede Datenbank mit einem Wert, den Sie ändern möchten, jedes Objekt in der Datenbank aktualisieren.

Konfiguration der Tabellen prüfen

Verwenden Sie die folgende Anweisung, um die vorhandenen Werte für die Tabellen zu prüfen:

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

Beispielausgabe:

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

Konfiguration der Tabellenspalten prüfen

Verwenden Sie die folgende Anweisung, um die Werte für Spalten zu prüfen:

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

Beispielausgabe:

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

Hinweis: Die Konfiguration des Zeichensatzes und der Sortierung gilt nur für stringbasierte Spalten (z. B. char, varchar, enum, text, mediumtext, longtext und set). Bei allen anderen Spaltentypen werden die Konfiguration des Zeichensatzes und der Sortierung als NULL angezeigt.

Aktives Schema oder aktive Datenbank prüfen

Mit der folgenden Anweisung können Sie die Details für das aktive Schema oder die aktive Datenbank prüfen.

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;

Beispielausgabe:

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

Hinweis :Die Ausgabe enthält nur die Konfiguration für Zeichensatz und Sortierung für anwendbare stringbasierte Spalten für die aktuell aktive Datenbank.

Konfiguration der gespeicherten Prozeduren prüfen

Verwenden Sie die folgende Anweisung, um die Werte für gespeicherte Prozeduren zu prüfen:

SHOW PROCEDURE STATUS WHERE db = 'DB_NAME';

Ersetzen Sie DB_NAME durch den Namen der Datenbank.

Beispielausgabe:

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

Verwenden Sie die folgende Anweisung, um die Definition einer gespeicherten Prozedur abzurufen:

SHOW CREATE PROCEDURE DB_NAME.SP_NAME;

Ersetzen Sie Folgendes:

• DB_NAME: Der Name der MySQL-Datenbank
• SP_NAME: der Name der gespeicherten Prozedur

Wenn in einer gespeicherten Prozedur nicht der gewünschte Wert für den Zeichensatz oder die Sortierung verwendet wird, müssen Sie sie möglicherweise löschen und mit dem neuen Zeichensatz oder der neuen Sortierung neu erstellen.

Datenbanksortierung prüfen

In der vorherigen Beispielausgabe wird die gespeicherte Prozedur mit latin1_swedish_ci als Standardsortierung und dem entsprechenden Zeichensatz latin1 erstellt. Das bedeutet, dass die gesamte Verarbeitung mit dem Zeichensatz latin1 erfolgt, es sei denn, bestimmte Parameter der Prozedur sind mit einem benutzerdefinierten Zeichensatz getaggt. Wenn Sie den Zeichensatz und die Sortierung nur auf Datenbankebene ändern, wird die gespeicherte Prozedur nicht automatisch durch die Aktualisierung auf Datenbankebene aktualisiert. Die Prozedur wird weiterhin mit dem Zeichensatz latin1 und der entsprechenden Sortierung ausgeführt. Wenn Sie die gespeicherte Prozedur löschen und neu erstellen, werden der neue Zeichensatz und die neue Sortierung der Datenbank verwendet, die Sie gerade aktualisiert haben.

Die Zuordnung zwischen Sortierung und Zeichensatz einer Datenbank können Sie durch Abfragen der Tabelle information_schema.COLLATION_CHARACTER_SET_APPLICABILITY ermitteln.

Verwenden Sie die folgende Anweisung als Beispiel:

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

Beispielausgabe:

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

Konfiguration der Trigger prüfen

Verwenden Sie die folgende Anweisung als Beispiel, um die Werte für Trigger zu prüfen.

SHOW TRIGGERS FROM DB_NAME;

Ersetzen Sie DB_NAME durch den Namen der MySQL-Datenbank.

Mit der folgenden Beispielanweisung können Sie die Definition eines Triggers abrufen.

SHOW CREATE trigger DB_NAME.TRIGGER_NAME;

Ersetzen Sie Folgendes:

• DB_NAME: Der Name der MySQL-Datenbank
• TRIGGER_NAME: der Name des Triggers

Wenn für den Trigger nicht der gewünschte Wert für den Zeichensatz oder die Sortierung verwendet wird, müssen Sie ihn möglicherweise löschen und mit dem neuen Zeichensatz oder der neuen Sortierung neu erstellen. Prüfen Sie, ob für den Trigger die richtige Datenbanksortierung zugeordnet ist. Weitere Informationen zum Prüfen der Datenbanksortierung finden Sie unter Datenbanksortierungszuordnung prüfen.

Konfiguration der Ereignisse prüfen

Verwenden Sie die folgende Anweisung als Beispiel, um die Werte für Ereignisse zu prüfen.

SHOW EVENTS FROM DB_NAME;

Ersetzen Sie DB_NAME durch den Namen der Datenbank.

Mit der folgenden Beispielanweisung können Sie die Definition eines Ereignisses abrufen.

SHOW CREATE EVENT DB_NAME.EVENT_NAME;

Ersetzen Sie Folgendes:

• DB_NAME: Der name der Datenbank
• EVENT_NAME: Der Name des Ereignisses

Wenn für das Ereignis nicht der gewünschte Wert für den Zeichensatz oder die Sortierung verwendet wird, müssen Sie es möglicherweise löschen und mit dem neuen Zeichensatz oder der neuen Sortierung neu erstellen. Prüfen Sie, ob für das Ereignis die richtige Datenbanksortierung zugeordnet ist. Weitere Informationen zum Prüfen der Datenbanksortierung finden Sie unter Datenbanksortierungszuordnung prüfen.

Achtung: Wenn Sie Ereignisse neu erstellen, kann es passieren, dass sie ihre geplante Abfolge verpassen.

Konfiguration der Funktionen prüfen

Mit der folgenden Anweisung können Sie die Werte für Funktionen prüfen.

SHOW FUNCTION STATUS WHERE db = 'DB_NAME';

Ersetzen Sie DB_NAME durch den Namen der Datenbank.

Verwenden Sie die folgende Beispielanweisung, um die Definition einer Funktion abzurufen.

SHOW CREATE FUNCTION DB_NAME.FUNCTION_NAME;

Ersetzen Sie Folgendes:

• DB_NAME: Der Name der MySQL-Datenbank
• FUNCTION_NAME: Der Name der Funktion

Wenn für die Funktion nicht der gewünschte Wert für den Zeichensatz oder die Sortierung verwendet wird, müssen Sie sie möglicherweise löschen und mit dem neuen Zeichensatz oder der neuen Sortierung neu erstellen. Prüfen Sie, ob für die Funktion die richtige Datenbanksortierung zugeordnet ist. Weitere Informationen zum Prüfen der Datenbanksortierung finden Sie unter Datenbanksortierungszuordnung prüfen.

Zeichensatz oder Sortierung: Prüfen Sie, ob für die Funktion die richtige Datenbanksortierung zugeordnet ist. Weitere Informationen zum Prüfen der Datenbanksortierung finden Sie unter Datenbanksortierungszuordnung prüfen.

Bestimmen, auf welcher Ebene aktualisiert werden soll

Nachdem Sie die Konfiguration des Zeichensatzes und der Sortierung Ihrer Datenbank geprüft haben, haben Sie eine Liste der Datenbanken und möglicherweise eine Liste der Datenbankobjekte, die Sie aktualisieren müssen.

Die Anzahl der Aufgaben, die Sie ausführen müssen, hängt vom Typ der Datenbankobjekte ab, für die Konfigurationsänderungen erforderlich sind.

• Aktualisierungen auf Datenbankebene ausführen: Sie müssen nur die MySQL-Metadaten aktualisieren.
• Aktualisierungen auf Datenbank- und Tabellenebene ausführen: Sie müssen nur die MySQL-Metadaten aktualisieren.
• Aktualisierungen auf Datenbank-, Tabellen- und Spaltenebene ausführen: Sie müssen die Metadaten aktualisieren und Tabellen und Datenbankobjekte neu erstellen. Diese Aktualisierungen können am längsten dauern. Sie können jedoch eines der bereitgestellten Beispielskripts verwenden, um alle Tabellen und Spalten in einer bestimmten Datenbank zu aktualisieren.

Instanz sichern

Wichtig: Überspringen Sie diesen Schritt nicht.

Erstellen Sie vor dem Ausführen dieser Updates ein Backup Ihrer Instanz. Weitere Informationen finden Sie unter On-Demand-Sicherung erstellen. Wir empfehlen, die Sicherung zu erstellen, wenn keine DDL-Vorgänge für die Instanz ausgeführt werden.

Befehle zur Neuerstellung der gespeicherten Datenbankobjekte in der Datenbank generieren

Wenn Sie gespeicherte Datenbankobjekte in Ihren Datenbanken mit dem neuen Zeichensatz und der neuen Sortierung löschen und neu erstellen müssen, verwenden Sie jetzt das Tool mysqldump, um die Befehle zu generieren. Nachdem Sie Ihre Datenbank- und Tabellenspalten aktualisiert haben, verwenden Sie die generierte mysqldump-Datei, um die gespeicherten Datenbankobjekte in Ihrer Datenbank neu zu erstellen. Mit diesem Befehl werden Anweisungen für alle gespeicherten Prozeduren, Funktionen, Trigger und Ereignisse generiert.

Führen Sie den folgenden Befehl aus, damit Sie die Ausgabe später verwenden können, um alle gespeicherten Prozeduren, Funktionen und Ereignisse einer bestimmten Datenbank neu zu erstellen (wenn Sie gespeicherte Datenbankobjekte neu erstellen).

$ 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

Ersetzen Sie Folgendes:

• IP_ADDRESS: IP-Adresse der Cloud SQL for MySQL-Instanz
• DB_NAME: Der Name der MySQL-Datenbank

Datenbanken und gegebenenfalls Tabellen und Spalten aktualisieren

In diesem Schritt aktualisieren Sie die Werte für den Zeichensatz und die Sortierung Ihrer Datenbanken. Möglicherweise müssen Sie auch Ihre Tabellen und Spalten neu erstellen.

Aktualisierungen auf Datenbankebene

Wenn Sie nur Aktualisierungen auf Datenbankebene durchführen, führen Sie die folgende Anweisung aus, um Ihre Datenbank zu aktualisieren:

ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

Ersetzen Sie Folgendes:

• DB_NAME: Der Name der Datenbank, die aktualisiert werden soll
• NEW_CHARSET: Der neue Zeichensatz, der auf die Datenbank angewendet werden soll
• NEW_COLLATION: Die neue Sortierung, die auf die Datenbank angewendet werden soll

Aktualisierungen auf Datenbank- und Tabellenebene

Wenn Sie Aktualisierungen auf Datenbank- und Tabellenebene vornehmen, gehen Sie so vor:

1. Führen Sie die folgende Anweisung aus, um Ihre Datenbank zu aktualisieren:

   ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

   Ersetzen Sie Folgendes:

   • DB_NAME: Der Name der Datenbank, die aktualisiert werden soll
   • NEW_CHARSET: Der neue Zeichensatz, der auf die Datenbank angewendet werden soll
   • NEW_COLLATION: Die neue Sortierung, die auf die Datenbank angewendet werden soll

2. Führen Sie für jede Tabelle, die Sie aktualisieren möchten, die folgende Anweisung aus:

   ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

   Ersetzen Sie Folgendes:

   • TABLE_NAME: Der Name der Tabelle, die aktualisiert werden soll
   • NEW_CHARSET: Der neue Zeichensatz, der auf die Tabelle angewendet werden soll
   • NEW_COLLATION: Die neue Sortierung, die auf die Tabelle angewendet werden soll

Aktualisierungen auf Datenbank-, Tabellen- und Spaltenebene

Auf dieser Ebene müssen Sie vorhandene Daten neu codieren, damit sie den neuen Zeichensatz- und Sortierungseinstellungen entsprechen. Wenn Sie Aktualisierungen auf Datenbank-, Tabellen- und Spaltenebene vornehmen, gehen Sie so vor:

1. Bevor Sie die Tabellenspalten aktualisieren, sollten Sie die folgenden Hinweise lesen:

   • Die Zeichensatz- und Sortierungskonvertierung ALTER TABLE führt dazu, dass die Tabelle neu erstellt und gesperrt wird. Daher wird jede aktive Abfrage, die versucht, auf die Tabelle zuzugreifen, blockiert.
   • Die Umwandlung des Zeichensatzes und der Sortierung mit ALTER TABLE beansprucht etwas Zeit, da die gesamte Tabelle in den Arbeitsspeicher geladen wird. Dieser Vorgang kann die Latenz parallel ausgeführter Arbeitslasten erhöhen, da die Seiten des Zwischenspeicherpools für die aktualisierte Tabelle verwendet werden.
   • Wenn auf mehrere Tabellen zugegriffen wird und einige der Tabellen aktualisiert werden, während andere noch nicht aktualisiert sind, können die Abfragen während dieser Transaktionszeit inkonsistente Ergebnisse liefern.
   • Wenn Sie eine Zeichensatz- und -Sortierungskonvertierung ALTER TABLE ausführen, wird eine zusätzliche Tabelle (sogenannte Schattentabelle) erstellt. Planen Sie die Laufwerknutzung entsprechend.
   • Durch die Konvertierung des Zeichensatzes und der Sortierung mit ALTER TABLE werden auch alle Indexe für die Tabelle neu erstellt.
   • Sie müssen die Replikatinstanzen nicht separat aktualisieren. Die Befehle ALTER, DROP und CREATE werden automatisch repliziert. Das Ausführen von DDL-Befehlen kann jedoch dazu führen, dass sich die Verzögerungszeit des Replikats während des Vorgangs erhöht.
   • Wenn Sie eine der Datenbanken aus einem Backup wiederherstellen, das vor der Anwendung dieser Updates erstellt wurde, müssen Sie die Updates noch einmal anwenden.

2. Prüfen Sie Ihre Tabelle auf Datentupel, die möglicherweise nicht mit dem neuen Zeichensatz kompatibel sind. Wenn es Inkompatibilitäten gibt, müssen Sie diese beheben, bevor Sie den Zeichensatz aktualisieren. Andernfalls treten während der Konvertierung Fehler auf. Mit der folgenden SELECT CONVERT-Anweisung können Sie Ihre Daten mit dem neuen Zeichensatz validieren.

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

   Hinweis: Bei sehr großen Datenmengen kann die Ausführung der SELECT CONVERT-Abfrage länger dauern. Wenn Sie beispielsweise eine Tabelle t mit einer Zeichenspalte c haben, können Sie die folgende SELECT CONVERT-Anweisung ausführen, um die Datentupel vor der Konvertierung in den ASCII-Zeichensatz zu prüfen.

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

   Beispielausgabe:

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

   Da in diesem Beispiel die drei Tupel zurückgegeben werden, gibt es drei inkonsistente Werte, die korrigiert werden müssen. Bevor Sie mit dem nächsten Schritt fortfahren, sehen Sie sich die Ausgabe Ihrer Abfrage an und korrigieren Sie die Daten, bis Ihre Abfrage für alle Spalten, die konvertiert werden müssen, null Tupel zurückgibt. Sie können auch mehrere Spalten in derselben SELECT-Abfrage kombinieren.

3. Wählen Sie eine Option zum Aktualisieren des Zeichensatzes und der Sortierung für Ihre Tabellen auf Spaltenebene aus.

4. Wenn Sie den Zeichensatz für eine bestimmte Tabellenspalte aktualisieren möchten, folgen Sie der Anleitung auf dem Tab „Spaltenebene“. Dort müssen Sie nur ausgewählte Spalten neu codieren. Andernfalls folgen Sie der Anleitung auf dem Tab „Tabelle“, um die gesamte Tabelle neu zu codieren.

   Tabellenebene

   Tipp: Wenn Sie viele Tabellen haben, können Sie mit dem Script auch mehrere Tabellen aktualisieren.

   1. Führen Sie die folgende Anweisung aus, um Ihre Datenbank zu aktualisieren:

      ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      Ersetzen Sie Folgendes:

      • DB_NAME: Der Name der Datenbank, die aktualisiert werden soll
      • NEW_CHARSET: Der neue Zeichensatz, der auf die Datenbank angewendet werden soll
      • NEW_COLLATION: Die neue Sortierung, die auf die Datenbank angewendet werden soll

   2. Führen Sie für jede betroffene Tabelle die folgende Anweisung aus, um die Tabelle zu aktualisieren:

      ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      Ersetzen Sie Folgendes:

      • TABLE_NAME: Der Name der Tabelle, die aktualisiert werden soll
      • NEW_CHARSET: Der neue Zeichensatz, der auf die Tabelle angewendet werden soll
      • NEW_COLLATION: Die neue Sortierung, die auf die Tabelle angewendet werden soll

   3. Aktualisieren Sie auf Tabellenebene, damit alle Spalten, die Teil der Tabelle sind, mit dem neuen Zeichensatz und der neuen Sortierung aktualisiert werden. Bei dieser Option wird die Tabelle vollständig neu erstellt. Führen Sie die folgende Anweisung aus, um diese Option zu verwenden:

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

      Ersetzen Sie Folgendes:

      • TABLE_NAME: Der Name der Tabelle, die aktualisiert werden soll
      • NEW_CHARSET: Der neue Zeichensatz, der auf die Tabelle angewendet werden soll
      • NEW_COLLATION: Die neue Sortierung, die auf die Tabelle angewendet werden soll
      Hinweis: Wenn MySQL die Daten mit dieser Option nicht in den neuen Zeichensatz einpassen kann, ändert MySQL möglicherweise den Datentyp. Diese Änderung ist unwahrscheinlich, wir empfehlen Ihnen aber, Ihre Tabellen nach der Konvertierung zu überprüfen.

   Spaltenebene

   Aktualisierung auf Spaltenebene: Bei dieser Option wird die Tabelle ebenfalls vollständig neu erstellt. Wenn mehrere Spalten aktualisiert werden, kann diese Option zu mehreren Neuerstellungen führen.

   Tipp: Wenn Sie viele Spalten haben, können Sie auch das Script zum Aktualisieren mehrerer Spalten in einer Tabelle verwenden.

   Führen Sie die folgende Anweisung aus, um diese Option zu verwenden:

   1. Führen Sie die folgende Anweisung aus, um Ihre Datenbank zu aktualisieren:

   ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

   Ersetzen Sie Folgendes:

   • DB_NAME: Der Name der Datenbank, die aktualisiert werden soll
   • NEW_CHARSET: Der neue Zeichensatz, der auf die Datenbank angewendet werden soll
   • NEW_COLLATION: Die neue Sortierung, die auf die Datenbank angewendet werden soll
   2. Führen Sie für jede betroffene Tabelle die folgende Anweisung aus, um die Tabelle zu aktualisieren:

   ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;
    

   Ersetzen Sie Folgendes:

   • TABLE_NAME: Der Name der Tabelle, die aktualisiert werden soll
   • NEW_CHARSET: Der neue Zeichensatz, der auf die Tabelle angewendet werden soll
   • NEW_COLLATION: Die neue Sortierung, die auf die Tabelle angewendet werden soll
   3. Führen Sie die folgende Anweisung aus, um die Aktualisierung auf Spaltenebene vorzunehmen:

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

   Hinweis: Sie müssen die genaue Spaltendefinition abrufen. Verwenden Sie SHOW CREATE TABLE TABLE_NAME, um die genaue Spaltendefinition abzurufen.

   Ersetzen Sie Folgendes:

   • DB_NAME: Der Name der Datenbank, die aktualisiert werden soll
   • TABLE_NAME: Der Name der Tabelle, die aktualisiert werden soll
   • COLUMN_DEFINITION: Geben Sie eine genaue Spaltendefinition mit Einschränkungen an. Die Spaltendefinition enthält möglicherweise die alten Werte für den Zeichensatz und die Sortierung. Sie müssen die Spaltendefinition mit den neuen Werten aktualisieren
   • NEW_CHARSET: Der neue Zeichensatz, der auf die Tabellenspalte angewendet werden soll
   • NEW_COLLATION: Die neue Sortierung, die auf die Tabellenspalte angewendet werden soll

   Wiederholen Sie diese Anweisung für alle String-Spalten in der Tabelle, für die ein Zeichensatz oder eine Sortierung aktualisiert werden muss.

Aktualisierungen prüfen und nach Fehlern suchen

Achten Sie während des Vorgangs ALTER TABLE auf Fehler, die darauf hinweisen, dass vorhandene Daten in der Tabelle nicht mit dem neuen Zeichensatz kompatibel sind. Beispiel:

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

Wenn Sie auf Datenkompatibilitätsfehler stoßen, empfehlen wir, die Tabellen noch einmal auf solche Fehler zu prüfen und sie zu beheben, bevor Sie die ALTER TABLE-Anweisungen noch einmal ausführen, um die Daten neu zu codieren.

Außerdem kann während des Vorgangs ALTER TABLE die Fremdschlüsselprüfung für die Tabellen (die Fremdschlüsseltabelle und die referenzierte Tabelle) fehlschlagen, während die Spalte neu codiert wird. In diesem Fall können Sie die Fremdschlüsselprüfung (SET FOREIGN_KEY_CHECKS=0;) deaktivieren und die Aktualisierung noch einmal ausführen.

Gespeicherte Datenbankobjekte neu erstellen

Nachdem Sie Ihre Datenbank erfolgreich aktualisiert haben, können Sie die generierte mysqldump-Datei verwenden, um die gespeicherten Datenbankobjekte mit den Werten für Zeichensatz und Sortierung neu zu erstellen.

So erstellen Sie Ihre gespeicherten Datenbankobjekte neu:

1. Prüfen Sie, ob die gespeicherten Datenbankobjekte in der Datenbank vorhanden sind.
2. Achten Sie darauf, dass keine parallele Auslastung vorliegt, für die die gespeicherten Datenbankobjekte während dieses Zeitraums vorhanden sein müssen.

3. Die mysqldump-Datei, die Sie mit dem mysqldump-Tool generiert haben, enthält nicht die Anweisung zum Entfernen von Triggern. Wenn Sie Trigger löschen müssen, öffnen Sie ein MySQL-Terminal, um die DROP-Anweisung für Trigger zu generieren.

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

4. Kopieren Sie die Ausgabe.

5. Führen Sie die Anweisung zum Löschen der Trigger für Ihre Datenbank entweder in einer MySQL-Shell oder in einem Skript aus.

6. Nachdem Sie die Trigger gelöscht haben, wenden Sie die Dumpdatei an, die Sie mit dem Tool mysqldump generiert haben. Geben Sie Folgendes ein, um die Dump-Datei anzuwenden und die Objekte neu zu erstellen:

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

   Ersetzen Sie Folgendes:

   • IP_ADDRESS: IP-Adresse der Cloud SQL for MySQL-Instanz
   • DB_NAME: Der name der Datenbank

Weitere Sicherung der Instanz erstellen

Nachdem Sie die Aktualisierungen abgeschlossen haben, erstellen Sie eine weitere Sicherung Ihrer Instanz. Weitere Informationen finden Sie unter On-Demand-Sicherung erstellen.

Beispielskripte

Wenn Sie Datenbanken, Tabellen und Spalten aktualisieren müssen, finden Sie in diesem Abschnitt Beispielscripts, mit denen Sie Ihre Daten neu codieren können.

Tipp: Sie können die Datenkonvertierung in einer Kloninstanz testen, bevor Sie die Daten in der Produktion neu codieren.

Script zum Aktualisieren mehrerer Tabellen

Mit dem folgenden Skript wird eine Anweisung für alle Tabellen in der angegebenen Datenbank generiert. Mit der Anweisung können Sie vorhandene Daten neu codieren, damit sie den neuen Zeichensatz- und Sortierungseinstellungen entsprechen.

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

Ersetzen Sie Folgendes:

• DB_NAME: Der Name der Datenbank, die aktualisiert werden soll
• NEW_CHARSET: Der neue Zeichensatz, der auf die Tabelle angewendet werden soll
• NEW_COLLATION: Die neue Sortierung, die auf die Tabelle angewendet werden soll

Beispielausgabe:

+--------------------------------------------------------------------------------------------+
|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 zum Aktualisieren mehrerer Spalten in einer Tabelle

Das folgende Skript generiert eine Anweisung für alle Spalten in einer bestimmten Tabelle. Mit der Anweisung können Sie vorhandene Daten neu codieren, damit sie den neuen Zeichensatz- und Sortierungseinstellungen entsprechen.

1. ALTER TABLE-Anweisungen für alle Tabellen in der Datenbank generieren

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

   Ersetzen Sie Folgendes:

   • DB_NAME: Der Name der Datenbank, die aktualisiert werden soll
   • NEW_CHARSET: Der neue Zeichensatz, der auf die Tabelle angewendet werden soll
   • NEW_COLLATION: Die neue Sortierung, die auf die Tabelle angewendet werden soll

   Beispielausgabe:

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

2. Generieren Sie für jede Tabelle ALTER TABLE MODIFY-Befehle.

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

   Ersetzen Sie Folgendes:

   • DB_NAME: Der Name der Datenbank, die aktualisiert werden soll
   • TABLE_NAME: Der Name der Tabelle, die aktualisiert werden soll
   • COLUMN_DEFINITION: Nachdem Sie Ergebnisse aus jeder Abfrage erhalten haben, ersetzen Sie sie durch die Spaltendefinition für jede Spalte. Spaltendefinitionen können sich zwischen Spalten unterscheiden.
   • NEW_CHARSET: Der neue Zeichensatz, der auf die Tabellenspalte angewendet werden soll
   • NEW_COLLATION: Die neue Sortierung, die auf die Tabellenspalte angewendet werden soll

   Beispielausgabe:

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

Nächste Schritte

• Cloud SQL-Instanzen überwachen
• Instanzlogs ansehen