¿Cómo convertir un conjunto de caracteres completo de la base de datos MySQL y la intercalación a UTF-8?

¿Cómo puedo convertir todo el conjunto de caracteres de la base de datos MySQL a UTF-8 y la intercalación con UTF-8?

Utilice los comandos ALTER DATABASE y ALTER TABLE .

 ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 

O si todavía está utilizando MySQL 5.5.2 o una versión anterior que no admite el UTF-8 de 4 bytes, use utf8 lugar de utf8mb4 :

 ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; 
  1. ¡Hacer una copia de seguridad!

  2. Luego necesita establecer los conjuntos de caracteres predeterminados en la base de datos. Esto no convierte las tablas existentes, solo establece el valor predeterminado para las tablas recién creadas.

     ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci; 
  3. Luego, deberá convertir el conjunto de caracteres en todas las tablas existentes y sus columnas. Esto supone que sus datos actuales se encuentran actualmente en el conjunto de caracteres actual. Si sus columnas están configuradas para un conjunto de caracteres, pero sus datos están realmente almacenados en otro, deberá consultar el manual de MySQL para saber cómo manejarlo.

     ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 

En el shell de línea de comando

Si eres uno de los shell de línea de comandos, puedes hacerlo muy rápidamente. Solo completa “dbname”: D

 DB="dbname" ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;' mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names \ | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) \ | mysql "$DB" 

One-liner para copiar / pegar

 DB="dbname"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'; mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql "$DB" 

Puede crear el sql para actualizar todas las tablas con:

 SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8 COLLATE utf8_general_ci; ", "ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ") AS alter_sql FROM information_schema.TABLES WHERE TABLE_SCHEMA = your_database_name; 

Capture la salida y ejecútela.

La respuesta de Arnold Daniels es más elegante.

Antes de continuar, asegúrese de: Haber completado una copia de seguridad completa de la base de datos.

Paso 1: Cambios de nivel de base de datos

  • Identificar el conjunto de intercalación y caracteres de su base de datos

     SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA S WHERE schema_name = 'your_database_name' AND (DEFAULT_CHARACTER_SET_NAME != 'utf8' OR DEFAULT_COLLATION_NAME not like 'utf8%'); 
  • Corregir la intercalación de la base de datos

     ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

Paso 2: Cambios a nivel de tabla

  • Identificación de tablas de base de datos con el conjunto de caracteres incorrectos o colación

     SELECT CONCAT( 'ALTER TABLE ', table_name, ' CHARACTER SET utf8 COLLATE utf8_general_ci; ', 'ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ') FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C WHERE C.collation_name = T.table_collation AND T.table_schema = 'your_database_name' AND (C.CHARACTER_SET_NAME != 'utf8' OR C.COLLATION_NAME not like 'utf8%') 
  • Ajuste de colación y conjunto de caracteres de las columnas de la tabla

Capture la salida sql superior y ejecútela. (como el siguiente)

 ALTER TABLE rma CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE rma_history CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE rma_products CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_products CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE rma_report_period CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_report_period CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE rma_reservation CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_reservation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE rma_supplier_return CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE rma_supplier_return_history CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE rma_supplier_return_product CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return_product CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 

consulte: https://confluence.atlassian.com/display/CONFKB/How+to+Fix+the+Collation+and+Character+Set+of+a+MySQL+Database

Use HeidiSQL . Es gratis y una muy buena herramienta db.

Desde el menú de herramientas, ingrese el editor de tablas masivas

Seleccione la base de datos completa o elija las tablas para convertir,

  • marque Cambiar colación predeterminada: utf8mb4_general_ci
  • tick Convertir a juego de caracteres: utf8

Ejecutar

Esto convierte una base de datos completa de latin a utf8 en solo unos segundos.

Funciona de maravilla 🙂

HeidiSQL se conecta por defecto como utf8, por lo que cualquier carácter especial debería verse como el carácter (æ ø) y no codificado al inspeccionar los datos de la tabla.

El verdadero escollo al pasar de latín a utf8 es asegurarse de que pdo se conecta con utf8 charset. Si no, obtendrás datos de basura insertados en la tabla utf8 y signos de interrogación en tu página web, lo que hace que pienses que los datos de la tabla no son utf8 …

Inspirado por @sdfor comment, aquí hay un script bash que hace el trabajo

 #!/bin/bash printf "### Converting MySQL character set ###\n\n" printf "Enter the encoding you want to set: " read -r CHARSET # Get the MySQL username printf "Enter mysql username: " read -r USERNAME # Get the MySQL password printf "Enter mysql password for user %s:" "$USERNAME" read -rs PASSWORD DBLIST=( mydatabase1 mydatabase2 ) printf "\n" for DB in "${DBLIST[@]}" do ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE `'"$CHARSET"'`;' mysql "$DB" -u"$USERNAME" -p"$PASSWORD" -e "SHOW TABLES" --batch --skip-column-names \ | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE `'"$CHARSET"'`;' ) \ | mysql "$DB" -u"$USERNAME" -p"$PASSWORD" echo "$DB database done..." done echo "### DONE ###" exit 

En caso de que los datos no estén en el mismo conjunto de caracteres, puede considerar este fragmento de http://dev.mysql.com/doc/refman/5.0/es/charset-conversion.html

Si la columna tiene un tipo de datos no binarios (CHAR, VARCHAR, TEXT), su contenido debe estar codificado en el conjunto de caracteres de la columna, no en otro juego de caracteres. Si los contenidos están codificados en un juego de caracteres diferente, puede convertir la columna para usar primero un tipo de datos binarios y luego a una columna no binaria con el juego de caracteres deseado.

