May
22
2009
MySQL y los dichosos “encodings”
Posted by Jaime Crespo on Fri 22 May 2009 12:53 UTC
Tags:

Si no tenemos cuidado, podemos encontrarnos con una desagradable sorpresa en forma de caracteres extraños.

Cualquier persona que comienza a manejar una base de datos suele encontrarse con problemas a la hora de manejar literales de cadena. Si no se pone el debido cuidado, podemos encontrarnos con errores causados por la utilización o conversión de codificaciones de caracteres. Intentaremos dar una pequeña guía para aclarar cómo manejar los distintos ‘encodings’ y ‘collations’ en una base de datos MySQL.

Conceptos básicos

Definamos, antes de nada, conceptos:

Juego de caracteres, character set o “charset”

Se trata de un subconjunto definido de símbolos de texto. El más ambicioso de todos ellos es UCS (Universal Character Set), el cual permite representar más de 1 millón de símbolos de forma no ambigua (asignándoles un nombre y número diferente a cada uno), aunque en la práctica apenas se usan más de 65.000.

Codificación de caracteres o (character) encoding

Es el algoritmo o la tabla que identifica cada símbolo del juego de caracteres con un valor binario, es decir, indica cómo se almacena de manera digital. Por ejemplo, UCS-2 y UTF-8 son encodings para el mismo juego de caracteres (UCS) [1], pero la forma de asignarlos varía en cada uno. Mientras que en UTF-8 la representación del caracter puede ocupar entre 1 y 4 bytes [2], en UCS-2, el tamaño es fijo, de 2 bytes.
Veamos esto de forma práctica: Creemos una tabla con tres encodings diferentes: UTF-8, UCS-2 y ASCII: 

mysql> CREATE TABLE `encoding` (
    -> `utf8` char(1) character set utf8 default NULL,
    -> `ucs2` char(1) character set ucs2 default NULL,
    -> `ascii` char(1) character set ascii default NULL
    -> );

Insertamos los caracteres ‘a’ y ‘ñ’: 

mysql> INSERT INTO encoding VALUES ('a', 'a', 'a');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO encoding VALUES ('ñ', 'ñ', 'ñ');
Query OK, 1 row affected, 1 warning (0.00 sec)

Y vemos cómo están guardados en disco realmente: 

mysql> SELECT utf8 AS `char`, hex(utf8) AS `utf8`, hex(ucs2) AS `ucs2`, hex(ascii) AS `ascii` FROM encoding;
+------+------+------+-------+
| char | utf8 | ucs2 | ascii |
+------+------+------+-------+
| a    | 61   | 0061 | 61    |
| ñ    | C3B1 | 00F1 | 3F    |
+------+------+------+-------+
2 rows in set (0.00 sec)

Como se puede ver, UTF-8 tiene la ventaja de que para la mayoría de caracteres usados en inglés coincide con la codificación ASCII. Sin embargo, para el resto de caracteres, requiere dos o más bytes [3]).

Algunos de los encodings más usuales son:

  • ASCII o US-ASCII: encoding histórico de 7 bits (aunque existen los denominados encodings “ASCII extendidos” que aumentaban el numero de caracteres a 8 bits)
  • ISO 8859-1 o Latin 1: utilizaba 1 byte y, siendo compatible con ASCII, incluía símbolos adicionales para la mayoría de idiomas occidentales (incluyendo la ñ, los acentos y la ‘ç’).
  • ISO 8859-15 o Latin 9: es una revisión de Latin 1, que añade, entre otros, el símbolo del euro.
  • windows-1252: versión ligeramente distinta a Latin 1/9 que utilizaban los sistemas operativos Windows
  • UTF-8: encoding de la norma Unicode que, siendo compatible con los caracteres ASCII originales, permite reprentar los caracteres de casi cualquier lengua [4] de manera eficiente (sólo requiere más de 1 byte para caracteres fuera de ASCII)
  • UTF-16: también de la norma unicode, sólo que requiere siempre 2 bytes para todos los caracteres UCS básicos. Es lo más eficiente si usamos muchos caracteres CJK (chinos, japoneses o koreanos). Se diferencia de UCS-2 en que éste último no podía representar caracteres más allá del número 65.536.

Collation u ordenación

Es el algoritmo que indica si dos caracteres son equivalentes, o si uno debería ir antes o después en una ordenación. Por ejemplo, en español, si nosotros tuviéramos la siguiente tabla y valores: 

