DBAtlas · Database operations atlas

Convert latin1 / utf8mb3 tables to utf8mb4

MySQLMaintenance

Command

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

What it does

Finds columns and tables that are not utf8mb4 and generates the conversion DDL; CONVERT TO changes both the table default and every character column, enabling emoji / 4-byte characters.

When to use it

On schemas migrated from 5.x, on 'Incorrect string value' (1366) errors, and when joins between columns with different collations cannot use indexes.

Caution

CONVERT TO rewrites the table with COPY and blocks writes for the duration (INPLACE is not supported); on large tables run the same --alter through pt-online-schema-change / gh-ost. utf8mb4 uses 4 bytes per character: an indexed VARCHAR(255) becomes 1020 bytes; the limit is 3072 bytes with the DYNAMIC / COMPRESSED row format and 767 with COMPACT / REDUNDANT, beyond which you get error 1071. VARCHAR / TEXT columns are promoted to a larger type (TEXT → MEDIUMTEXT) when needed to keep the same character capacity. A collation change affects sorting and comparison: utf8mb4_0900_ai_ci is accent and case insensitive, so values that used to differ may collide on a unique index (1062). Convert joined tables together. The client connection must use utf8mb4 too.

Open in DBAtlas →

Related