DBAtlas · Atlas für Datenbankbetrieb

latin1- / utf8mb3-Tabellen nach utf8mb4 konvertieren

MySQLWartung

Befehl

-- Columns that are not utf8mb4
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE CHARACTER_SET_NAME IS NOT NULL AND CHARACTER_SET_NAME <> 'utf8mb4'
  AND TABLE_SCHEMA NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- Generate conversion DDL for tables whose default charset is not utf8mb4
SELECT CONCAT('ALTER TABLE `', t.TABLE_SCHEMA, '`.`', t.TABLE_NAME,
              '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, ALGORITHM=COPY, LOCK=SHARED;') AS ddl
FROM information_schema.TABLES t
JOIN information_schema.COLLATIONS c ON c.COLLATION_NAME = t.TABLE_COLLATION
WHERE c.CHARACTER_SET_NAME <> 'utf8mb4' AND t.TABLE_TYPE = 'BASE TABLE'
  AND t.TABLE_SCHEMA NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema');

-- Schema default and a single table
ALTER DATABASE legacy CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE legacy.cust CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, ALGORITHM=COPY, LOCK=SHARED;

Was es tut

Findet Spalten und Tabellen, die nicht utf8mb4 sind, und erzeugt die Konvertierungs-DDL; CONVERT TO ändert sowohl den Tabellen-Standard als auch jede Zeichenspalte und ermöglicht Emojis / 4-Byte-Zeichen.

Wann einsetzen

Bei aus 5.x migrierten Schemas, bei Fehlern 'Incorrect string value' (1366) und wenn Joins zwischen Spalten mit unterschiedlicher Collation keine Indizes nutzen können.

Vorsicht

CONVERT TO schreibt die Tabelle mit COPY neu und blockiert währenddessen Schreibzugriffe (INPLACE wird nicht unterstützt); auf großen Tabellen dasselbe --alter über pt-online-schema-change / gh-ost ausführen. utf8mb4 nutzt 4 Bytes pro Zeichen: Ein indiziertes VARCHAR(255) wird zu 1020 Bytes; das Limit liegt beim Zeilenformat DYNAMIC / COMPRESSED bei 3072 Bytes und bei COMPACT / REDUNDANT bei 767, darüber erhältst du Fehler 1071. VARCHAR- / TEXT-Spalten werden bei Bedarf auf einen größeren Typ hochgestuft (TEXT → MEDIUMTEXT), um dieselbe Zeichenkapazität zu behalten. Eine Änderung der Collation wirkt auf Sortierung und Vergleich: utf8mb4_0900_ai_ci unterscheidet weder Akzente noch Groß-/Kleinschreibung, daher können früher verschiedene Werte auf einem Unique Index kollidieren (1062). Gejointe Tabellen gemeinsam konvertieren. Auch die Client-Verbindung muss utf8mb4 verwenden.

In DBAtlas öffnen →

Verwandte Einträge