10.3. Determinar el conjunto de caracteres y la colación por defecto

MySQL 5.0

previous page next page

10.3. Determinar el conjunto de caracteres y la colación por defecto

Hay configuraciones por defecto para conjuntos de caracteres y colaciones en cuatro niveles: servidor, base de datos, tabla, y conexión. La siguiente descripción puede parecer compleja, pero en la práctica este funcionamiento de varios niveles conduce a resultados naturales y obvios.

10.3.1. Conjunto de caracteres y colación del servidor

El servidor MySQL tiene un conjunto de caracteres para el servidor y una colación, y ambos deben ser distintos al valor nulo.

MySQL determina el conjunto de caracteres y la colación del servidor con el siguiente procedimiento:

  • Según las opciones en efecto cuando arranca el servidor

  • Según los valores cambiados en tiempo de ejecución

A nivel de servidor, la decisión es simple. El conjunto de caracteres y la colación del servidor dependen inicialmente de la opción usada al arrancar mysqld. Se puede usar para el conjunto de caracteres y a éste se puede añadir para la colación. No especificar un conjunto de caracteres, es como especificar . Especificar sólo un conjunto de caracteres (por ejemplo, ) pero no una colación, es como especificar , ya que es la colación por defecto para . Por lo tanto, los siguientes tres comandos tienen el mismo efecto: 

shell> mysqld
shell> mysqld --default-character-set=latin1
shell> mysqld --default-character-set=latin1 \
           --default-collation=latin1_swedish_ci

Una forma de cambiar la especificación es recompilando. Para cambiar el conjunto de caracteres por defecto y la colación al compilar las fuentes, debe utilizarse: y como argumentos para configure. Por ejemplo: 

shell> ./configure --with-charset=latin1

O: 

shell> ./configure --with-charset=latin1 \
           --with-collation=latin1_german1_ci

Tanto mysqld como configure verifican que la combinación del conjunto de caracteres y la colación es válida. Si no lo es, cada programa muestra un mensaje de error y acaba.

El conjunto de caracteres y la colación actuales están disponibles como los valores de las variables y . Estas variables pueden cambiarse en tiempo de ejecución.

10.3.2. Conjuntos de caracteres y colaciones de la base de datos

Cada base de datos tiene un conjunto de caracteres y una colación que no pueden ser nulos. Los comandos y tienen cláusulas opcionales para especificar el conjunto de caracteres y colación de la base de datos: 

CREATE DATABASE 
    [[DEFAULT] CHARACTER SET ]
    [[DEFAULT] COLLATE ]

ALTER DATABASE 
    [[DEFAULT] CHARACTER SET ]
    [[DEFAULT] COLLATE ]

Ejemplo: 

CREATE DATABASE 
    DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL elige el conjunto de caracteres y colación de la base de datos así:

  • Si tanto como se especifican, entonces el conjunto de caracteres es y la colación .

  • Si se especifica sin , entonces el conjunto de caracteres es y la colación es la de defecto.

  • En el resto de casos, es el conjunto de caracteres y la colación del servidor.

La sintaxis de MySQL es análoga a la sintaxis estándar SQL . Por ello, es posible crear bases de datos con distintos conjuntos de caracteres y colaciones en el mismo servidor MySQL.

El conjunto de caracteres de la base de datos y la colación se usan como valores por defecto para una tabla si no se especifica el conjunto de caracteres y colación en el comando . No tienen otro propósito.

El conjunto de caracteres y colación para la base de datos por defecto están disponibles como los valores de las variables y . El servidor obtiene estas variables siempre que la base de datos por defecto cambia. Si no hay base de datos por defecto, las variables tienen el mismo valor que las variables correspondiente del lado del servidor, y .

10.3.3. Conjunto de caracteres y colación de tabla

Cada tabla tiene un conjunto de caracteres y colación que no pueden ser nulas. Los comandos y tienen cláusulas opcionales para especificar el conjunto de caracteres y la colación: 

