Konvertieren von Daten von latin1 nach utf8mb4

Warum

Um eine größere Bandbreite an Daten speichern zu können, wobei internationale Symbole unterstützt werden, müssen wir Daten von latin1 konvertieren, welches ein traditioneller Zeichensatz ist. Allerdings ist utf8 kein empfohlenes Best Practice. Derzeit ist das beste Vorgehen, Daten nach utf8mb4 zu konvertieren.

Das Konvertieren neuer Daten

Bevor wir damit beginnen, reelle Daten nach utf8mb4 zu konvertieren, müssen wir die Datenbank konvertieren, was einen Einfluss auf alle Tabellen und Spalten haben wird, die später während des Konvertierens von Tabellen und Spalten hinzugefügt werden.

ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci;

Als nächsten Schritt müssen wir dasselbe auf Tabellenebene tun, d. h. alle neu erstellten Spalten haben bereits den richtigen Zeichensatz, während wir die schon vorhandenen konvertieren werden.

ALTER TABLE for_each_table_in_db CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Für jede Datenbank und für jede Tabelle in der Datenbank musst du die oben angeführten Statements ausführen.

Das Konvertieren vorhandener Daten

Jetzt sind wir soweit, die vorhandenen Daten zu konvertieren.

Um dies zu tun, müssen wir für jede Datenbank und jede Tabelle die folgenden Statements ausführen:

select table_schema, table_name, column_name, data_type, character_set_name, collation_name 
from information_schema.columns 
where 
   table_schema = 'db_name' 
   and table_name = 'tbl_name' 
   and data_type in ('varchar', 'char', 'text', 'tinytext', 'mediumtext', 'longtext');

Falls du Spalten findest, welche die genannten Datentypen und Zeichensätze enthalten, welche du konvertieren musst (latin1 oder auch etwas anderes von dir Verwendetes), musst du solche Spalten konvertieren, indem du Alter-Statements ausführst.

Beachte, dass wir keine blob-basierten Felder abändern müssen, weil diese als “raw”-Daten ohne Kodierung gespeichert sind.

Das nächste Statement hilft uns, eine einzelne Spalte zu konvertieren (bitte beachte, dass der Datentyp varchar(10) nur ein Beispiel ist):

ALTER TABLE table_name CHANGE column_name column_name varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci

Automatisierungsmöglichkeiten

Normalerweise haben wir drei Möglichkeiten, eine Tabelle zu verändern:

• indem wir Statements direkt ausführen;
• indem wir pt-osc laufen lassen;
• indem wir Veränderungen an einem Replikat mit folgendem Failover ausführen.

Leider funktioniert die dritte Möglichkeit für uns in diesem Fall aufgrund von Replikationseinschränkungen nicht (es sind sowohl Mysql als auch MariaDB betroffen, weshalb der Wechsel von einem zum anderen nichts bringt). 

Es ist nicht möglich, Restriktionen, wie sie in https://dev.mysql.com/doc/refman/8.0/en/replication-features-differing-tables.html erwähnt werden, vorzunehmen (wie oben erwähnt gilt das gleiche für MariaDB). Alles, was nicht erlaubt ist, ist restringiert. Und eine Replikation klappt nicht, wenn wir verschiedene Zeichensätze haben.

Die nächsten beiden Möglichkeiten funktionieren für uns:

1. Ein direktes ALTER ist in Ordnung, wenn wir uns eine gewisse Ausfallzeit erlauben können, während der die Tabelle gesperrt sein wird;
2. pt-osc auszuführen hat seine Vorteile, weil wir Tabellen ohne Ausfallzeit abändern können. Bitte beachte, dass wir verschiedene Spalten haben, die wir konvertieren müssen, und verschiedene ALTER  zu einer kombinieren können.

ALTER TABLE table_name
   CHANGE c1 c1 varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
   CHANGE c2 c2 varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
   ...
   CHANGE cN cN varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Hier ein Beispiel, wie wir pt-osc ausführen können, um eine Tabelle abzuändern:

pt-online-schema-change --user=<username> --password=<password> --alter "CHANGE c1 c1 varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci" D=db_name,t=tbl_name --execute

Finaler Test

Als Test können wir zum Schluss das gleiche Statement ausführen, welches wir ausgeführt haben, als wir die Spalten, die konvertiert werden sollten, extrahiert haben, um sicherzustellen, dass alle Spalten den gewünschten Zeichensatz haben.