XOOPS Brasil

 

9.3. Determinando o Conjunto de Caracteres e Collation Padrões

Existem configurações padrões para conjuntos de caracteres e collations em quatro níveis: servidor, banco de dados, tabela, conexão. A seguinte descrição pode parecer complexa, mas será encontrada na prática que os padrões em multi-níveis levam a resultados naturais e óbvios.

9.3.1. Conjunto de Caracteres e Collations do Servidor

O MySQL Server possui um conjunto de caracteres de servidor e collation de servidor que não podem ser nulos.

O MySQL determina o conjunto de caracteres e collations de servidor desta forma:

  • De acordo com as opções de configuração em efeito quando o servidor é iniciado.

Neste nível, a decisão é simples. O conjunto de caracteres e collations do servidor dependem das opções que você usa quando você inicia o mysqld. Você pode usar --default-character-set=character_set_name para o conjunto de caracteres, e junto com isto você pode adcionar --default-collation=collation_name para a collation. Se você não especificar um conjunto de caracteres, é o mesmo que utilizar --default-character-set=latin1. Se você especificar apenas um conjunto de caracteres (por exemplo, latin1) mas não uma collation, é o mesmo que usar --default-charset=latin1 --collation=latin1_swedish_ci pois latin1_swedish_ci é a collation padrão para latin1. Desta forma, os três comando seguintees todos têm o mesmo efeito:

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

Um modo de o conjunto é recompilando. Se você quiser alterar o conjunto de caracteres e collation padrões na construção dos fontes, utilize: --with-character-set e --with-collation como argumento para configure. Por exemplo:

shell> ./configure --with-character-set=latin1

ou

shell> ./configure --with-character-set=latin1
--with-collation=latin1_german1_ci

Tanto o mysqld quanto o configure verificam que a combinação conjunto de caracteres/collations é válida. Cada programa exibe um mensagem de erro e termina se a combinação não for válida.

9.3.2. Conjunto de Caracteres e Collation de Banco de Dados

Todo banco de dados tem um conjunto de caracteres de banco de dados e uma collatio de banco de dados, que não podem ser nulos. Os comandos CREATE DATABASE e ALTER DATABASE agora possuem cláusulas opcionais para especificarem o collation e conjunto de caracteres de banco de dados:

CREATE DATABASE db_name
[DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]]
ALTER DATABASE db_name
[DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]]

Exemplo:

CREATE DATABASE db_name
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

O MySQL escolhe o conjunto de caracteres e collations do banco de dados desta forma:

  • Se CHARACTER SET X e COLLATE Y foram especificados, então o conjunto de caracteres é X e a é collation Y.

  • Se CHARACTER SET X foi especificado sem COLLATE, então o conjunto de caracteres é X e a collation é o padrão.

  • Senão utiliza o conjunto de caracteres e a collation de servidor.

A sintaxe CREATE DATABASE ... DEFAULT CHARACTER SET ... do MySQL é análoga a sintaxe CREATE SCHEMA ... CHARACTER SET ... do padrão SQL. Por isto, é possível criar bancos de dados com com conjunto de caracteres e collations diferentes, no mesmo servidor MySQL.

O conjuto de caracteres e collations do banco de dados são usados como valores padrões se o conjunto de caracteres e a collation de tabela não forem especificados nas instruções CREATE TABLE. Eles não possuem nenhum outro propósito.

9.3.3. O Conjunto de Caracteres e Collations de Tabela

Toda tabela tem um conjunto de caracteres e collations de tabela, que não pode ser nulo. As instruções CREATE TABLE e ALTER TABLE agora possuem um cláusula opcional para especificar o conjunto de caracteres e collation de tabela:

CREATE TABLE table_name ( column_list )
[DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]]
ALTER TABLE table_name
[DEFAULT CHARACTER SET character_set_name] [COLLATE collation_name]

Exemplo:

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

O MySQL escolhe o conjunto de caracteres e collation de tabela desta forma:

  • Se CHARACTER SET X e COLLATE Y forem especificados, então o conjunto de caracteres é X e collation é Y.

  • Se CHARACTER SET X foi especificado sem COLLATE, então o conjunto de caracteres é X e o collation é o padrão.

  • Senão, o conjunto de caracteres e collation são os padrões.

