Codificación en BD (MySQL y Oracle)

Tanto en Oracle como en MySQL permiten establecer el conjunto de caracteres que pueden ser utilizados en los datos que se almacenan, esto es conocido como el CHARACTER SET o conjunto de caracteres permitidos. En el caso de que se intente introducir un carácter que no se encuentra en este conjunto, la BD devolvería un error indicando que el carácter no es permitido en la BD.

Este dato puede ser establecido a la hora de crear el esquema de la BD:

CREATE DATABASE <database_name>
USER SYS IDENTIFIED BY <password>
USER SYSTEM IDENTIFIED BY <password>
CHARACTER SET <character_set_name>
NATIONAL CHARACTER SET <character_set_name>
...

y

CREATE DATABASE <database_name>
CHARACTER SET charset_name,
COLLATE collation_name;

Lo ideal sería que nuestra BD soportara el mayor numero de caracteres, de esta forma evitamos una restricción desde la BD. Como solución podríamos establecer el CHARACTER SET = UTF-8, el cual permite la representación de todos los caracteres. En el caso de Oracle, no he detectado ningún problema, ya que tanto la comparación como la ordenación es correcta una vez que establecemos el CHARACTER SET = UTF-8. Los problemas surgieron a la hora de intentar lo mismo en MySQL.

Para facilitar las pruebas, las he realizado sobre una BD creada y lo único que he ido modificando es la codificación de la tabla que utilizaba para las pruebas.

Caso 1 (CHARACTER SET UTF-8)

Intentamos crear una tabla con el CHARACTER SET UTF-8.

create table test (cadena VARCHAR(10) CHARACTER SET binary);

En este caso se permite el conjunto de caracteres UTF-8, pero a la hora de comprobar la comparación entre cadenas detectamos que no es sensible a mayúsculas/minúsculas, además de no diferenciar entre caracteres diferentes como ‘á’ y ‘a’.

Caso 2 (CHARACTER SET binary)

Intentamos crear una tabla con el CHARACTER SET binary. En este caso al utilizar binary, sería parecido a la utilización de UTF-8 ya que se introduciría la codificación binaria del carácter.

create table test (cadena VARCHAR(10) CHARACTER SET binary);

En este caso la comparación era tanto sensible a mayúsculas/minúsculas y además se diferenciaba correctamente los caracteres diferentes (á != a). Pero en este caso se producían dos problemas:

• La información se almacenaba en binario, es decir, era la representación binaria correspondiente a los caracteres.  Ej: ef  bf  bd  41  ef  bf  bd  49  69.
• Debido a que la ordenación es mediante la representación binaria de los caracteres, esta ordenación era incorrecta y se podía producir el siguiente caso :  á > Á > b > a > B > A

Caso 3 (CHARACTER SET UTF-8 collate utf8_bin)

En este caso se permite el conjunto de caracteres UTF-8 en la representación y además se indica un collation utf8_bin, el cual provocará una comparación binaria a la hora de comparar los valores y también en la ordenación.

create table test (cadena VARCHAR(10) CHARACTER SET utf8 collate utf8_bin);

En este caso es sensible a mayúsculas/minúsculas, diferenciaba correctamente los caracteres diferentes (á != a) y la representación de los caracteres era correcta. En este caso el único problema era que la comparación era binaria y por ello la ordenación no era correcta.

Caso 4 (CHARACTER SET latin1 collate latin1_general_cs)

Por último, viendo que estableciendo CHARACTER SET=UTF8 no podíamos conseguir un comportamiento parecido al conseguido en Oracle, decidimos usar un conjunto de caracteres latin1, el cual contiene los caracteres utilizados en los países de Europa del Oeste, también añadimos el collation latin1_general_cs para permitir la sensibilidad a mayúscula/minúscula.

create table test (cadena VARCHAR(10) CHARACTER SET latin1 collate latin1_general_cs);

En este caso conseguimos un comportamiento igual que el obtenido en Oracle, pero con la diferencia que en este caso sólo permitiremos en nuestra BD aquellos caracteres de la codificación latin1 (http://www.collation-charts.org/mysql60/mysql604.latin1_general_cs.html)

Estas son las sentencias utilizadas a la hora de realizar las comparaciones:

drop table if exists test;

--- Aquí va el create table correspondiente

--- para realizar las comparaciones

INSERT INTO test VALUES ('ábecedario');

INSERT INTO test VALUES ('becedario');

INSERT INTO test VALUES ('abecedario');

INSERT INTO test VALUES ('nodo');

INSERT INTO test VALUES ('ñ');

INSERT INTO test VALUES ('oca');

SELECT * FROM test where cadena like 'á%';

SELECT * FROM test where cadena like 'Á%';

SELECT * FROM test where cadena like 'a%';

SELECT * FROM test where cadena like 'ñ';

SELECT * FROM test order by cadena;