unreal4u's Personal Network Because my reality… is just your virtuality

4Ago/1223

Sobre COLLATION y CHARSET en MySQL

Hace bastante tiempo que tenía ganas de abordar varias cosas de MySQL, pero una de las más importantes (a mi juicio) es lo relativo a los COLLATIONs y los CHARSETs que tanto dolor de cabeza le pueden dar a aquellos de habla hispana. ¿Quién no ha tenido problemas con los tildes (no confundir con los acentos) en alguna página?

Y aunque este tema ya fue tocado en un post previo, éste será un poco más específico y aclaratorio sólo sobre estas dos propiedades de cada tabla y/o campo.

Introducción

Desde prácticamente los inicios de MySQL es que siempre he tenido la curiosidad con respecto a qué significan estas cosas raras: al principio todo funcionaba mágicamente usando latin1, el cual también era el predeterminado de MySQL (así como también Apache y PHP). Sin embargo, los problemas empezaron cuando el mundo decidió migrar hacia UTF-8, y más específicamente cuando Apache o PHP (una de las dos, no me acuerdo bien cuál) decidió que de ahora en adelante el charset predeterminado sería este mismo. Aunque siempre existía la opción de aplicar iconv(), estaba más que claro que era un poco contra-productivo tener que aplicar esta función a cualquier salida hecha por la base de datos, así que en algún momento de la vida empecé a ocupar UTF-8 también en la base de datos.

Partiendo por lo básico

Una de las primeras cosas que se deben saber es el cómo un CHARSET u otro guarda la información. Para ser muy breves y concisos, utf8 ocupa hasta 3 bytes en cada caracter, mientras que latin1 siempre ocupará 1 solo byte.

Aunque ninguno de los dos campos es obligatorio al crear una tabla, se recomienda que sí se haga no por ustedes, sino que si algún día cambian la configuración, tendrán una mezcla de charsets en sus bases de datos, el cual puede ser un parto detectar y cambiar.
Con respecto a esto mismo, si colocan un CHARSET, MySQL le asignará de forma automática un COLLATION que está asociado al CHARSET. La lista de estos dos la pueden averiguar con el siguiente SQL:

-- Mostrar los CHARSETs instalados:
SHOW CHARACTER SET;
-- Mostrar COLLATIONS instalados:
SHOW COLLATION;

El primero debería entregarles un listado de todos los CHARSET instalados y también debería mostrarles el COLLATION asociado. También les muestra el número de bytes máximo que ocupa cada CHARSET.
El segundo comando muestra todos los COLLATIONs instalados.

Por último, cabe destacar que podemos asignar COLLATIONs y CHARSETs; en orden de prioridad; a nivel de:

  • MySQL completo
  • Bases de datos
  • Tablas
  • Campos

Eso significa que si se asigna un CHARSET y COLLATION a nivel de MySQL, todas las bases de datos heredarán esta propiedad. A su vez, todas las tablas heredarán las propiedades de la base de datos y asimismo, todos los campos heredarán las propiedades predeterminadas de las tablas. En cualquiera de estos pasos podemos sobre-escribir el predeterminado con nuestro propio CHARSET y COLLATION.

¿Y qué significan entonces?

En palabras muy breves, una de las principales diferencias es que CHARSET hace referencia a cómo MySQL guarda internamente el dato y COLLATION es una manera de decirle cómo debe comparar el texto y/o ordenarlo. Para explicar bien este punto apliquemos algunos ejemplos:

-- Creamos la tabla:
CREATE TABLE IF NOT EXISTS collationTests (
    name01 CHAR(5) CHARSET utf8 COLLATE utf8_unicode_ci,
    name02 CHAR(5) CHARSET latin1 COLLATE latin1_general_cs,
    name03 CHAR(5) CHARSET ASCII COLLATE ascii_general_ci,
    name04 CHAR(5) CHARSET utf8 COLLATE utf8_bin,
    name05 CHAR(5) CHARSET latin1 COLLATE latin1_bin,
    name06 CHAR(5) CHARSET ASCII COLLATE ascii_bin,
) ENGINE=MyISAM;