Aquí hay un ejemplo:

  ALTER TABLE t1 CHANGE c1 c1 BLOB; ALTER TABLE t1 CHANGE c1 c1 VARCHAR(100) CHARACTER SET utf8; 

Asegúrese de elegir la intercalación correcta, o puede obtener conflictos clave únicos. por ejemplo, Éleanore y Eleanore podrían considerarse iguales en algunas colaciones.

Aparte:

Tuve una situación en la que ciertos caracteres “se rompieron” en los correos electrónicos a pesar de que se almacenaron como UTF-8 en la base de datos. Si está enviando correos electrónicos usando datos utf8, también puede querer convertir sus correos electrónicos para enviarlos en UTF8.

En PHPMailer, simplemente actualice esta línea: public $CharSet = 'utf-8';

Para las bases de datos que tienen un gran número de tablas, puede usar un script php simple para actualizar el conjunto de caracteres de la base de datos y todas las tablas usando lo siguiente:

 $conn = mysqli_connect($host, $username, $password, $database); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $alter_database_charset_sql = "ALTER DATABASE ".$database." CHARACTER SET utf8 COLLATE utf8_unicode_ci"; mysqli_query($conn, $alter_database_charset_sql); $show_tables_result = mysqli_query($conn, "SHOW TABLES"); $tables = mysqli_fetch_all($show_tables_result); foreach ($tables as $index => $table) { $alter_table_sql = "ALTER TABLE ".$table[0]." CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci"; $alter_table_result = mysqli_query($conn, $alter_table_sql); echo "
"; var_dump($alter_table_result); echo "

"; }

 mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql cp dump.sql dump-fixed.sql vim dump-fixed.sql :%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/ :%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/ :wq mysql -uusername -ppassword < dump-fixed.sql 

Si no puede hacer que sus tablas se conviertan o su tabla siempre está configurada para un conjunto de caracteres que no sean utf8, pero quiere utf8, su mejor opción podría ser borrarlo y comenzar de nuevo y especificar explícitamente:

 create database database_name character set utf8; 

La única solución que funcionó para mí: http://docs.moodle.org/23/en/Converting_your_MySQL_database_to_UTF8

Conversión de una base de datos que contiene tablas

 mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql cp dump.sql dump-fixed.sql vim dump-fixed.sql :%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/ :%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/ :wq mysql -uusername -ppassword < dump-fixed.sql 

alter table table_name charset = ‘utf8’;

Esta es una consulta simple que pude usar para mi caso, puede cambiar el nombre de la tabla según sus requisitos.

Para cambiar la encoding del conjunto de caracteres a UTF-8 para la base de datos misma, escriba el siguiente comando en el indicador mysql>. Reemplace DBNAME con el nombre de la base de datos:

 ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci; 
 DELIMITER $$ CREATE PROCEDURE `databasename`.`update_char_set`() BEGIN DECLARE done INT DEFAULT 0; DECLARE t_sql VARCHAR(256); DECLARE tableName VARCHAR(128); DECLARE lists CURSOR FOR SELECT table_name FROM `information_schema`.`TABLES` WHERE table_schema = 'databasename'; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN lists; FETCH lists INTO tableName; REPEAT SET @t_sql = CONCAT('ALTER TABLE ', tableName, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci'); PREPARE stmt FROM @t_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; FETCH lists INTO tableName; UNTIL done END REPEAT; CLOSE lists; END$$ DELIMITER ; CALL databasename.update_char_set(); 

La forma más segura es modificar las columnas primero a un tipo binario y luego modificarlas de nuevo con el conjunto de caracteres deseado.

Cada tipo de columna tiene su tipo binario respectivo, de la siguiente manera:

  1. CHAR => BINARY
  2. TEXTO => BLOB
  3. TINYTEXT => TINYBLOB
  4. MEDIUMTEXT => MEDIUMBLOB
  5. LONGTEXT => LONGBLOB
  6. VARCHAR => VARBINARIO

P.ej.:

 ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARBINARY; ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARCHAR(140) CHARACTER SET utf8mb4; 

Intenté en varias tablas latin1 y mantuvo todos los signos diacríticos.

Puede extraer esta consulta para todas las columnas que hacen esto:

 SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY ', COLUMN_NAME,' VARBINARY;'), CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY ', COLUMN_NAME,' ', COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') FROM information_schema.columns WHERE TABLE_SCHEMA IN ('[TABLE_SCHEMA]') AND COLUMN_TYPE LIKE 'varchar%' AND (COLLATION_NAME IS NOT NULL AND COLLATION_NAME NOT LIKE 'utf%'); 

Después de hacer esto en todas sus columnas, entonces lo hace en todas las tablas:

 ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 

Para generar esta consulta para toda su tabla, use la siguiente consulta:

 SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_COLLATION NOT LIKE 'utf8%' and TABLE_SCHEMA in ('[TABLE_SCHEMA]'); 

Y ahora que ha modificado todas sus columnas y tablas, haga lo mismo en la base de datos:

 ALTER DATABASE [DATA_BASE_NAME] CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci; 

También puede utilizar la herramienta DB Navicat, que lo hace más fácil.

  • Siva.

Haga clic con el botón derecho en su base de datos y seleccione Propiedades de DB y cambie como desee en desplegable

enter image description here