mysql - puede - ¿Cómo cambiar la intercalación predeterminada de una base de datos?

tipos de intercalacion sql server

Question

Nuestro programador anterior estableció la intercalación incorrecta en una tabla (Mysql). Lo configuró con colación latina, cuando debería ser UTF8, y ahora tengo problemas. Todos los registros con caracteres chinos y japoneses se convierten en ??? personaje.

¿Es posible cambiar la intercalación y recuperar el detalle del personaje?

Answer 1

Agregando a lo que publicó David Whittaker, he creado una consulta que genera la tabla completa y las columnas alteran la declaración que convertirá cada tabla. Puede ser una buena idea correr

SET SESSION group_concat_max_len = 100000;

primero para asegurarse de que su grupo concat no sobrepase el límite muy pequeño como se ve here .

 SELECT a.table_name, concat(''ALTER TABLE '', a.table_schema, ''.'', a.table_name, '' DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci, '',
group_concat(distinct(concat('' MODIFY '', column_name, '' '', column_type, '' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci '', if (is_nullable = ''NO'', '' NOT'', ''''), '' NULL '',
if (COLUMN_DEFAULT is not null, CONCAT('' DEFAULT /''', COLUMN_DEFAULT, ''/'''), ''''), if (EXTRA != '''', CONCAT('' '', EXTRA), '''')))), '';'') as alter_statement
FROM information_schema.columns a
INNER JOIN INFORMATION_SCHEMA.TABLES b ON a.TABLE_CATALOG = b.TABLE_CATALOG
AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND b.table_type != ''view''
WHERE a.table_schema = ? and (collation_name = ''latin1_swedish_ci'' or collation_name = ''utf8mb4_general_ci'')
GROUP BY table_name;

Una diferencia aquí entre la respuesta anterior es que usaba utf8 en lugar de ut8mb4 y el uso de t1.data_type con t1.CHARACTER_MAXIMUM_LENGTH no funcionaba para las enumeraciones. Además, mi consulta excluye las vistas, ya que deberán modificarse por separado.

Simplemente utilicé una secuencia de comandos de Perl para devolver todas estas alteraciones como una matriz e iterar sobre ellas, arreglé las columnas que eran demasiado largas (generalmente eran varchar (256) cuando los datos generalmente solo tenían 20 caracteres, por lo que era una solución fácil )

Encontré algunos datos dañados cuando alteraba de latin1 -> utf8mb4. Parecía que los caracteres latin1 codificados en utf8 en las columnas serían ridículos en la conversión. Simplemente mantuve los datos de las columnas que sabía que iban a ser un problema en la memoria de antes y después del alter y los comparé y generé declaraciones de actualización para corregir los datos.

Answer 2

Heres cómo cambiar todas las bases de datos / tablas / columnas. Ejecute estas consultas y generarán todas las consultas posteriores necesarias para convertir su esquema completo a utf8. ¡Espero que esto ayude!

- Cambiar la compilación predeterminada de BASE DE DATOS

SELECT DISTINCT concat(''ALTER DATABASE `'', TABLE_SCHEMA, ''` CHARACTER SET utf8 COLLATE utf8_unicode_ci;'')
from information_schema.tables
where TABLE_SCHEMA like ''database_name'';

- Cambiar clasificación TABLE / conjunto de caracteres

SELECT concat(''ALTER TABLE `'', TABLE_SCHEMA, ''`.`'', table_name, ''` CHARACTER SET utf8 COLLATE utf8_unicode_ci;'')
from information_schema.tables
where TABLE_SCHEMA like ''database_name'';

- Cambiar COLUMN Collation / Char Set

SELECT concat(''ALTER TABLE `'', t1.TABLE_SCHEMA, ''`.`'', t1.table_name, ''` MODIFY `'', t1.column_name, ''` '', t1.data_type , ''('' , t1.CHARACTER_MAXIMUM_LENGTH , '')'' , '' CHARACTER SET utf8 COLLATE utf8_unicode_ci;'')
from information_schema.columns t1
where t1.TABLE_SCHEMA like ''database_name'' and t1.COLLATION_NAME = ''old_charset_name'';

Answer 3

Tenga en cuenta que en Mysql, el utf8 caracteres utf8 es solo un subconjunto del juego de caracteres UTF8 real. Para guardar un byte de almacenamiento, el equipo Mysql decidió almacenar solo tres bytes de caracteres UTF8 en lugar de los cuatro bytes completos. Eso significa que algunos idiomas asiáticos y emojis no son totalmente compatibles. Para asegurarse de que puede almacenar todos los caracteres UTF8, utilice el tipo de datos utf8mb4_bin y utf8mb4_bin o utf8mb4_general_ci en Mysql.

Answer 4

cambiar la recopilación de la base de datos:

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

cambiar la clasificación de la tabla:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

cambiar la intercalación de columnas:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

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?

Answer 5

here describe bien el proceso. Sin embargo, algunos de los personajes que no encajaban en el espacio latino se han ido para siempre. UTF-8 es un SUPERSET de latin1. No al revés. La mayoría encajará en el espacio de un solo byte, pero los indefinidos no (consulte una lista de latin1; no se definen todos los 256 caracteres, según la definición de mysql en latin1)