mysql> CREATE TABLE `collation` (`a` char(20) character set utf8 collate utf8_spanish_ci);
mysql> INSERT INTO `collation` VALUES ('amapola');
mysql> INSERT INTO `collation` VALUES ('Andorra');
mysql> INSERT INTO `collation` VALUES ('Ávila');
mysql> INSERT INTO `collation` VALUES ('ábaco');
mysql> INSERT INTO `collation` VALUES ('burro');

y ejecutáramos la sentencia que nos permite obtener estos valores de manera ordenada, obtendríamos: 

mysql> SELECT * FROM `collation` ORDER BY a;
+---------+
| a       |
+---------+
| ábaco   |
| amapola |
| Andorra |
| Ávila   |
| burro   |
+---------+
5 rows in set (0.00 sec)

o si preguntáramos por cuales de las palabras empiezan por a, obtenemos: 

mysql> SELECT * FROM `collation` WHERE a LIKE 'a%';
+---------+
| a       |
+---------+
| amapola |
| Andorra |
| Ávila   |
| ábaco   |
+---------+
4 rows in set (0.02 sec)

Es decir, asume que a == A == á == Á

Esto es así porque, por defecto, el “collate” que se le hemos indicado es utf8_spanish_ci. Podemos forzar que use otro algoritmo de ordenamiento de la siguiente manera: 

mysql> SELECT a FROM `collation`ORDER BY a COLLATE utf8_bin;
+---------+
| a       |
+---------+
| Andorra |
| amapola |
| burro   |
| Ávila   |
| ábaco   |
+---------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM `collation` WHERE a LIKE 'a%' COLLATE utf8_bin;
+---------+
| a       |
+---------+
| amapola |
+---------+
1 row in set (0.00 sec)

Lo cual le indica que utilice exclusivamente para comparaciones el valor binario de su codificación UTF-8.

Aplicación

Una vez que ya conocemos los conceptos básicos alrededor de las codificación, vamos a ver cómo los utilizamos en una base de datos.

Existen tres lugares, a distinto nivel, donde podemos configurar el encoding que estamos utilizando para distintas tareas:

  • Variables de sesión
  • Variables globales
  • Schemas
  • Aplicación final cliente

La diferencia entre variables globales y de sesión es que las globales se configuran a nivel de servidor y son las que establecen el valor por defecto de las variables de sesión, las cuales pueden modificarse desde el cliente para cada conexión. Las variables de sesión se configuran por conexión, y sólo son válidas mientras ésta esté activa. Si existen variables de sesión y globales con el mismo nombre, las de sesión tienen precedencia.

También podemos asignar encodings y collations a bases de datos, tablas y columnas de manera independiente.

Por último, y aunque no lo trataremos aquí, debemos asegurarnos de que la aplicación que finalmente muestra los resultados lo hace en la misma codificación que hemos recibido los datos. Por ejemplo, si estamos en una sesión con mysql-cliente, tendremos que cambiar la codificación de salida del terminal de manera adecuada.

Veámos más en profundidad a qué afecta y cómo modificarlas en cada nivel.

Variables

Son las siguientes: 

mysql> show session 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   |
+--------------------------+--------+
7 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 |
+----------------------+-----------------+
3 rows in set (0.00 sec)
  • character_set_client
    Es el encoding en el que el servidor asume que le envías los datos. Por defecto es latin1.
  • character_set_connection y collation_connection
    character_set_connection es el encoding al que convierte los comandos enviados por el cliente (desde el encoding definido por character_set_client). Por defecto es latin1. collation_connection es el ordenamiento que asume para los literales enviados por el cliente. Es ignorado cuando se trabaja con columnas de tablas, ya que éstos ya tienen definida un “collate” propio, el cual tiene prioridad sobre el de la conexión. Como la collation por defecto de latin1 es latin1_swedish_ci, éste es el valor que toma por defecto.
  • character_set_results
    Es el encoding al que el servidor convierte los resultados y mensajes de error antes de enviarlos al cliente
  • character_set_server y collation_server
    Es el encoding y collation que usa el servidor por defecto (si no los cambiamos: latin1/latin1_swedish_ci).
  • character_set_database y collation_database
    Es el encoding/collation del schema que está en uso actualmente. El servidor la actualiza cada vez que cambiamos la base de datos actual. Podemos cambiarla, pero eso no cambia el encoding por defecto de la base de datos actual. Sus valores por defecto (no tenemos una base de datos selecionada) son los mismos que character_set_server y collation_server (latin1/latin1_swedish_ci, si no los hemos cambiado).
  • character_set_system
    Es el encoding interno usado para los identificadores. Siempre es utf8 (es de sólo lectura).
  • character_set_filesystem
    Es el encoding al que convierte (desde character_set_client para) los nombres de archivo usados en sentencias SELECT…INTO OUTFILE o LOAD DATA INFILE. Por defecto es binary, con el que no se hace ninguna conversión.

