ATÉ 30 % OFF para compra e renovaÇÃo!

Autor Tópico: Charset utf8 e seus collates no MariaDB/MySQL/Percona  (Lida 103 vezes)

Alexandre Pereira Bühler

  • Expert
  • *****
  • Mensagens: 2048
  • Focalize as oportunidades e não os obstáculos.
    • Simão & Bühler Ltda
    • Email
Charset utf8 e seus collates no MariaDB/MySQL/Percona
« Online: Janeiro 24, 2018, 10:08:07 am »
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


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');

Sobre os collates:

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.

E quanto aos indíces? Como fica?

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.
« Última modificação: Janeiro 24, 2018, 10:10:58 am por Alexandre Pereira Bühler »
--
Alexandre Pereira Bühler
https://www.simaoebuhler.com.br
Hospedagem compartilhada. Temos servidores dedicados.
Eu RTFM todo dia e você?

Kleyber

  • Expert
  • *****
  • Mensagens: 2963
    • TK Informídia
Re:Charset utf8 e seus collates no MariaDB/MySQL/Percona
« Responder #1 Online: Janeiro 24, 2018, 11:35:11 am »
Show de aula, Alexandre!! Obrigado por compartilhar!!
Kleyber Derick

ITIL® V3 Foundation Certified
Analista de Sistemas
São Luís - Maranhão

Jailton

  • Expert
  • *****
  • Mensagens: 2609
Re:Charset utf8 e seus collates no MariaDB/MySQL/Percona
« Responder #2 Online: Janeiro 24, 2018, 11:47:37 am »
Boa Aula Alexandre.

Para evitar dores de cabeça eu e muita gente usa o utf8 como padrão, e deixa os acentos só nos campos memos, nos outros campos,
removemos no OnValidate na hora de gravar com a função:

<?php
/*
Função para tirar os acentos de uma string! pode ser adaptadas para outras coisas

como usar: Sempre usar o $this-> antes das funções.
echo $this->RemoveAcentos("Amanhã não irei caçar");
*/

function RemoveAcentos($Msg) {
   
   $Msg=str_replace('Â', 'A', $Msg);
   $Msg=str_replace('À', 'A', $Msg);
   $Msg=str_replace('Á', 'A', $Msg);
   $Msg=str_replace('Ä', 'A', $Msg);
   $Msg=str_replace('Ã', 'A', $Msg);

   $Msg=str_replace('â', 'a', $Msg);
   $Msg=str_replace('ã', 'a', $Msg);
   $Msg=str_replace('à', 'a', $Msg);
   $Msg=str_replace('á', 'a', $Msg);
   $Msg=str_replace('ä', 'a', $Msg);

   $Msg=str_replace('Ê', 'E', $Msg);
   $Msg=str_replace('È', 'E', $Msg);
   $Msg=str_replace('É', 'E', $Msg);
   $Msg=str_replace('Ë', 'E', $Msg);

   $Msg=str_replace('ê', 'e', $Msg);
   $Msg=str_replace('è', 'e', $Msg);
   $Msg=str_replace('é', 'e', $Msg);
   $Msg=str_replace('ë', 'e', $Msg);

   $Msg=str_replace('Î', 'I', $Msg);
   $Msg=str_replace('Í', 'I', $Msg);
   $Msg=str_replace('Ì', 'I', $Msg);
   $Msg=str_replace('Ï', 'I', $Msg);

   $Msg=str_replace('î', 'i', $Msg);
   $Msg=str_replace('í', 'i', $Msg);
   $Msg=str_replace('ì', 'i', $Msg);
   $Msg=str_replace('ï', 'i', $Msg);

   $Msg=str_replace('Ô', 'O', $Msg);
   $Msg=str_replace('Õ', 'O', $Msg);
   $Msg=str_replace('Ò', 'O', $Msg);
   $Msg=str_replace('Ó', 'O', $Msg);
   $Msg=str_replace('Ö', 'O', $Msg);

   $Msg=str_replace('ô', 'o', $Msg);
   $Msg=str_replace('õ', 'o', $Msg);
   $Msg=str_replace('ò', 'o', $Msg);
   $Msg=str_replace('ó', 'o', $Msg);
   $Msg=str_replace('ö', 'o', $Msg);

   $Msg=str_replace('Û', 'U', $Msg);
   $Msg=str_replace('Ù', 'U', $Msg);
   $Msg=str_replace('Ú', 'U', $Msg);
   $Msg=str_replace('Ü', 'U', $Msg);

   $Msg=str_replace('û', 'U', $Msg);
   $Msg=str_replace('ú', 'U', $Msg);
   $Msg=str_replace('ù', 'U', $Msg);
   $Msg=str_replace('ü', 'U', $Msg);
   
   $Msg=str_replace(chr(9),'', $Msg);      
   $Msg=str_replace(chr(92),'', $Msg);   
   $Msg=str_replace(chr(34),'', $Msg);
   $Msg=str_replace(chr(39),'', $Msg);
   $Msg=str_replace('ç', 'c', $Msg);
   $Msg=str_replace('Ç', 'C', $Msg);
   $Msg=str_replace('ª', 'a.', $Msg);
   $Msg=str_replace('º', 'o.', $Msg);
   
   $String="";

   for ($j = 0;$j <= strlen($Msg); $j++) {
      if (ord(substr($Msg, $j,1))<=122 OR ord(substr($Msg, $j,1))==124) {  // 124 = | Usado nos arquivos do SPED.
         $String=$String . substr($Msg, $j, 1);
      }
   }
   
    $Msg=$String;
   
   return $Msg;

} // Fim da Função: RemoveAcentos($Msg)

?>
O Princípio da Vibração. "Nada está parado, tudo se move, tudo vibra". Caibalion.

Kleyber

  • Expert
  • *****
  • Mensagens: 2963
    • TK Informídia
Re:Charset utf8 e seus collates no MariaDB/MySQL/Percona
« Responder #3 Online: Janeiro 24, 2018, 09:04:13 pm »
Eu também uso UTF-8, Jailton, só que nos campos texto eu defino quais tipos de caracteres serão digitados. Aí não tenho dores de cabeça com isto.
Kleyber Derick

ITIL® V3 Foundation Certified
Analista de Sistemas
São Luís - Maranhão