[size=10pt]Hoje vamos falar do charset utf8 e alguns dos seus collates mais usados em português.
Eles irão afetar diretamente como ocorrerá sua ordenação e a criação de índices que necessitem de uma coluna com dados únicos como primary key, unique e etc[/size]
Mas antes vamos criar para nossos testes a tabela a seguir:
CREATE TABLE teste (
general varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci,
sueco varchar(3) CHARACTER SET utf8 COLLATE utf8_swedish_ci,
binario varchar(3) CHARACTER SET utf8 COLLATE utf8_bin,
unicodeci varchar(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci);
Para popular esta tabela vamos usar estes dados de exemplo:
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘A’,‘A’,‘A’,‘A’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘Ä’,‘Ä’,‘Ä’,‘Ä’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘N’,‘N’,‘N’,‘N’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘Ñ’,‘Ñ’,‘Ñ’,‘Ñ’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘Z’,‘Z’,‘Z’,‘Z’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘Ö’,‘Ö’,‘Ö’,‘Ö’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘Nz’,‘Nz’,‘Nz’,‘Nz’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘Az’,‘Az’,‘Az’,‘Az’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘Ç’,‘Ç’,‘Ç’,‘Ç’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘C’,‘C’,‘C’,‘C’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘Á’,‘Á’,‘Á’,‘Á’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘A’,‘A’,‘A’,‘A’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘a’,‘a’,‘a’,‘a’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘á’,‘á’,‘á’,‘á’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘ã’,‘ã’,‘ã’,‘ã’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘tes’,‘tes’,‘tes’,‘tes’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘TES’,‘TES’,‘TES’,‘TES’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘TeS’,‘TeS’,‘TeS’,‘TeS’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘sao’,‘sao’,‘sao’,‘sao’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘são’,‘são’,‘são’,‘são’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘ção’,‘ção’,‘ção’,‘ção’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘cão’,‘cão’,‘cão’,‘cão’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘ß’,‘ß’,‘ß’,‘ß’);
INSERT INTO teste (general, sueco, binario, unicodeci) VALUES (‘ss’,‘ss’,‘ss’,‘ss’);
[size=10pt]Sobre os collates: [/size]
1) utf8_general_ci
É o padrão da tripa sertaneja MySQL/MariaDB/Percona.
Não faz comparações entre caracteres em múltiplos idiomas.
Em regras gerais não há distinção entre letras maiúsculas,minúsculas e nem caracteres acentuados e com cedilha (case-insensitive).
Buscando por “tes”, registros como “Tes” ou “TES” serão retornados.
Buscando por “cao”, registros como “ção” ou “cão” será retornado
O agrupamento genérico é vagamente baseado em inglês, onde Ä = A, N = Ñ e O = Ö
Vamos ver três selects ordenados:
SELECT general FROM teste ORDER BY general;
resultado:
A ã á a A Á Ä Az Ç C ção cão Ñ N Nz Ö ß são sao ss TeS TES tes Z
SELECT general FROM teste where general = ‘tes’ ORDER BY general ;
resultado:
tes TES TeS
SELECT general FROM teste where general = ‘cao’ ORDER BY general ;
resultado:
ção cão
2) utf8_unicode_ci
Faz comparações entre caracteres em múltiplos idiomas.
Por exemplo, na língua alemã o caractere “ß” seria equivalente ao “ss”.
Como o utf8_unicode_ci tem que fazer esse tipo de comparação combinando mais de um caractere, ele é mais lento que utf8_general_ci.
Em regras gerais não há distinção entre letras maiúsculas,minúsculas e nem caracteres acentuados e com cedilha (case-insensitive).
Buscando por “tes”, registros como “Tes” ou “TES” serão retornados.
Buscando por “cao”, registros como “ção” ou “cão” será retornado
O agrupamento unicode é vagamente baseado em inglês, onde Ä = A, N = Ñ e O = Ö
Considerando sistemas que funcionam de forma global e devem trabalhar com múltiplos idiomas, como um Wordpress ou Wikipédia por exemplo, usar utf8_unicode_ci é o certo, embora seja mais lento.
Vamos ver três selects ordenados:
SELECT unicodeci FROM teste ORDER BY unicodeci;
resultado:
A ã á a A Á Ä Az Ç C ção cão Ñ N Nz Ö são sao ß ss TeS TES tes Z
SELECT unicodeci FROM teste where unicodeci = ‘tes’ ORDER BY unicodeci ;
resultado: tes TES TeS
SELECT unicodeci FROM teste where unicodeci = ‘cao’ ORDER BY unicodeci ;
resultado:
ção cão
3) utf8_bin
Ele tem como base a comparação bit a bit dos caracteres, resultando então numa comparação case-sensitive, ao contrário dos outros collations.
Ou seja, Em regras gerais há distinção entre letras maiúsculas e minúsculas e acentos.
Vamos ver quatro selects ordenados:
SELECT binario FROM teste ORDER BY binario;
A A Az C N Nz TES TeS Z a cão sao ss são tes Á Ä Ç Ñ Ö ß á ã ção
SELECT binario FROM teste where binario = ‘tes’ ORDER BY binario ;
resultado: tes
SELECT binario FROM teste where binario = ‘cao’ ORDER BY binario ;
resultado: Não retorna dados
SELECT binario FROM teste where binario = ‘cão’ ORDER BY binario ;
resultado: cão
4) utf8_swedish_ci
Collate padrão para o idioma sueco.
Não sei porque, mas assim como o latin_swedish_ci algums brasileiros gostam de usar.
Carece de verificação, mas até onde eu saiba faz comparações entre caracteres no idioma sueco.
Em regras gerais não há distinção entre letras maiúsculas,minúsculas e nem caracteres acentuados e com cedilha (case-insensitive).
Buscando por “tes”, registros como “Tes” ou “TES” serão retornados.
Buscando por “cao”, registros como “ção” ou “cão” será retornado
Em sueco Ä e Ö não são apenas separados de A e O; são as últimas letras do alfabeto
Vamos ver três selects ordenados:
SELECT sueco FROM teste ORDER BY sueco;
resultado:
A ã á a A Á Az Ç C cão ção N Ñ Nz são sao ß ss TeS TES tes Z Ä Ö
SELECT sueco FROM teste where sueco = ‘tes’ ORDER BY sueco ;
resultado: tes TES TeS
SELECT sueco FROM teste where sueco = ‘cao’ ORDER BY sueco ;
resultado:
ção cão.
[size=10pt]E quanto aos indíces? Como fica? [/size]
Para fazermos o teste com unique teremos que apagar uma das linhas com com A.
Vamos fazer um delete limitado:
delete from teste where general = ‘A’ order by general limit 1;
resultado: Registros afetados: 1 Registros encontrados: 0 Avisos: 0 Duração de 1 consulta: 0,250 sec
Após o delete você deverá ter 23 registros dos 24 originais e somente uma linha com ‘A’.
Agora na nossa cabeça estamos prontos para adicionar um índice unique.
Pois para nós, em nosso idioma corrente (português), A é diferente de Á e Ä.
Vamos ver se para a tripa sertaneja MySQL/MariaDB/Percona esta ideia vale?
ALTER TABLE teste ADD UNIQUE INDEX idx_general (gerenal);
resultado: Erro SQL (1062): Duplicate entry ‘N’ for key ‘idx_general’
Apesar de nós brasileiros acharmos estranhos faz todo sentido este erro.
Lembra? O agrupamento genérico é vagamente baseado em inglês, onde Ä = A, N = Ñ e O = Ö
ALTER TABLE teste ADD UNIQUE INDEX idx_unicodeci (unicodeci);
resultado: Erro SQL (1062): Duplicate entry ‘N’ for key ‘idx_general’
Apesar de nós brasileiros acharmos estranhos faz todo sentido este erro.
Lembra? O agrupamento genérico é vagamente baseado em inglês, onde Ä = A, N = Ñ e O = Ö
ALTER TABLE teste ADD UNIQUE INDEX idx_binario (binario);
resultado: Registros afetados: 0 Registros encontrados: 0 Avisos: 0 Duração de 1 consulta: 0,532 sec
Agora sim houve sucesso na criação do unique.
Lembra: Ele tem como base a comparação bit a bit dos caracteres, resultando então numa comparação case-sensitive, ao contrário dos outros collations.
ALTER TABLE teste ADD UNIQUE INDEX idx_sueco (sueco);
resultado: Erro SQL (1062): Duplicate entry ‘N’ for key ‘idx_general’
Apesar de nós brasileiros acharmos estranhos faz todo sentido este erro.
Lembra? O agrupamento genérico é vagamente baseado em inglês, onde Ä = A, N = Ñ e O = Ö
Dá para notar que se quiser usar indíces únicos como chaves primárias, unique e etc terá dificuldades se o seu collate não for utf8_bin e houver registros duplicados na coluna que no inglês seus caracteres sejam considerados “iguais”.
Espero ter ajudado aos indecisos.