Conteúdo:
- Objetivo e Motivação
- Collations e Character Set
- Níveis de customização
- Alternativas práticas
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.
É importante distinguir CHARACTER SET
de COLLATION
s:
CHARACTER SET
é um conjunto de símbolos e codificaçoes (ou encondings).- A
COLLATION
é um conjunto de regras utilizadas para comparar caracteres em umCHARACTER SET
. Duas palavras que traduzem bem o termoCOLLATION
são verificação e conferência. Vamos utilizar o próprio nomeCOLLATION
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
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:
- Através de dois parâmetros passados ao
mysqld
> mysqld --character-set-server=latin1 \
--collation-server=latin1_swedish_ci
- 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
- 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.
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.
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:
-
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. -
Modificar migrations atuais e garantir que todas migrations novas especifiquem um
CHARACTER SET
e umaCOLLATION
. 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
.