MySQL: caracteres e conversões

Conteúdo:

1. Objetivo e Motivação
2. Collations e Character Set
3. Níveis de customização
4. Alternativas práticas

1. Objetivo e Motivação

Atualmente, não padronizamos as configurações de codificação do banco de dados entre desenvolvedores e ambientes de sandbox e produção. Porém, num belo dia, o @dowglaz encontrou um problema ao testar um processo seletivo. Bem no momento do snapshot. E aí? Será que tinha algo zuado e a gente não sabia?

Na verdade não. O problema era que a codificação do banco de dados do servidor era uma e a configuração do database.yml era outra. Sendo assim, a conversão de caracteres falhava.

Este documento tem por objetivo explicar o conceito de COLLATION e de CHARACTER SET no banco de dados MySQL - conceitos estes que balizam esta questão da codificação e conversão de caracteres neste RDBMS - para que a gente possa entender isso melhor e não cometamos os mesmos erros no futuro.

2. Collations e Character Set

É importante distinguir CHARACTER SET de COLLATIONs:

• CHARACTER SET é um conjunto de símbolos e codificaçoes (ou encondings).
• A COLLATION é um conjunto de regras utilizadas para comparar caracteres em um CHARACTER SET. Duas palavras que traduzem bem o termo COLLATION são verificação e conferência. Vamos utilizar o próprio nome COLLATION aqui.

Exemplificando o conceito: suponha que nós temos um alfabeto de quatro letras: "A", "B", "a" e "b". Atribuímos um número a cada letra:

character_set = {
  "A" => 0,
  "B" => 1,
  "a" => 2,
  "b" => 3
}

Neste exemplo, a letra "A" é um símbolo, o número 0 é uma codificação para a letra "A" e a combinação das quatro letras e números é um CHARACTER SET.

Suponha que nós queremos comparar duas strings, "A" e "B". A maneira mais simples de se fazer isso é olhar para as codificações: 0 para "A", 1 para "B". Como 0 < 1, nós dizemos que A < B. Nós acabamos de aplicar uma COLLATION para o nosso CHARACTER SET. COLLATION é um conjunto de regras (um única regra nesse caso): "comparar as codificações". Essa é a COLLATION mais simples, a chamada binary collation.

Além desta, ainda temos a case-insensitive collation e case-sensistive collation que, como os próprios nomes dizem, aplicam a comparação considerando ou não a caixa.

Mais informações e fonte: http://dev.mysql.com/doc/refman/5.7/en/charset-general.html

3. Níveis de customização

De acordo com a documentação do MySQL, há quatro níveis de customização de COLLATION e CHARACTER SET: servidor, banco de dados, tabela e coluna.

##3.1 Customização por servidor

Este tipo de customização implica que o servidor MySQL tem uma configuração padrão caso não seja especificado no banco de dados, na tabela ou na coluna. Existem três possibilidades para se especificar uma configuração para o servidor:

1. Através de dois parâmetros passados ao mysqld

> mysqld --character-set-server=latin1 \
         --collation-server=latin1_swedish_ci

2. Configurando através do arquivo /etc/mysql/my.cnf

Neste caso, basta adicionar as seguintes linhas a este arquivo, na seção [mysqld]: character-set-server=utf8 collation-server=utf8_unicode_ci init-connect='set NAMES utf8' init-connect='set collation_connection = utf8_unicode_ci' skip-character-set-client-handshake

3. Parametrizando o mysql na fase de compilação

É possível parametrizar qual serão a COLLATION e a CHARACTER SET padrões do servidor, passando ao cmake valores para os parâmetros DEFAULT_CHARSET e DEFAULT_COLLATION, por exemplo:

> cmake . -DDEFAULT_CHARSET=latin1 \
          -DDEFAULT_COLLATION=latin1_german1_ci

Mais informações e fonte: http://dev.mysql.com/doc/refman/5.7/en/charset-server.html.

3.2 Customização por banco de dados

Para parametrizar por banco de dados, pode-se informar os valores na criação ou pode-se alterar posteriormente, como a seguir:

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf_unicode_ci;

Mais informações e fonte: http://dev.mysql.com/doc/refman/5.7/en/charset-database.html.

3.3 Customização por tabela

Para se personalizar essas variáveis no momento da criação de uma tabela, basta fazer:

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

Pode-se também alterar a mesma posteriormente:

ALTER TABLE t1 CHARACTER SET latin1 COLLATE latin1_german_cs

É possível checar o COLLATION da tabela com o comando a seguir:

SELECT TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_schema_name' AND
      TABLE_NAME = 't1';

Saída:

+------------------+
| TABLE_COLLATION  |
+------------------+
| latin1_danish_ci |
+------------------+
1 row in set (0.00 sec)

Mais informações e fonte: http://dev.mysql.com/doc/refman/5.7/en/charset-table.html.

##3.4 Customização por coluna

Uma maneira um tanto fora do padrão mas ainda possível é a customização de uma coluna. Assim como nos métodos anteriores, pode-se customizar tanto no momento de criação quanto a posteriori. Seguem exemplos:

Exemplo:

CREATE TABLE t1
(
    col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    col2 CHAR(10),
    col3 NUMBER(10,2)
) CHARACTER SET latin1 COLLATE latin1_bin;

Neste caso, está se personalizando apenas a coluna col1 com o CHARACTER SET utf8 e a COLLATION utf8_unicode_ci, enquanto as demais utilizam a dupla latin1 e latin1_bin, informada para o restante da tabela.

Podemos checar as collations/character set utilizados utilizando o comando:

SELECT COLUMN_NAME,
       COLLATION_NAME,
       CHARACTER_SET_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_schema_name' AND
      TABLE_NAME = 't1';

Saída:

+-------------+------------------+--------------------+
| column_name | collation_name   | character_set_name |
+-------------+------------------+--------------------+
| col1        | utf8_unicode_ci  | utf8               |
| col2        | latin1_danish_ci | latin1             |
| col3        | NULL             | NULL               |
+-------------+------------------+--------------------+
3 rows in set (0.00 sec)

Mais informações e fonte: http://dev.mysql.com/doc/refman/5.7/en/charset-column.html.

Fonte deste capítulo: http://dev.mysql.com/doc/refman/5.7/en/charset-syntax.html.

#4. Alternativas práticas

Aqui vejo duas possibilidades práticas de padronização de CHARACTER SET e COLLATION, sendo a primeira mais plausível:

1. Padronizar as instalações de todos os Dev's através do arquivo /etc/mysql/my.cnf (como descrito no item 3.1.2). Isso poderia ser feito na configuração de um ambiente padrão (via ansiable, por exemplo). Desta forma, seria melhor padronizarmos o uso do vagrant no napratica também.

2. Modificar migrations atuais e garantir que todas migrations novas especifiquem um CHARACTER SET e uma COLLATION. Desta forma, fica um pouco trabalhoso e a garantia sobre as novas migrations ficariam ao encargo do desenvolvedor.

E, claro, sempre podemos convencionar que cada desenvolvedor garanta que seu ambiente esteja configurado corretamente. Os servidores de produção e sandbox ambos usam a combinação utf8 e utf8_unicode_ci.