CREATE TABLE  ()
    [DEFAULT CHARACTER SET  [COLLATE ]]

ALTER TABLE 
    [DEFAULT CHARACTER SET ] [COLLATE ]

Ejemplo: 

CREATE TABLE t1 ( ... )
    DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

MySQL elije el conjunto de caracteres y colación de la siguiente forma:

  • Si se especifican y , entonces el conjunto de caracteres es y la colación .

  • Si se especifica sin , el conjunto de caracteres es y la colación es la de defecto.

  • En cualquier otro caso, el conjunto de caracteres y colación son las del servidor.

El conjunto de caracteres y colación de la tabla se usan como valores por defecto si el conjunto de caracteres y la colación no se especifican en la definición de las columnas. El conjunto de caracteres de la tabla y la colación son extensiones MySQL; no hay mucho al respecto en el estándar SQL.

10.3.4. Conjunto de caracteres y colación de columnas

Cada columna “carácter” (esto es, una columna de tipo , , o ) tiene un conjunto de caracteres y colación de columna, que no pueden ser nulos. La sintaxis de definición de columnas tiene cláusulas opcionales para especificar el conjunto de caracteres y la colación: 

 {CHAR | VARCHAR | TEXT} ()
    [CHARACTER SET  [COLLATE ]]

Ejemplo: 

CREATE TABLE Table1
(
    column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci
);

MySQL elige el conjunto de caracteres y la colación de la columna de la siguiente forma:

  • Si se especifican y , entonces el conjunto de caracteres es y la colación .

  • Si se especifica sin , el conjunto de caracteres es y la colación es la de defecto.

  • En cualquier otro caso, se usan el conjunto de caracteres y la colación de la tabla.

Las cláusulas y son de SQL estándar.

10.3.5. Ejemplos de asignación de conjunto de caracteres y colación

Los siguientes ejemplos muestran cómo MySQL determina los valores del conjunto de caracteres y colación por defecto.

Ejemplo 1: Definición de tabla y columna 

CREATE TABLE t1
(
    c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci
) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;

Aquí tenemos una columna con un conjunto de caracteres y una colación . La definición es explícita, así que es sencillo. Debe tenerse en cuenta que no hay problema al almacenar una columna en una tabla .

Ejemplo 2: Definición de tabla y columna 