O conjunto de caracteres e collation de tabela são usado como valores padrões, se o conjunto de caracteres e collation de colunas não são especificados nas definições de colunas individuais. O conjunto de caracteres e collation de tabelas são extensões MySQL; não há nada deste tipo na padrão SQL.

9.3.4. Conjunto de Caracteres e Collation de Colunas

Toda coluna ``caracter'' (isto é, uma colua do tipo CHAR, VARCHAR, ou TEXT) tem um conjunto de caracteres e collation de coluna, que não pode ser nulo. A sintaxe de definição de coluna agora possui uma cláusula opcional para especificar o conjunto de caracteres e collation:

column_name {CHAR | VARCHAR | TEXT} (column_length)
[CHARACTER SET character_set_name [COLLATE collation_name]]

Exemplo:

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

O MySQL escolhe o conjunto de caracteres e collation de coluna desta forma:

  • Se CHARACTER SET X e COLLATE Y forem especificados, então o conjunto de caracteres é X e collation é Y.

  • Se CHARACTER SET X foi especificado sem COLLATE, então o conjunto de caracteres é X e o collation é o padrão.

  • Senão, o conjunto de caracteres e collation são os padrões.

As cláusulas CHARACTER SET e COLLATE são do padrão SQL.

9.3.5. Exemplos de Atribuições de Conjuntos de Caracteres e Collation

Os seguintes exemplos mostram como o MySQL determina valores de conjunto de caracteres e collations padrões.

Exemplo 1: Definição de Tabela + Coluna

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

Aqui você tem uma coluna com um conjunto de caracteres latin1 e um collation latin1_german1_ci. A definição é explicita, assim ele é direto. Note que não há problemas em armazenar uma coluna latin1 em uma tabela latin2.

Example 2: Definição de Tabela + Coluna

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

Desta vez temos uma coluna com um conjunto de caracteres latin1 e uma collation padrão. Agora, embora possa parecer natural, a collation padrão é tomada do nível de tabela. Como a collation padrão para latin1 é sempre latin1_swedish_ci, a coluna c1 terá uma collation latin1_swedish_ci (e não latin1_danish_ci).

Exemplo 3: Definição de Tabela + Coluna

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

Temos uma coluna com um conjunto de caracteres padrão e uma collation padrão. Nesta circunstância, o MySQL olha para o nível de tabela para determinar o conjunto de caracteres e collation de coluna. Assim o conjunto de caracteres para colune c1 é latin1 e sua collation é latin1_danish_ci.

Exemplo 4: Definição de Banco de Dados + Tabela + Coluna

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

Criamos uma coluna sem especificar seu conjunto de caracteres e collation. Também não especificamos um conjunto de caracteres e uma collation na nível de tabela. Nestas circubntâncias, o MySQL olha para o nível de banco de dados para a determinação. (A configuração do banco de dados se torna a configuração da tabela e então a configuração da coluna). Assim o conjunto de caracteres para coluna c1 é latin2 e sua collation é latin2_czech_ci.

9.3.6. Conjunto de Caracteres e Collation de Conexão

Toda conexão tem o seu conjunto de caracteres e collation, que não podem ser nulos. Esistem atualmente dois conjuntos de caracteres de conexão, que chamamos ``connection/literals'' e ``connection/results'' quando é necessário distingui-los.

Considere o que é uma ``conexão'': é o que você faz quando conecta ao servidor. O cliente envia instruções SQL, como consultas, pela conexão com o sevidor. O servidor envia respostas, como resultados, pela conexão de volta para o cliente. Isto leva a diversas questões, tal como: (a) em qual conjunto de caracteres está uma consulta quando ela deixa o cliente? (b) em qual conjunto de caracteres o servidor deve traduzir uma consulta após recebê-la? (c) para qual conjunto de caracteres o servidor deve traduzir antes de enviar o resultado ou mensagem de erros de volta para o cliente? Você pode fazer um ajuste fino das configurações para isto, ou você pode depender dos padrões (neste caso, você pode ignorar esta seção).

Existem suas instruções que afetam o conjunto de caracteres da conexão:

SET NAMES character_set_name
SET CHARACTER SET character_set_name