-- Insertamos algunos datos:
INSERT INTO collationTests VALUES ('Ñandú','Ñandú','Nandu','Ñandú','Ñandú','Nandu');

Lo que hicimos arriba fue crear una tabla con diversos CHARSETs y luego insertamos una columna con el mismo dato, a método de comparación. Enseguida, aplicamos algunos SELECTs que es donde empieza la diversión total.

Lo primero que cabe destacar es que el CHARSET ASCII no permite ingresar otra cosa que no esté en la tabla ASCII predeterminada, así que la letra "Ñ" y aquellas letras con tilde quedan absolutamente descartadas.

SELECT LENGTH(name01) AS bL01, CHAR_LENGTH(name01) AS cL01 FROM collationTests;
SELECT LENGTH(name02) AS bL02, CHAR_LENGTH(name02) AS cL02 FROM collationTests;
SELECT LENGTH(name03) AS bL03, CHAR_LENGTH(name03) AS cL03 FROM collationTests;

La primera serie de consultas que vamos a realizar tiene que ver con el tamaño (en bytes) de cada campo relevante y su tamaño en número de caracteres. Hago esta aclaración ya que ambas funciones tienen esa distinción.

El resultado de esto es:

bL01	cL01
7	    5
bL02	cL02
5	    5
bL03	cL03
5	    5

Como vemos, en UTF-8 se guardaron 7 bytes de información (un byte extra en la letra "Ñ", otro byte extra en la letra "ú") pero el largo de cada cadena en cada caso es de 5 caracteres.

Aplicamos algunas consultas para ver las diferencias entre COLLATIONs:

SELECT * FROM collationTests WHERE name01 LIKE 'N%';
SELECT * FROM collationTests WHERE name01 LIKE 'ñ%';
SELECT * FROM collationTests WHERE name01 LIKE 'Ñ%';
SELECT * FROM collationTests WHERE name04 LIKE 'N%';
SELECT * FROM collationTests WHERE name04 LIKE 'ñ%';
SELECT * FROM collationTests WHERE name04 LIKE 'Ñ%';

Las primeras 3 (CHARSET utf8 COLLATE utf8_general_ci) devolverán un registro cada uno, mientras que de las últimas 3 (CHARSET utf8 COLLATE utf8_bin) sólo el último devolverá un resultado positivo.
Esto se debe a que la columna "name01" tiene COLLATION utf8-general-ci, que, entre otras cosas, considera como sinónimo la letra "N" y "Ñ", y además es case-insensitive (utf8-general-ci). Esto también se aplica a los tildes, de forma que si buscamos por ñandu (sin tilde) el resultado entregado será el mismo que si buscamos por "Ñandú". De igual forma, "ÑaÑdU" igual entregará un resultado positivo para "Ñandú".
En las últimas 3 sin embargo, si no buscamos exactamente por lo ingresado en la base de datos no se devolverá ningún registro, así que cualquier cosa que no sea un match exacto de "Ñandú" simplemente se descartará.

Ordenando resultados

Al principio del punto anterior dije que los COLLATION también servían para cambiar la manera en que ordenamos la información. Si nos fijamos en los COLLATION presentes para UTF-8, podemos apreciar de que existen 2 que llaman la atención: utf8_spanish_ci y utf8_spanish2_ci.

La diferencia entre ambos es que la primera se ocupa para español moderno mientras que la segunda se aplica a español tradicional.
De esta forma, podemos ver que ambos incorporan el uso de la "Ñ" como una letra entre la "N" y la "O", de forma que si tenemos los siguientes registros:

Nicolás
Ñandú
Operación

Ambos COLLATION ordenarán esos 2 registros de esa forma. Sin embargo, si tenemos los siguientes registros:

