DBAtlas · Atlas de operaciones de bases de datos

Convertir tablas latin1 / utf8mb3 a utf8mb4

MySQLMantenimiento

Comando

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

Qué hace

Encuentra columnas y tablas que no son utf8mb4 y genera el DDL de conversión; CONVERT TO cambia tanto el valor por defecto de la tabla como todas las columnas de caracteres, habilitando emojis / caracteres de 4 bytes.

Cuándo usarlo

En esquemas migrados desde 5.x, ante errores 'Incorrect string value' (1366) y cuando los joins entre columnas con collations distintas no pueden usar índices.

Precaución

CONVERT TO reescribe la tabla con COPY y bloquea las escrituras mientras dura (INPLACE no está soportado); en tablas grandes ejecute el mismo --alter mediante pt-online-schema-change / gh-ost. utf8mb4 usa 4 bytes por carácter: un VARCHAR(255) indexado pasa a 1020 bytes; el límite es de 3072 bytes con el row format DYNAMIC / COMPRESSED y de 767 con COMPACT / REDUNDANT, por encima del cual obtiene el error 1071. Las columnas VARCHAR / TEXT se promueven a un tipo mayor (TEXT → MEDIUMTEXT) cuando hace falta para mantener la misma capacidad de caracteres. Un cambio de collation afecta a la ordenación y la comparación: utf8mb4_0900_ai_ci no distingue acentos ni mayúsculas, así que valores antes distintos pueden colisionar en un índice único (1062). Convierta juntas las tablas que se combinan en joins. La conexión del cliente también debe usar utf8mb4.

Abrir en DBAtlas →

Relacionado