Alterando las opciones de sesión y globales

Podemos establecer los siguientes valores para alterar el comportamiento por defecto: 

character_set_client
character_set_connection
character_set_filesystem
character_set_results
character_set_server

Podemos hacerlo de varias manera:

  • Cambiar la variable de sesión en caliente, desde un cliente mysql 
SET LOCAL character_set_client='utf8';
SET @@local.character_set_client='utf8';

Es la opción adecuada si sólo queremos que tenga efecto para la sesión actual. No se mantiene entre sesiones, ni siquiera para el mismo usuario.

  • Cambiar la variable global en caliente, desde un cliente mysql: 
SET GLOBAL character_set_client='utf8';
SET @@global.character_set_client='utf8';

Todas las nuevas conexiones tendrán ese valor por defecto. Tiene el inconveniente de que al reiniciar el servidor, los cambios se perderán.

  • SET NAMES  / SET CHARACTER SET

Una forma alternativa -más corta- de cambiar estas variables es utilizando: 

SET NAMES 'x';

Lo cual es equivalente a hacer: 

SET character_set_client = 'x';
SET character_set_results = 'x';
SET character_set_connection = 'x';

O bien, de forma muy parecida: 

SET CHARACTER SET 'x';

Lo cual es equivalente a hacer: 

SET character_set_client = 'x';
SET character_set_results = 'x';
SET character_set_connection = @@collation_database;
  • Añadir la opción al ejecutar mysqld/mysqld_safe 
mysqld --character_set_client='utf8'

Es necesario hacerlo manualmente (o con algún script) cada vez que iniciemos el servidor.

  • Recompilar el servidor para que por defecto tome un encoding distinto

Es muy engorroso sólo para poner una opción

  • Añadir la opción a my.cf o my.ini: 
[mysqld]
character_set_client='utf8'

Es la opción recomendada si queremos que el cambio sea persistente.

Para ver los valores que pueden tomar estas varaibles disponibles, podemos ejecutar: 

SHOW CHARACTER SET [LIKE 'pattern']

Schemas

Podemos cambiar también cómo se guardan los datos en las tablas de la siguiente manera: 

CREATE DATABASE ... DEFAULT CHARACTER SET 'X' COLLATE 'Y' [5]
ALTER DATABASE ... DEFAULT CHARACTER SET 'X' COLLATE 'Y'

Establece el encoding y collation por defecto para esa base de datos. El cambiarla no altera automáticamente las tablas y columnas dentro de esa base de datos, sino sólo de las que creemos por defecto (sin explicitarlo) a partir de ahora. Por defecto utiliza character_set_server. 

CREATE TABLE ... DEFAULT CHARACTER SET 'X' COLLATE 'Y'
ALTER TABLE ... DEFAULT CHARACTER SET 'X' COLLATE 'Y'

Establece el encoding y collation por defecto para esa tabla. El cambiarla no altera automáticamente columnas dentro de esa base de datos, sino sólo de las que creemos por defecto (sin explicitarlo) a partir de ahora. Por defecto utiliza el character set de la base de datos a la que pertenece (no de la base de datos actual). 

ALTER TABLE ADD COLUMN ... DEFAULT CHARACTER SET 'X' COLLATE 'Y'
ALTER TABLE ADD MODIFY ... DEFAULT CHARACTER SET 'X' COLLATE 'Y'
ALTER TABLE ADD CHANGE ... DEFAULT CHARACTER SET 'X' COLLATE 'Y'

Establece el encoding y collation que tendrá realmente la columna. Por defecto utiliza el character set de la tabla a la que pertenece.

Más información

Notas

  1. UCS-2 no permite, siendo estrictos, representar todos los caracteres UCS usados actualmente, por lo que tiende a reemplazarse por UTF-16
  2. MySQL sólo permite caracteres multibyte UTF-8 de hasta 3 caracteres, lo cual es suficiente para los caracteres que se usan en la práctica
  3. Es necesario remarcar que tenemos dos funciones en MySQL para ver el tamaño de una cadena: lenght(), que es el tamaño en bytes y char_lenght(), que es el tamaño en caracteres. Ambos pueden diferir.
  4. Se propuso la adición de los caracteres Klingon a UCS, pero finalmente fue rechazado
  5. Una sintaxis equivalente, pero más corta para poner un determinado encoding es: 
    CREATE DATABASE ... CHARSET 'X'