SET NAMES indica o que está na instrução SQL que o cliente envia. Assim, SET NAMES cp1251diz ao servidor que ``futuras mensagens vindas do cliente estarão no conjunto de caracteres cp1251'' e o servidor está livre para traduzir para seu próprio conjunto de caracteres, se apropriado.

SET CHARACTER SET indica o que está na instrução SQL que o cliente envia, e também o que está no resultado que o servidor envia de volta para o cliente. Assim, SET CHARACTER SET inclui SET NAMES, e também especifica qual conjunto de caracteres o valor da coluna terá se, por exempo, você usar uma instrução SELECT.

EXEMPLO: Suponha que column1 é definido como CHAR(5) CHARACTER SET latin2. Se você não utilizar SET CHARACTER SET, então para SELECT column1 FROM t o servidor enviará de volta todos os valores para column1 usando o conjunto de caracteres latin2. Se por outro lado você usar SET CHARACTER SET latin1 então o servidor, antes de enviar de volta, converterá os valores latin2 para latin1. Tal conversão é lenta e poder ter perdas.

Quando você executa SET NAMES ou SET CHARACTER SET, você também está alterando a ``collation da conexão''. No entanto a collation da conexão existe apenas para consistência. Normalmente o seu valor não importa.

Com o cliente mysql, não é necessário executar SET NAMES todas as vezes que você inicá-lo. Você pode adicionar a opção --default-character-set-name a sua linha de instrução do mysql, ou em seu arquivo de opção. Por exemplo, a seguinte configuração do arquivo de opção irá alterar o conjunto de caracteres da conexão cada vez que você executar mysql:

[mysql]
default-character-set-name=character_set_name

9.3.7. Conjunto de Caracteres e Collation de Caracter de String Literal

Todo caracter de uma string literal tem um conjunto de caracteres e collation, que podem ser nulos.

Um caracter de uma string literal pode ter um introdutor de conjunto de caracteres opcional e cláusula COLLATE:

[_character_set_name]'string' [COLLATE collation_name]

Exemplos:

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

A instrução simples SELECT 'string' usa o conjunto de caracteres da conexão/literal.

A expressão _character_set_name é formalmente chamada um introdutor. Ele diz ao analisador que ``a string que ele vai seguir está no conjunto de caracteres X.'' Como isto tem confundido as pessoas no passado, enfatizamos que um introdutor não faz qualquer conversão, ele simplesmente um sinal que não altera o valor da string. Um introdutor também é permitido antes de uma notação de um literal hexa padrão e um literal hexa numérico (x'literal' e 0xnnnn), e antes de ? (substituição de parâmetros ao usar intruções preparadas dentro de uma interface de linguagem de programação).

Exemplos:

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

O MySQL determina um conjunto de caracteres e collation de literal desta forma:

  • Se _X e COLLATE Y forma especificados então o conjunto de caracteres do literal é X e o collation do literal é Y

  • Se _X é especificado mas COLLATE não é especificado, então o conjunto de caracteres do literal é X e a collation do literal é a collation padrão do X

  • De outra forma, o conjunto de caracteres e collation é o da conexão/literal.

Exemplos:

  • Uma string com o conjunto de caracteres latin1 e collation latin1_german1_ci.

    SELECT _latin1'Müller' COLLATE latin1_german1_ci;
    
  • Uma string com conjunto de caracteres latin1 e e sua collation padrão, isto é, latin1_swedish_ci:

    SELECT _latin1'Müller';
    
  • Uma string com o conjunto de caracteres e a collation da conexão/literal:

    SELECT 'Müller';
    

Introdutores de conjunto de caracteres e a cláusula COLLATE são implementados de acordo com as especificações do padrão SQL.

9.3.8. Cláusula COLLATE em Várias Partes de uma Consulta SQL

Com a cláusula COLLATE você pode sobrescrever o padrão da collation, qualquer que seja ele, para comparação. COLLATE pode ser usada em várias partes da consulta SQL. Aqui estão alguns exemplos:

  • Com ORDER BY:

    SELECT k
    FROM t1
    ORDER BY k COLLATE latin1_german2_ci;
    
  • Com AS:

    SELECT k COLLATE latin1_german2_ci AS k1
    FROM t1
    ORDER BY k1;
    
  • Com GROUP BY:

    SELECT k
    FROM t1
    GROUP BY k COLLATE latin1_german2_ci;
    
  • Com aggregate functions:

    SELECT MAX(k COLLATE latin1_german2_ci)
    FROM t1;
    
  • Com DISTINCT:

    SELECT DISTINCT k COLLATE latin1_german2_ci
    FROM t1;
    
  • Com WHERE:

    SELECT *
    FROM t1
    WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k;
    
  • Com HAVING:

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