CREATE TABLE t1
(
    c1 CHAR(10) CHARACTER SET latin1
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

Ahora tenemos una columna con un conjunto de caracteres y la colación por defecto. Aunque puede parecer normal, la colación por defecto no es la del nivel de tabla. En lugar de ello, ya que la colación para siempre es , la columna tiene una colación de (no ).

Ejemplo 3: Definición de tabla y columna 

CREATE TABLE t1
(
    c1 CHAR(10)
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

Tenemos una columna con un conjunto de caracteres y colación por defecto. Bajo esta circunstancia, MySQL busca a nivel de tabla para determinar el conjunto de caracteres y colación para la columna. Así, el conjunto de caracteres para la columna es y su colación es .

Ejemplo 4: Definición de base de datos, tabla y columna 

CREATE DATABASE d1
    DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;
USE d1;
CREATE TABLE t1
(
    c1 CHAR(10)
);

Creamos una columna sin especificar su conjunto de caracteres y colación. Tampoco especificamos un conjunto de caracteres y colación a nivel de tabla. En este caso, MySQL busca a nivel de base de datos para inspirarse. (La configuración de la base de datos pasa a ser la configuración de la tabla, y posteriormente la configuración de columna). Así, el conjunto de caracteres para la columna es y su colación es .

10.3.6. Conjunto de caracteres y colación de la conexión

Varias variables de sistema de conjunto de caracteres y colaciones están relacionadas con la interacción cliente-servidor. Algunas de ellas se han mencionado en secciones anteriores:

  • El conjunto de caracteres y la colación del servidor están disponibles como los valores de las variables y .

  • El conjunto de caracteres y la colación de la base de datos por defecto están disponibles como valores de las variables y .

Variables de conjuntos de caracteres y colaciones adicionales están involucradas en tratar el tráfico para la conexión entre un cliente y el servidor. Cada cliente tiene variables para el conjunto de caracteres y colación relacionados con la conexión.

Una “conexión” es lo que se hace al conectarse con el servidor. El cliente envía comandos SQL, tales como consultas, mediante la conexión al servidor. El servidor envía respuestas, tales como resultados, mediante la conexión de vuelta al cliente. Esto genera preguntas sobre tratamiento de conjuntos de caracteres y colaciones para conexiones de cliente, cada una de ellas puede responderse mediante variables de sistema:

  • ¿ Qué conjunto de caracteres usa una consulta al salir del cliente?

    El servidor toma la variable para usarla en las consultas enviadas por el cliente.

  • ¿ Qué conjunto de caracteres debería usar el servidor para traducir una consulta tras recibirla?

    Para esto, el servidor usa y . Esto convierte las consultas enviadas por el cliente de a (excepto para cadenas de caracteres literales que tienen un introductor como o ). es importante para comparaciones de cadenas de caracteres literales. Para comparaciones de cadenas de caracteres con valores de columnas no importa, ya que las columnas tienen una precedencia mayor en las colaciones.

  • ¿ Qué conjunto de caracteres debería usar el servidor para traducir los resultados o errores antes de enviar el mensaje de vuelta al cliente?

    La variable indica el conjunto de caracteres usado por el servidor para devolver los resultados de las consultas al cliente. Esto incluye datos resultantes como los valores de las columnas, y metadatos resultantes como nombres de columnas.

Puede ajustar estas variables, o puede depender de los valores por defecto (en tal caso, puede obviar esta sección).

Hay dos comandos que afectan al conjunto de caracteres de conexión: 

SET NAMES ''
SET CHARACTER SET

indica qué hay en el comando SQL que envía el cliente. Por lo tanto, le dice al servidor “los próximos mensajes entrantes de este cliente están en el conjunto de caracteres .” También especifica el conjunto de caracteres para los resultados que el servidor devuelve al cliente. (Por ejemplo, indica los conjuntos de caracteres de la columna si usa el comando .)

Un comando ' es equivalente a estos tres comandos: 

mysql> SET character_set_client = ;
mysql> SET character_set_results = ;
mysql> SET character_set_connection = ;

Cambiar a también cambia de la colación por defecto a .

es similar pero cambia el conjunto de caracteres y la colación para la conexión para ser las de la base de datos por defecto. Un comando es equivalente a estos tres comandos: 

mysql> SET character_set_client = ;
mysql> SET character_set_results = ;
mysql> SET collation_connection = @@collation_database;

Cuando un cliente se conecta, envía al servidor el nombre del conjunto de caracteres que quiere usar. El servidor cambia las variables , , y para ese conjunto de caracteres. (De hecho, el servidor efectúa una operación usando el conjunto de caracteres).

No es necesario ejecutar cada vez que se arranca el cliente mysql, aunque se quiera utilizar un conjunto de caracteres diferente del que hay por defecto. Puede añadirse la opción en la línea de comandos de mysql, o en el fichero de opciones. Por ejemplo, el siguiente fichero de opciones cambia las variables del conjunto de caracteres a cada vez que se ejecuta mysql: 

[mysql]
default-character-set=koi8r

Ejemplo: Supongamos que se define como . Si no se especifica o , entonces para , el servidor devuelve todos los valores para usando el conjunto de caracteres que el cliente especificó al conectar. Por otro lado, si se especifica o , entonces justo antes de devolver los resultados, el servidor convierte el valor a . La conversión puede perder información si hay caracteres que no están en ambos conjuntos de caracteres.

Si no se quiere que el servidor haga ninguna conversión, debe cambiarse a : 

mysql> SET character_set_results = NULL;

10.3.7. Conjunto de caracteres y colación de columnas “carácter”

Cada literal de cadenas de caracteres tiene un conjunto de caracteres y una colación, que no pueden ser nulos.

Un literal de cadena de caracteres puede tener un introductor de conjunto de caracteres opcional y una cláusula : 

[_]'' [COLLATE ]

Ejemplos: 

SELECT '';
SELECT _latin1'';
SELECT _latin1'' COLLATE latin1_danish_ci;

Para el comando simple ', la cadena de caracteres tiene el conjunto de caracteres y colación definida por las variables de sistema y .

La expresión se llama formalmente introductor. Le dice al parser: “la siguiente cadena de caracteres utiliza el conjunto de caracteres ”. Esto ha sido fuente de confusión en el pasado. Enfaticemos pues que un introductor no causa ninguna conversión, sino que es estrictamente una señal que no cambia el valor de la cadena de caracteres. Un introductor también es legal antes de la notación para literales hexadecimales y literales numéricos hexadecimales (' y ), y antes de (parámetros de sustitución al usar comandos preparados dentro de la interfície de un lenguaje de programación).

Ejemplos: 

SELECT _latin1 x'AABBCC';
SELECT _latin1 0xAABBCC;
SELECT _latin1 ?;

MySQL determina el conjunto de caracteres y colación para un literal así:

  • Si se especifican y , entonces el conjunto de caracteres es y se usa la colación .

  • Si se especifica pero no se especifica , entonces el conjunto de caracteres es y el conjunto de caracteres es el de defecto.

  • En cualquier otro caso, se utilizan el conjunto de caracteres y la colación que hay en las variables de sistema y .

Ejemplos:

  • Una cadena de caracteres con conjunto de caracteres y colación : 

    SELECT _latin1'Müller' COLLATE latin1_german1_ci;

  • Una cadena de caracteres con conjunto de caracteres y su colación por defecto (esto es, ): 

    SELECT _latin1'Müller';

  • Una cadena de caracteres con conjunto de caracteres y colación por defecto de la conexión: 

    SELECT 'Müller';

Los introductores de conjunto de caracteres y la cláusula se implementan según las especificaciones del estándar SQL.

10.3.8. Usar COLLATE en sentencias SQL

Con la cláusula , puede obviarse la colación por defecto para comparación. puede usarse en varias partes de los comandos SQL. Aquí se muestran algunos ejemplos:

  • Con : 

    SELECT k
FROM t1
ORDER BY k COLLATE latin1_german2_ci;

  • Con : 

    SELECT k COLLATE latin1_german2_ci AS k1
FROM t1
ORDER BY k1;

  • Con : 

    SELECT k
FROM t1
GROUP BY k COLLATE latin1_german2_ci;

  • Con funciones agregadas: 

    SELECT MAX(k COLLATE latin1_german2_ci)
FROM t1;

  • Con : 

    SELECT DISTINCT k COLLATE latin1_german2_ci
FROM t1;

  • Con : 

         SELECT *
     FROM t1
     WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k;
   
         SELECT *
     FROM t1
     WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;

  • Con : 

    SELECT k
FROM t1
GROUP BY k
HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;

10.3.9. Precedencia de la cláusula COLLATE

La cláusula tiene alta precedencia (mayor que ), así que las siguientes dos expresiones son equivalentes: 

x || y COLLATE z
x || (y COLLATE z)

10.3.10. Operador BINARY

El operador es una abreviación de la cláusula . ' es equivalente a ' COLLATE , donde es el nombre de la colación binaria para el conjunto de caracteres de ''. Cada conjunto de caracteres tiene una colación binaria. Por ejemplo, la colación binaria para el conjunto de caracteres es , así si la columna es del conjunto de caracteres , los siguientes dos comandos tienen el mismo efecto: 

SELECT * FROM t1 ORDER BY BINARY a;
SELECT * FROM t1 ORDER BY a COLLATE latin1_bin;

10.3.11. Casos especiales en los que determinar la colación es complicado

En la gran mayoría de consultas, resulta obvio qué colación usa MySQL para resolver una operación de comparación. Por ejemplo, en los siguientes casos, debe quedar claro que la colación es “la colación de la columna ”: 

SELECT x FROM T ORDER BY x;
SELECT x FROM T WHERE x = x;
SELECT DISTINCT x FROM T;

Sin embargo, cuando están implicados varios operandos, puede haber ambigüedad. Por ejemplo: 

SELECT x FROM T WHERE x = 'Y';

¿Esta consulta debe usar la colación de la columna , o de la columna de caracteres literal ?

SQL estándar resuelve tales cuestiones usando lo que se solía llamar reglas “coercitivas”. Es decir: Como e tienen colaciones, ¿cuál tiene precedencia? Puede ser difícil de resolver, pero las siguientes reglas resuelven la mayoría de situaciones:

  • Una cláusula explícita tiene una coercibilidad de 0. (No es coercible en absoluto.)

  • La concatenación de dos cadenas de caracteres con diferentes colaciones tiene una coercibilidad de 1.

  • La colación de una columna tiene una coercibilidad de 2.

  • Una “constante de sistema” (la cadena de caracteres retornada por funciones como o ) tiene una coercibilidad de 3.

  • Una colación de un literal tiene una coercibilidad de 4.

  • o una expresión derivada de tiene una coercibilidad de 5.

Los valores de coercibilidad precedentes son los de MySQL 5.0.3. Consúltese la nota posterior en esta sección para más información relacionada con versiones.

Estas reglas resuelven ambigüedades como:

  • Uso de la colación con el valor más bajo de coercibilidad.

  • Si ambos operadodres tienen la misma coercibilidad, entonces hay un error si las colaciones son distintas.

Ejemplos:

Usa colación de
Usa colación de
Error

La función puede usarse para determinar la coercibilidad de una expresión de cadena de caracteres: 

mysql> SELECT COERCIBILITY('A' COLLATE latin1_swedish_ci);
        -> 0
mysql> SELECT COERCIBILITY(VERSION());
        -> 3
mysql> SELECT COERCIBILITY('A');
        -> 4

Consulte Sección 12.9.3, “Funciones de información”.

En MySQL 5.0 antes de la versión 5.0.3, no hay constantes de sistema o coercibilidad ignorables. Funciones como tienen una coercibilidad de 2 en lugar de 3, y los literales tienen una coercibilidad de 3 en lugar de 4.

10.3.12. A cada colación un conjunto de caracteres correcto

Recuerde que cada conjunto de caracteres tiene una o más colaciones, y cada colación está asociadad con uno y sólo un conjunto de caracteres. Por lo tanto, los siguientes comandos causan un mensaje de error ya que la colación no es legal con el conjunto de caracteres : 

mysql> SELECT _latin1 'x' COLLATE latin2_bin;
ERROR 1251: COLLATION 'latin2_bin' is not valid
for CHARACTER SET 'latin1'

10.3.13. Un ejemplo del efecto de una colación

Supongamos que la columna en la tabla tiene estos valores de columna : 

Muffler
Müller
MX Systems
MySQL

Y supongamos que los valores de la columna se retornan usando los siguientes comandos: 

SELECT X FROM T ORDER BY X COLLATE ;

El orden resultante de los valores para diferentes colaciones se muestra en esta tabla:

Muffler Muffler Müller
MX Systems Müller Muffler
Müller MX Systems MX Systems
MySQL MySQL MySQL

La tabla es un ejemplo que muestra el efecto de la utilización de diferentes colaciones en una cláusula . El carácter que causa el orden de ordenación diferente en este ejemplo es la U con dos puntos encima (), que los alemanes llaman "U-umlaut".

  • La primera columna muestra el resultado de usando la regla de colación Sueca/Finlandesa, que dice que U-umlaut se ordena con Y.

  • La segunda columna muestra el resultado de usando la regla alemana DIN-1, que dice que U-umlaut se ordena con U.

  • La tercera columna muestra el resultado de usando la regla alemana DIN-2, que dice que U-umlaut se ordena con UE.

previous page start next page