Cruzada
Baño
Carlos
Dedo
Chile

Una COLLATION utf8_spanish_ci ordenará los registros de una forma mientras que utf8_spanish2_ci la ordenará de otra, debido a que el español tradicional considera "ch" como una letra entre la "C" y la "D". Asimismo, considera el uso de la letra "LL" como una letra entre la "L" y "M".

Si quieren una prueba de concepto, ejecuten lo siguiente:

CREATE TABLE IF NOT EXISTS spanishCollation (
    name01 VARCHAR(15) CHARSET utf8 COLLATE utf8_spanish_ci,
    name02 VARCHAR(15) CHARSET utf8 COLLATE utf8_spanish2_ci
) ENGINE=MyISAM;

INSERT INTO spanishCollation VALUES ('Baño', 'Baño'),('Carlos', 'Carlos'),('Cruzada', 'Cruzada'),('Chile', 'Chile'),('Llorar', 'Llorar'),('Lámina', 'Lámina'),('Loreto', 'Loreto'),('Dedo', 'Dedo');

SELECT * FROM spanishCollation ORDER BY name01;
SELECT * FROM spanishCollation ORDER BY name02;

Al ejecutar las consultas se podrá ver claramente la diferencia entre ambas formas de ordenar los resultados.

Conclusiones

Aunque parece ser un tema menor, la verdad es que hay harto que pensar y decidir detrás de los CHARSETs y COLLATIONs. Por el lado del CHARSET, aunque parezca como mejor opción utilizar siempre UTF8, hay casos en que resulta totalmente inútil: si queremos guardar una cadena cuyo valor sabemos que no contendrá valores distintos del alfabeto, es mejor ocupar ASCII y de esa forma estar seguros que no se pueden ingresar caracteres inválidos. Por otro lado, si estamos seguros de que nuestra aplicación nunca jamás tendrá otro idioma que no sea español, también podemos utilizar latin1, aunque hay que tener cuidado en hacer los ajustes necesarios en todos aquellos lados donde podría haber una influencia de otro set de caracteres.

El tema del COLLATION sí es un poco más extenso ya que dependerá mucho del cómo se efectúen las búsquedas y también el idioma en que estemos trabajando. MySQL asigna utf8_general_ci como COLLATION predeterminado de UTF-8, pero éste presenta algunos problemas en hebreo y en algunas localizaciones de idiomas cirílicos; principalmente bielorruso, macedonio, serbio y ucraniano; así que para estos casos resulta mejor ocupar utf8_unicode_ci, la que tampoco está exenta de polémica ya que es más lento que utf8_general_ci.

Como gran conclusión: traten de usar UTF-8 como CHARSET donde sea posible, y si quieren tener velocidad vayan por utf8_general_ci como COLLATION pero si quieren tener certeza de que todo está bien ordenado y que sea compatible con (casi) todos los idiomas del mundo ocupen utf8_unicode_ci.

Fuentes:
code.openark.org
dev MySQL

¿Te gustó este artículo?

¡Considera suscribirte a nuestro feed!

Sobre Camilo Sperberg

