¿Cómo cambiar el CONJUNTO DE CARACTERES (y la COLECCIÓN) en toda una base de datos?
Nuestro programador anterior estableció una clasificación incorrecta en una tabla (Mysql). Lo configuró con intercalación latina, cuando debería ser UTF8, y ahora tengo problemas. Cada disco con caracteres chinos y japoneses pasa a ??? personaje.
¿Es posible cambiar la intercalación y recuperar los detalles del personaje?
cambiar la intercalación de la base de datos:
ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
cambiar la clasificación de la tabla:
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
cambiar la clasificación de columnas:
ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
¿ Qué utf8mb4_0900_ai_cisignifican las partes de?
3 bytes -- utf8
4 bytes -- utf8mb4 (new)
v4.0 -- _unicode_
v5.20 -- _unicode_520_
v9.0 -- _0900_ (new)
_bin -- just compare the bits; don't consider case folding, accents, etc
_ci -- explicitly case insensitive (A=a) and implicitly accent insensitive (a=á)
_ai_ci -- explicitly case insensitive and accent insensitive
_as (etc) -- accent-sensitive (etc)
_bin -- simple, fast
_general_ci -- fails to compare multiletters; eg ss=ß, somewhat fast
... -- slower
_0900_ -- (8.0) much faster because of a rewrite
Más información:
- ¿Cuáles son las diferencias entre utf8_general_ci y utf8_unicode_ci?
- ¿Cuál es la diferencia entre utf8_general_ci y utf8_unicode_ci?
- ¿Cómo cambiar la intercalación de la base de datos, tabla, columna?
- ¿Cuál es la diferencia entre utf8_general_ci y utf8_unicode_ci?
Aquí se explica cómo cambiar todas las bases de datos/tablas/columnas. Ejecute estas consultas y generarán todas las consultas posteriores necesarias para convertir toda su base de datos a codificación de caracteres utf8mb4y intercalaciones al valor predeterminado de MySQL 8 utf8mb4_0900_ai_ci. ¡Espero que esto ayude!
-- Cambiar la clasificación predeterminada de la BASE DE DATOS
SELECT
CONCAT('ALTER DATABASE `', SCHEMA_NAME,'` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;')
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME
NOT IN ('sys','mysql','information_schema','performance_schema','innodb')
AND SCHEMA_NAME LIKE 'database_name';
Tenga en cuenta que cambiar el valor predeterminado del esquema cambia el valor predeterminado para las tablas nuevas (y sus columnas). No modifica las columnas existentes de las tablas existentes.
-- Cambiar clasificación de TABLA / Conjunto de caracteres
SELECT
CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA
NOT IN ('sys','mysql','information_schema','performance_schema','innodb')
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA LIKE 'database_name';
Tenga en cuenta que cambiar el valor predeterminado de la tabla cambia el valor predeterminado para las nuevas columnas. No modifica las columnas existentes en las tablas existentes.
-- Cambiar clasificación de COLUMNA / Conjunto de caracteres
SELECT
CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
IF(COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT \'', COLUMN_DEFAULT, '\''), ''),
IF(IS_NULLABLE = 'YES', ' NULL ', ' NOT NULL '),
' COLLATE utf8mb4_0900_ai_ci;')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA
NOT IN ('sys','mysql','information_schema','performance_schema','innodb')
AND COLLATION_NAME IS NOT NULL
AND TABLE_SCHEMA LIKE 'database_name'
AND COLLATION_NAME = 'old_collation_name';
Esto cambia las columnas reales y el comportamiento de la base de datos en las consultas. Sin embargo, no convierte los datos si no están en un conjunto de caracteres/clasificación compatible. Consulte https://dev.mysql.com/blog-archive/mysql-8-0-collations-migrating-from-older-collations/ para obtener detalles sobre cómo migrar desde intercalaciones anteriores. También asumimos aquí que sus valores predeterminados no incluyen una comilla simple (debería escaparse) y nos aseguramos de que COLLATION_NAMEno se NULLexcluyan columnas con números enteros, marcas de tiempo, etc.
Filtramos los esquemas integrados del sistema como sysy mysqlen los tres casos, ya que probablemente no deberían modificarse a menos que tenga una razón explícita para hacerlo.
