Hacer cambios en la base de datos, es ese tipo de cosas que rehúye la mayoría de la gente, sobre todo si hablamos de cambiar charset y cotejamiento de Utf8 a Utf8mb4.
Pero es necesario si necesitamos implementar algunas mejoras en nuestra web, y en muchos casos es obligatoria al momento de actualizar nuestro CMS.
En este artículo te enseñaré cómo convertir tu base de datos de Utf8 a Utf8mb4 de forma sencilla y práctica, sin que sea necesario ser un experto en MySQL o MariaDB 😉.
Contenido del post
¿Por qué es necesario cambiar a Utf8mb4?
Porque la mayoría de los CMS lo necesitan para las nuevas implementaciones y mejoras.
Esta respuesta te puede parecer simple, pero si entiendes que son los juegos de caracteres y el cotejamiento, lo comprenderás mejor.
Juego de caracteres o Charset
En una base de datos se almacenan caracteres (cada letra, número, símbolo, es un carácter), y estos deben ser reconocibles por quien los lee.
Como existen miles de caracteres en el mundo y su uso varía dependiendo de la región geográfica, lengua, etc. debemos colocarle a nuestra base de datos un charset para que admita los caracteres que nos interesa.
Por ejemplo, si hablas chino mandarín, querrás tener un charset que admita los caracteres propios de esta lengua. En el caso del español, es necesario que la base de datos reconozca la ñ o las tildes, por ejemplo.
Teniendo esto claro, verás la importancia de escoger el charset adecuado al momento de crear una base de datos, ya que después de hacer la conversión puede ser algo complejo.
¡Pero, tranquilo!, que no es nada del otro mundo, para eso está este post. 😀
Como las bases de datos están en constante mejora, hace algunos años el Utf8 era el recomendado, pero ahora el Utf8mb4 es lo actual. Además, son totalmente compatibles.
Los CMS actuales para agregar nuevas funcionalidades requieren usar el Utf8mb4. Un ejemplo típico de esto, son los emojis, sin él no tendrás soporte completo para ellos.
Estos emojis no podrás colocarlos en tu web: 😎🤗👻 si el juego de caracteres del campo donde se almacenan es Utf8.
Cotejamiento de una base de datos
El cotejamiento son las reglas que permiten comparar caracteres en la base de datos y como se ordenan.
Por ejemplo: el manejo de las doble l: “LL”, se manejará y ordenará diferente dependiendo del cotejamiento que tengamos.
El cotejamiento está relacionado con el charset que tengamos. Por ejemplo:
Charset: Utf8mb4 ➡ Cotejamiento: Utf8mb4_unicode_520_ci
MyISAM o InnoDB
MyISAM e InnoDB son dos populares motores de almacenamiento en las bases de datos.
A partir de la versión 5.6 de MySQL, InnoDB ofrece un mejor rendimiento y soporte nativo para búsqueda de texto completo, por lo que en mi opinión ya no vale la pena permanecer en MyISAM.
En lo particular trabajo con InnoDB y para esta guía te mostraré como migrar hacia él si tienes MyISAM.
Formato de tablas en MySQL
Esto se está poniendo complejo 🥵, pero espera, que ya terminamos la teoría.
El formato de tablas usado en MySQL o MariaDB, es otro aspecto relevante en el cambio de Utf8 a Utf8mb4.
En MySQL o MariaDB los formatos de las tablas usadas son: Antelope o Barracuda.
Con Antelope la máxima longitud en bytes que puede tener las columnas e índices es de 767.
Utf8mb4 soporta hasta 4 bytes en cada carácter, cuando lo multiplicamos por los 255 máximos que pueden tener las columnas, nos excederemos si tenemos Antelope (4×255= 1020 > 767). Con una longitud de 191 estaremos justo por debajo en su máxima capacidad (4×191=764 < 767).
Esto significa, que si tenemos Antelope deberemos cambiar a 191 aquellos índices que excedan esta cifra.
Con barracuda no tendremos este problema, ya que soporta 3072 bytes . Pero cambiarlo dependerá de si tienes acceso a modificar el formato de archivos de MySQL o MariaDB en tu servidor.
¿Es necesario migrar a Utf8mb4?
No, no es obligatorio ni necesario. Si tu web funciona bien, y tu CMS no te pide que lo cambie, puedes permanecer con él.
Lo que si puede ocurrir es que estés limitado de cara al futuro, y mientras más crece tu base de datos con la adición de nuevas columnas y campos, más complejo será hacer el cambio cuando lo necesites.
Es tu elección: quedarte anclado al presente, o trabajar para el futuro. ¡Oye, sin presión! 😁.
Ejemplo de esto es Moodle, que en su versión 3.3 requiere este tipo de cotejamiento y charset para su funcionamiento ordinario.
¡Adivina! ¿qué soporte ofrecerá en esta versión?. Pues sí, soporte completo para emojis 😅.
WordPress desde la versión 4.2 cambio su cotejamiento de Utf8 a Utf8mb4.
Después de este montón de explicaciones técnicas, vamos por lo que viniste. Aprieta el cinturón porque vienen curvas.
Haz un respaldo de tu base de datos, por si falla algo. Te recomiendo que hagas pruebas en un localhost y después la ejecutes en tu servidor en producción. Te dejo esta guía que puede ser de ayuda.
Si vas a ejecutar los cambios a tu servidor en producción (funcionamiento en la web) recomiendo ponerlo en mantenimiento mientras lo ejecutas. En WordPress este plugin cumple con esa misión.
Cambiando el formato de tablas en MySQL o MariaDB
Para saber si tu servidor tiene el formato de tablas Barracuda, entra en el phpMyAdmin de tu servidor. Dirígete a la pestaña SQL y en el campo en blanco escribe:
show variables like "%innodb_file%";
Dale clic al botón Continuar.
Si en pantalla te muestra innodb_file_format Antelope y innodb_file_format_max Antelope, debemos cambiarlo a Barracuda.
Te recomiendo consultes con tu hosting para obtener información.
En servidores auto administrados, como los VPS, accede a la línea de comando mediante SSH.
Logueate como root en tu servidor, luego entrar a MySQL con este comando:
mysql -u root –p
Te pedirá la contraseña del root de MySQL.
Luego debemos ubicar el archivo my.cnf (dependerá del tipo y versión del sistema operativo de tu servidor). En Debían está ubicado en /etc/mysql/
Lo editamos con el comando: nano my.cnf
Ubica la línea [mysqld] y debajo copia este código:
innodb_file_format = Barracuda innodb_large_prefix = 1 innodb_file_per_table = 1 innodb_file_format_max = Barracuda default-storage-engine = InnoDB
Guarda los cambios con las teclas: Ctrl + O y confirmándolo con la tecla Enter.
Reinicia MySQL con el comando: /etc/init.d/mysqld restart
Ahora entra nuevamente en phpMyAdmin y en la pestaña SQL colocamos nuevamente:
show variables like "%innodb_file%";
Si todo salió bien deberás ver ahora: innodb_file_format e innodb_file_format_max con Barracuda.
Cambiando el motor de búsqueda: de MyISAM a InnoDB
Vamos a phpMyAdmin, en la pestaña SQL, introduce esto:
SET @DATABASE_NAME = 'nombre_bd';
SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema = @DATABASE_NAME
AND `ENGINE` = 'MyISAM'
AND `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;Sustituye nombre_bd por el nombre de la base de datos que quieras modificar. Cuida de no borrar las comillas.
Luego haz clic a Continuar.
En la pantalla resultante, en sql_statements nos mostrará las tablas que tengan el motor de búsqueda MyISAM junto con las líneas de código para cambiarlo a InnoDB (si lo ves incompleto anda aquí).
Copia este código.
Selecciona la base de datos a modificar del menú a la izquierda. Vamos ahora nuevamente a la pestaña SQL (siempre con la base de datos seleccionada) y pega en el espacio en blanco el código copiado. Dale al botón Continuar.
Espera mientras se ejecuta el cambio, y al finalizar debemos tener toda nuestra base de datos con el nuevo motor de búsqueda.
Compruébalo seleccionando la base de datos; en la columna Tipo, deben mostrarse todas con InnoDB.
Convirtiendo el juego de caracteres y cotejamiento de Utf8 a Utf8mb4
El charset y cotejamiento lo debemos cambiar en la base de datos, así como en cada tabla y columna.
Primero lo cambiaremos para la base de datos globalmente. Pon este código en la pestaña SQL:
ALTER DATABASE nombre_bd CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci;
Cambia nombre_bd por el nombre de tu base de datos. Haz clic a Continuar.
En segundo lugar, lo haremos en las tablas y columnas. Vamos nuevamente a la pestaña SQL, copiamos este código y lo ejecutamos con Continuar.
SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci; ')
AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'nombre_bd';Como antes, reemplaza nombre_bd con el nombre tu base de datos y dale a Continuar. Obtendrás unos comandos que deberás copiar.
Es posible que solo los veas parcialmente:
Para verlos completos, haciendo clic al vínculo Opciones (visible encima de los resultados), desplegarás un menú, selecciona la casilla Textos completos y dale clic a Continuar.
Pega los comandos en la pestaña SQL y como siempre, clic a Continuar (no se ni por qué te lo digo otra vez, debes suponerlo 😄).
¡Y ya está!, habremos cambiado el charset y cotejamiento a Utf8mb4 de toda nuestra base de dato.
Ajustando el tamaño máximo de las columnas en las tablas
Realizar esta parte dependerá de si cambiaste el formato de tablas en MySQL o MariaDB de Antelope a Barracuda. De ser así, obvia este paso.
Si, por el contrario, no fue posible y se mantiene en Antelope, vamos a modificar la longitud del Varchar en cada tabla.
Todo varchar que tenga más de 191, lo llevaremos a este número.
Este proceso es un poco tedioso 🙂, pero muy importante para evitar errores en la base de datos. Existen script para ejecutarlos, pero prefiero hacerlo manualmente, ya que es un proceso delicado.
En phpMyAdmin, selecciona la base de datos y descolgamos el menú haciendo clic en el símbolo + colocado a la izquierda del nombre; esta acción nos mostrará todas las tablas que contiene.
Ahora, comenzando con la primera tabla; descuelga el menú haciendo clic otra vez en +, para que se muestre las columnas e índices.
Selecciona Columnas (estas se mostraran a la derecha). Solo debemos modificar aquellas que sean de tipo varchar y mayor a 191.
Por ejemplo, si el varchar de la columna es 200 llévalo a 191.
Para hacer esto, haz clic al vínculo Cambiar (tiene un lápiz al lado) correspondiente.
En la próxima pantalla, debajo de Longitud/Valores, cambia el valor a 191 y presiona Guardar.
Deberás repetir esto tantas veces sea necesario en cada tabla y cada columna de la base de datos.
¡Listo!, con este ultimo paso, ya tenemos nuestra base de datos con Utf8mb4; preparada para lo que venga 😉 .
Espero te ayude. Cualquier duda déjalo en los comentarios. Saludos.
Fundador de VivaBlogger y cofundador de EVirtualplus. Soy un tecnomaniaco desde que estaba en pañales. Mi familia es el fundamento de mi vida. Sígueme en mis redes sociales.
Muchas gracias, en mi caso tenía una web de hace bastante tiempo e imagino que la BD estaba desfasada. Muy bien explicado y muy didáctico!
Gracias!
Hola Marc.
Me alegra ser de ayuda.
Saludos.
perfecto, me sirvio demasiado tu solución, no hallaba por donde evitar que me destruyera la sesión al exportar gran cantidad de información en una pagina php hacia un archivo xls estaba con un cotejamiento UTF8_general_ci (max exportaba 4000 registros) y lo cambie por un UTF8MB4_general_ci (ahora exporto mas de 20 000 registros) y me exporta la cantidad de información que yo necesito.
Muchas Gracias
Que bien que todo salio perfecto 🤩
Saludos Daniel.
Muy interesante y muy detallado (excelente) cada paso con imágenes GIF capturadas. Gracias por compartir.
Te quería comentar que creo que sería muy importante un paso adicional ANTES de cambiar los cotejamientos, o así lo entiendo yo, y es “por si las moscas” desactivar el chequeo de claves foráneas (si las hubiera, pero no está mal por si acaso no se está seguro):
SET foreign_key_checks = 0;
y volver a activarlo al acabar con todo el proceso
SET foreign_key_checks = 1;
Hola Rafael.
Interesante comentario para aquellos que pudieran tener problemas con las claves foráneas al hacer cambios en MySQL.
Saludos.
Muchas gracias por el aporte Diógenes!!
Me alegra que te haya gustado 😉
Como estas? Puede ser que no pueda cambiar el cotejamiento debido a mi versión de mysql?
Versión mysql: 5.5.43
Phpmyadmin: versión: 4.0.8
Hola Ezequiel.
No deberías tener ningún problema.
Saludos.
Hola, gracias gran tutorial, pero tengo un problema que no puedo resolver, y es que hay veces que si inserta los emojis en la base de datos y todo se ve muy en la página y otras veces me salen estos simbolos (?????) en la base de datos , sabes a que se deba?
Hola Frank.
Utf8mb4 es un cotejamiento que soporta emojis.
Tendría que examinar la base de datos para poder llegar a una conclusión certera sobre tu problema.
Para insertar emojis, los tomo directamente de la pagina de unicode, copiandolos directamente de la columna Browse, y pegandolos en mi web.
Saludos.
Hola, gracias por responder ya pude solucionar el problema, solo quite el SET NAMES utf8 que tenia en el query y lo remplase por set_charset Utf8mb4
Me alegra Frank 🙂
Hola.
Estoy trasladando una instalación moodle de la 2.2 a la 3.3 y estoy tratando de resolver no sólo los problemas que me impiden continuar, sino las advertencias. En mi último paso, para llegar a la versión 3.3 me sigue advirtiendo que debo cambiar a utf8mb4 y he seguido todos los pasos que tan bien has expuesto en tus instrucciones. Pero hay algo que no termina de funcionar.
En teoría ya estoy en barracuda, pero cuando he ido a cambiar el cotejamiento con el ALTER TABLE muchas de ellas me daba el error de:
#1071 – Declaracion de clave demasiado larga. La maxima longitud de clave es 767
Por ello he ido a cada una de las tablas con error y he cambiado los VARCHAR a 191 y al ejecutarlo de nuevo no he tenido problemas y todos ellos se han cambiado al utf8mb4_general_ci que es el cambio que he hecho (por recomendación de los foros de moodle).
Ahora bien, me siguen quedando un montón de tablas cuyo cotejamiento sigue siendo utf8.
He entrado en su estructura y sus columnas son de Tipo Bigint(10), sin cotejamiento, y alguna de ellas decimal cuyo cotejamiento he puesto en utf8mb4.
Todas estas tablas no las puedo cambiar y lo curioso es que si tuvieran alguna columna con VARCHAR, poniéndola a 191 se cambia el cotejamiento de la tabla, pero cuando no tienen ningún VARCHAR no se cambia dicho cotejamiento.
Estoy en modo local y puedo continuar con la instalación de moodle, pero me gustaría dejar el tema cerrado antes de subirlo todo al servidor y no tener, en el futuro, que volver sobre el tema.
¿Tienes alguna sugerencia?
Gracias por tu ayuda.
Hola Luis. Disculpa por la tardanza en responder.
Si tienes Barracuda, no es necesario cambiar longitud del Varchar en cada tabla, como he explicado en el post.
Habría que revisar si tu formato de tablas es realmente barracuda, como también explico.
Saludos.
Excelente aporte amigo me sirvio muchisimo gracias
Me toco realizar casi todos estos pasos… ya que migré un sitio Moodle a un servidor que está configurado con Antelope, y además, cambié la bd, tablas y columnas a utf8mb4_unicode_ci. Un poco de investigación y mucho ensayo y error. No te imaginas cuanto hubiera ahorrado tan solo leyendo este post. Es un aporte excelente, gracias por la ayuda.
Hola Andreyna.
He migrado bases de datos de Moodle en varias ocasiones y se lo difícil que puede ser.
Me alegra que la guía te haya gustado, lastima que llego tarde. Pero te aseguro que aprendiste un montón haciéndolo por tu cuenta 😉
Saludos.
Buenas, utilizo moodle en un servidor compartido y he hecho el procedimiento que comentas de convertir de utf8 a utf8mb4 y en el paso de copiar y pegar todos los ALERT TABLES en la casilla de SQL y dar al boton de continuar me da el siguiente error.
Error
consulta SQL:
ALTER TABLE disenow1_moodl30.mdl_capabilities CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci
MySQL ha dicho: Documentación
#1071 – Declaracion de clave demasiado larga. La maxima longitud de clave es 767
Gracias por tu ayuda.
Un saludo
Hola Ernesto.
Por lo que comentas, imagino que no has podido llevar el formato de tablas a Barracuda.
Prueba a migrar todas las otras tabla, excepto esa.
Luego en esa tabla en particular lleva el tamaño máximo de las columnas a 191 (como explico en el ultimo paso), antes de llevarla a UTF8mb4; luego intenta la migración.
No esta demás recordarte guardar una copia de seguridad antes de hacer cualquier cambio 😉
Saludos.
Y si la base de datos wordpress está al instalarla en utf8mb4_unicode como el cotejamiento con servidor, pero la que dice “information_schema” está en utf8_general_ci y debajo dice “Total: 2 latin1_swedish_ci” ¿Eso es normal o está hecho un lío todo?
Espero que no haya que tocar nada, porque no sé como se hace lo de “Logueate como root en tu servidor”
Hola Maru.
El cotejamiento en el servidor puede ser distinto al de la base de datos, lo importante es este ultimo; si quieres migrar tu DB a Utf8mb4 puedes seguir los pasos de esta guía.
De todas maneras, es importante que tengas cuidado, ya que el manejo de la base de datos es un proceso delicado y si no sabes como acceder a tu servidor como root (si es un VPS o dedicado) es mejor contratar a un experto para este proceso.
Saludos.
Me da error en el cambio de tipo de busqueda a InnoDB
Error
consulta SQL:
ALTER TABLE `atigra_users` ENGINE=InnoDB
MySQL ha dicho: Documentación
#1067 – Valor por defecto invalido para ‘user_registered’
Hola Antonio.
Prueba agregando esto al archivo my.cnf debajo de la línea [mysqld]
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'Reinicia el MySQL como explico en el post.
Si no puedes acceder al my.cnf deberás ponerte en contacto con el soporte de tu hosting.
Saludos.
Excelente tu aporte Diógenes, muy claro y útil.
Me alegra que te haya sido de ayuda Edilberto.
Saludos 😉