9.3.9. Precedência da Cláusula COLLATE

A cláusula COLLATE tem alta precedência (maior que ||), então a expressão

x || y COLLATE z

é equivalente a:

x || (y COLLATE z)

9.3.10. Operador BINARY

O operador BINARY é uma atalho para uma cláusula COLLATE. Por exemplo, BINARY 'x' é equivalente a 'x' COLLATE y, onde y é o nome de uma collation binária apropriada. Por exemplo, assumindo que a coluna a é do conjunto de caracteres latin1, estas duas consultas têm o mesmo efeito:

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

Nota: Todo conjunto de caracteres tem um collation binário.

9.3.11. Alguns Casos Especiais Onde a Determinação da Collation e Trabalhosa

Na grande maioria das consultas, é obvio qual collation que o MySQL usa para resolver uma operação de comparação. Por exemplo, nos seguintes casos deve estar claro que a collationserá ``a collation de coluna da coluna x'':

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

No entanto, quando múltiplos operandos estão envolvidos, pode haver ambiguidade. Por exemplo:

SELECT x FROM T WHERE x = 'Y';

Esta consulta deve usar a collation de coluna x, ou da string literal 'Y'?

O padrão SQL resolve tal questão usando o que se costuma chamar real ``coercibilidade''. A essência é: Como x e 'Y' tem collation, qual collation toma precedência? É complexo, mas estas regras cuidariam da maioria das situações:

  • Uma cláusula COLLATE explicita tem precedência 4

  • Uma concatenação de duas strings com diferentes collations tem precedência 3.

  • Uma collation de coluna tem precedência 2.

  • Uma collation de literal tem precedência 1.

Estas regras resolvem ambiguidades da seguinte forma:

  • Use a collation com a maior precedência.

  • Se ambos os lados tiverem a mesma precedência, então terá um erro se a collation não são as mesmas.

Exemplos:

column1 = 'A'Usa a collation de column1
column1 = 'A' COLLATE xUsa a collation de 'A'
column1 COLLATE x = 'A' COLLATE yError

9.3.12. Collations Devem Ser para o Conjunto de Caracteres Certo

Lembramos que cada conjunto de caracteres tem um ou mais collation, e cada collation é associada com um e apenas um conjunto de caracteres. Consequentemente, a seguinte instrução causa um mensagem de erro porque a collation latin2_bin não é permitida com o conjunto de caracteres latin1:

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

9.3.13. Um exemplo do Efeito da Collation

Suponha que a coluna X na tabela T possui estes valores na coluna latin1:

Muffler
Müller
MX Systems
MySQL

E suponha que os valores da coluna são retornados usando a seguinte instrução:

SELECT X FROM T ORDER BY X COLLATE collation_name;

A ordem resultante dos valores para diferentes collation é mostrado nesta tabela:

latin1_swedish_cilatin1_german1_cilatin1_german2_ci
MufflerMufflerMüller
MX SystemsMüllerMuffler
MüllerMX SystemsMX Systems
MySQLMySQLMySQL

A tabela é um exemplo que mostra que mostra qual seria o efeito se usassemos collation diferentes em um cláusula ORDER BY. O caracter que está causando o problema neste exemplo é o U com dois pontos sobre ele, que os Alemães chamam de U-umlaut, mas nós chamamos de U-diaeresis.

A primeira coluna mostra o resultado da SELECT usando as regras de collation Suéco/Finlandês, que diz que U-diaeresis ordena com Y.

A segunda coluna mostra o resultado da SELECT usando as regras Almão DIN-1, que diz que U-diaeresis ordena com U.

A terceira coluna mostra o resultado da SELECT usando as regras Almão DIN-2, que diz que U-diaeresis ordena com UE.

Três collation diferentes, três resultados diferentes. Isto é o que o MySQL está aqui para tratar. Usando a collation apropriada, você pode esclher a ordem que você deseja.