Es Ingeniero Informático especializado en Linux y PHP (Es la primera persona en certificarse en PHP5.3 en Chile). En su tiempo libre le gusta estudiar nuevas técnicas de programación y escribir. Además, es amigo de todo ser viviente y cree que la tecnocracia es la mejor forma de política.
Archivado en: Bases de Datos, i18n/L10n, Mundo Web, 21,350 vistas Deja un comentario
Comentarios (23) Trackbars (1)
  1. This example shows how to configure mysqld server to use pure utf8 for server’s character set and collation instead of default latin1. This will help to correctly store non-latin character data in db (cyrillic сharacters for example and russian language in particularly).Go to the [mysqld] section in my.cnf and add two strings:collation_server=utf8_unicode_cicharacter_set_server=utf8You can also addskip-character-set-client-handshaketo enforce using of utf8 encoding in db.

    • Sugel, thanks for your comments ;)

      You are absolutely right, I went so deep into field related charset and collations, that I completely forgot about database and motor settings.

      As you mention it, the actual chain of command in charset related settings (or almost any setting in MySQL) follows like this:
      my.cnf
      database charset
      table charset
      field charset

      Greetings.

  2. Es un tema muy importante, y creo que tiene que ver con el problema que estoy teniendo en mi aplicacion, estoy accediendo a una base de datos de MySQL desde Delphi todo trabaja hasta ahora que he creado una funcion que devuelve un valor numerico o de cadena, si ejecuto esta funcion en MySQL no hay ningun problema, el error me lo da cuando trato de asignar esta funcion a un componente de Delphi un StoreProcedure el cual se supone que al pasarle el parametro de entrada a la funcion deba recibir el parametro de salida y en el error que me sale me habla de un “Latin1″ lo cual no sabia a que se referia. Hay forma de saber cual es el CHARSET y la COLLATION que me esta devolviendo la base de datos en la funcion? o acaso debo hacer coincidir estos valores desde los controles de mi aplicacion?

    muchas gracias por la informacion

    • Hasta donde sé no hay forma de saber el charset con el cual una función devuelve su respuesta, pero en la consulta podrías convertirlo (se puede hacer con cast por ejemplo), aunque lo ideal sería escribir tu aplicación y por supuesto tb la base de datos en un solo charset, de modo que nunca tengas este tipo de problemas.

      Saludos.

  3. Gracias por el post, muy informativo. Tengo una pregunta: mis tablas las he configurado con collate utf8_spanish_ci, que no diferencia entre acentos o no PERO sí que diferencia entre ñ y n. Esto me viene bien a la hora de ordenar los campos y realizar labores de mantenimiento, pero a la hora de búsquedas desde web me vendría bien no distinguir entre ñ y n. ¿Existe alguna forma de decirle al motor de búsqueda que para una conexión determinada utilice un collate u otro, para no tener que especificarlo en cada consulta?

    gracias por anticipado

    • No estoy en un pc ahora, pero se me ocurren dos ideas rápidas, la primera es intentar algo como esto:

      SELECT `name` FROM `table` ORDER BY `name` COLLATE utf8_general_ci;

      Y la segunda (dependiendo claro de los tipos de datos que necesites almacenar) es crear una copia de ese campo usando otro collate a fin de poder buscar por ese campo.
      Por qué esta solución? Pues porque de esta forma aprovechas los índices nativos de los que mysql predispone para hacer sus búsquedas en campos de texto y no tendrás una pérdida de velocidad al forzarlo a cambiar de collation.

      Mañana o pasadomañana recién podré corroborar/desmentir esta información pero al menos tienes dos métodos por los cuales partir :)

      Saludos.

  4. Hola, mi consulta es la siguiente: qué ocurre si cambiamos el collation a una tabla con datos? es peligroso hacerlo si la base de datos ya esta en marcha? es decir, los datos sufren alguna alteración?
    Gracias, espero respondas.

  5. Muy buen artículo (y muy aclaratorio) GRACIAS!!!

  6. Excelente, articulo, me has aclarado algunas dudas.

  7. hola sabes tengo una consulta, siempre he tenido estos problemas de la codificación y lo termino arreglando con alguna función o algo por el estilo, entonces me aburrí de esto :) , así que estoy esmerado en dejar esto configurado desde el mismo server, pero la verdad que me tiene un poco complicado, (ojala me respondas :( ,,,)
    Mira en mysql tengo esto
    mysql> SHOW VARIABLES LIKE ‘character_set%’;
    +————————–+—————————————+
    | Variable_name | Value |
    +————————–+—————————————+
    | character_set_client | utf8 |
    | character_set_connection | utf8 |
    | character_set_database | utf8 |
    | character_set_filesystem | binary |
    | character_set_results | utf8 |
    | character_set_server | utf8 |
    | character_set_system | utf8 |
    | character_sets_dir | /opt/mysql/server-5.6/share/charsets/ |
    +————————–+—————————————+
    8 rows in set (0.00 sec)

    mysql> SHOW VARIABLES LIKE ‘collation%’;
    +———————-+—————–+
    | Variable_name | Value |
    +———————-+—————–+
    | collation_connection | utf8_general_ci |
    | collation_database | utf8_general_ci |
    | collation_server | utf8_general_ci |
    +———————-+—————–+

    en apache

    AddDefaultCharset UTF-8

    ahora cuando descomento la linea de arriba, me muestra sin problemas los acentos tildes eñes tanto en la salida por consola como en el navegador al hacer select, pero en las inserciones me las guardas de manera deforme :( ,,,

    20–está nuñez en el éxito!!!
    21–men�2 �and�2
    22–está nuñez en el éxito!!!
    23–men�2 �and�2
    24–está nuñez en el éxito!!!
    25–men�2 �and�2
    26–está nuñez en el éxito!!!
    27–está nuñez en el éxito!!!
    28–está nuñez en el éxito!!!
    29–está nuñez en el éxito!!!
    30–está nuñez en el éxito!!!

    y cuando comento la linea de arriba entonces las inserciones a mano me las guarda con sus acentos pero en la salida por el html sele deforme :( ,,,

    18–menú ñandú
    19–está nuñez en el éxito!!!
    20–está nuñez en el éxito!!!
    21–menú2 ñandú2
    22–está nuñez en el éxito!!!
    23–menú2 ñandú2
    24–está nuñez en el éxito!!!
    25–menú2 ñandú2
    26–está nuñez en el éxito!!!
    27–está nuñez en el éxito!!!
    28–está nuñez en el éxito!!!
    29–está nuñez en el éxito!!!
    30–está nuñez en el éxito!!!

    haber si me puedes ayudar a arreglar esto,

    en PHP
    apache
    HTTP_ACCEPT_LANGUAGE es-419,es;q=0.8
    HTTP_ACCEPT_CHARSET ISO-8859-1,utf-8;q=0.7,*;q=0.3
    html
    Accept-Language es-419,es;q=0.8
    Accept-Charset ISO-8859-1,utf-8;q=0.7,*;q=0.3

    Saludos

    • obvio que respondo, mal que mal este blog es atendido por su propio dueño :P

      Lo que no veo es que en tu conexión hacia la base de datos le tengas puesto que quieres transmitir todo en UTF-8, esto se hace con “SET NAMES ‘utf8′” si mal no me acuerdo (de memoria) pero de todas formas esa es la manera más antigua y menos recomendable de hacerla. MySQLi incorpora una función que establece este parámetro (revisa mi class extended mysqli en github: https://github.com/unreal4u/u4u-classes ).

      Lo otro que quizás te puede servir, es esta nota que en tu caso específico te puede ayudar bastante más que este post: http://blog.unreal4u.com/2010/09/problemas-en-los-tildes-o-acentos/

      Saludos.

  8. Muchas gracias, explicaste y resumiste un tema muy importante y lo hiciste bastante fácil.
    Saludos!

  9. Genial! :) Me divertí mucho con tu artículo!

  10. Te hago una consulta relativa a este tema, que no se como resolverla.
    Estoy haciendo un query que trae datos de dos tablas que están en 2 base de datos distintas, que tienen un campo similar, pero me tira el sig. error:
    Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’ ,
    al igualar por ese campo.
    Hay alguna forma de castear en la consulta ambos campos al mismo tipo (sin alterar las bases, ya que no tengo acceso a modificar las bases).

    Desde ya, muchas gracias!!

    • Por supuesto, puedes ocupar BINARY o bien seleccionar por COLLATE. El único punto negativo de ambas soluciones es que la base de datos TENDRÁ que hacer un quicksort adicional.

      Ejemplos:
      SELECT id, campo COLLATE utf8_general_ci FROM t;
      SELECT * FROM t WHERE BINARY a = BINARY b;

      Saludos.

  11. muchas gracias por tu explicación, era algo que me venia ocasionando problemas..

  12. Antes de nada, darte las gracias por hacer un blog tan bueno como este.

    Te escribo porque me encuentro con un problema debido al maravilloso mundo de las tildes. Lo primero decir, que no soy un experto en bases de datos y por ello tuve el siguiente problema. Tuve que realizar una migración entre servidores cuyas base de datos tienen charset diferentes, en concreto una tiene latin1 y la otra utf8. Traté de realizar el backup para la migración utilizando la opción de –default-character-set=utf8 para tratar de cambiar los datos codificados en latin1 a utf8. Cargué ese backup en el nuevo servidor para proceder a su migración y me topé con este error al intentar realizar una inserción directamente sobre mysql. Si la insercción se lo paso a través de un script sql, me realiza la insercción, pero al realizar el select me muestra los datos con tildes deformes.
    mysql> Insert into prueba
    -> values(‘Estación’);
    ERROR 1366 (HY000): Incorrect string value: ‘\xA2n’ for column ‘NOMBRE’ at row 1

    (Establecí una tabla prueba con el campo nombre definido como charset utf8 y collate utf8_spanish_ci, para poder determinar el error).

    Muchas gracias de antemano.

  13. Hola, antes de nada darte las gracias por tu blog, resulta útil y muy interesante. No se si te llegó un comentario anterior, pero al tratar de enviartelo me dio mensaje de que ya te había enviado uno sin que lo hubiese hecho.

    Mi problema está en que he tratado de realizar una migración de una base de datos entre dos servidores cuya configuración de charset es distinta, una es latin1 y la otra utf8. Realicé un backup del servidor latin1 con mysqldump estableciendo la opción –default-character-set utf8 para tratar de igualar los tipos de caracteres. Cargué el backup sobre el servidor de utf8 para proceder a su migración y ahí comenzaron mis problemas. La base de datos del nuevo servidor ya no me reconoce los datos que tienen tildes. Si intento realizar una insercción manual me da el siguiente error:

    mysql> Insert into prueba
    -> values(‘Estación’);
    ERROR 1366 (HY000): Incorrect string value: ‘\xA2n’ for column ‘NOMBRE’ at row 1

    Si cargo un script Sql con ese mismo dato, me lo guarda en la base de datos pero a la hora de hacer un SELECT me lo recupera de la siguiente forma:

    ———–+
    NOMBRE |
    ———–+
    Estaci├│n |
    Estaci├│n |
    Estaci├│n |
    Estaci├│n |
    ———–+
    Así que no entiendo que es lo que está sucediendo. Te doy las gracias ya por anticipado.
    Un saludo.

    • Hola Daniel: los comentarios quedan en una cola de moderación, tengo que aprobarlos antes de que aparezcan en el sitio :)

      Respecto a tu problema: ese problema aparece cuando tratas de insertar datos que no están codificados en UTF-8 en la base de datos. Esto puede ser debido a que la conexión que tu estableces con la base de datos tiene otra codificación. Para solucionar esto, tienes que establecer SET_NAMES a utf8. Ignoro qué estás ocupando por detrás así que más que eso no te puedo ayudar.

      Saludos.

  14. Muchas gracias por tu pronta respuesta, me ha resultado muy muy útil, ahora veo que el problema lo tenía en la conexión y visualización a través del “mysql command line” y que en el código php de la aplicación que debe mostrar los datos el charset estaba mal configurado. Los datos en la BD se almacenan correctamente en utf8.

  15. hola, me has salvado pero aún tengo una duda en cuanto al caso _cs, ¿cuál es su diferencia con _ci, a parte de que ci es insensible y cs es sensible? y el binario sólo toma en cuenta el tamaño de los datos o su valor en ascii.


Deja un comentario