DBAtlas · Atlas d'exploitation des bases de données

Convertir les tables latin1 / utf8mb3 en utf8mb4

MySQLMaintenance

Commande

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

Ce que fait la commande

Trouve les colonnes et tables qui ne sont pas en utf8mb4 et génère le DDL de conversion ; CONVERT TO modifie à la fois la valeur par défaut de la table et chaque colonne de caractères, ce qui permet les emoji / caractères sur 4 octets.

Quand l'utiliser

Sur les schémas migrés depuis 5.x, sur les erreurs 'Incorrect string value' (1366), et quand les jointures entre colonnes de collations différentes ne peuvent pas utiliser les index.

Attention

CONVERT TO réécrit la table en COPY et bloque les écritures pendant toute la durée (INPLACE n'est pas pris en charge) ; sur les grandes tables, exécutez le même --alter via pt-online-schema-change / gh-ost. utf8mb4 utilise 4 octets par caractère : un VARCHAR(255) indexé fait 1020 octets ; la limite est de 3072 octets avec le row format DYNAMIC / COMPRESSED et de 767 avec COMPACT / REDUNDANT, au-delà de quoi vous obtenez l'erreur 1071. Les colonnes VARCHAR / TEXT sont promues vers un type plus grand (TEXT → MEDIUMTEXT) si nécessaire pour garder la même capacité en caractères. Un changement de collation affecte le tri et la comparaison : utf8mb4_0900_ai_ci est insensible aux accents et à la casse, donc des valeurs auparavant distinctes peuvent entrer en collision sur un index unique (1062). Convertissez ensemble les tables jointes. La connexion cliente doit aussi utiliser utf8mb4.

Ouvrir dans DBAtlas →

Voir aussi