Consultas case-insensitive e accent-insensitive no MySQL

Uma necessidade comum com dados em língua portuguesa são as buscas no bancos de dados insensível a caso e insensível a acentos.

No MySQL, até a versão 4.0, as consultas eram por padrão insensível ao caso (case-insensitive) e insensível ao acento (accent-insensitive). Isso mudou, porém, a partir da versão 4.1, que introduziu um suporte melhorado a comparações (collations) e definições de caracteres (charsets). Alguns desenvolvedores devem ter ficado surpresos com suas buscas que antes ignoravam acentos e maiúsculas e agora já exigiam que se colocasse.

A partir dessa versão, a sintaxe para uma consulta que ignora acentos e o caso seria a seguinte:

SELECT *
FROM `tab_municipios`
WHERE `NomeMunic` = _utf8 'SAO PAULO' COLLATE utf8_unicode_ci

Adaptado de Consultas case-insensitive no PostgreSQL e no MySQL

16 pensamentos em “Consultas case-insensitive e accent-insensitive no MySQL”

  1. o problema deste método é que se fizer a consulta com o acento, não vai funcionar.

    Por exemplo: SELECT *

    FROM `tab_municipios`

    WHERE `NomeMunic` = _utf8 'SÃO PAULO' COLLATE utf8_unicode_ci

  2. Fabrizio, a consulta é justamente para prever o uso ou não do acento. A consulta com acento irá funcionar.

    Tanto a minha consulta quanto a sua retornam o registro com ou sem o acento.

  3. Hélio, pode ser a codificação da instalação. De repente você pode fazer a busca diretamente sem o artifício de usar utf8, se a codificação da instalação já for esta.

  4. Você saberia me dizer se no caso de o registro no bd estar acentuado como eu faço para que uma consulta por uma keyword não acentuada funcione?

    Ex.: Tabela com campo nome e um registro com valor 'João'. Quando pesquisar por 'Joao' teria que ser encontrado o registro com o acento.

    1. Bruno, o que costumo fazer é sempre comparar de forma desacentuada. A desacentuação pode ser feita na linguagem de programação ou no banco de dados. Como é algo recorrente, crie uma função.
      Ex: (php)
      <code>$termo = desacentua($termo); </code>
      Ou
      (sql)
      <code>WHERE desacentua(campo) LIKE desacentua('%termo%')</code>

  5. Rafael, achei o problema que tinha com pesquisa…

    Como acesso mysql via php, não estava setando o charset:

    php.net/mysql_set_charset

    Agora funciona tudo transparente… sem nenhuma gambiarra

  6. Achei esta página, tendo um problema igual.

    Para quem não funciona:
    a) usem UTF8_unicode_ci (não general, o “ci” é “case insensitive) em TUDO (banco de dados, tabela, arquivo, HTML)
    b) ao se conectar ao BD usando PHP/mysql, o primeiro command deve ser “SET NAMES utf8”; -> eis o problema, o library MySQL usa Latin-1 como padrão
    c) convertem seu BD para unicode verdadeiro. Verdadeiro que dizer que no banco não deve ter “Ã!” e essas coisas, mas “ãçõé” etc. mesmo.

    Abraços!

  7. Meus prezados, temos uma solução pareativa na consulta sql, basta fazer a pesquisa das duas formas e usar DISTINCT para eliminar possíveis resultados duplicados:

    SELECT DISTINCT * FROM nome_tabela WHERE nome_campo LIKE _utf8 ‘%$consulta%’ COLLATE utf8_unicode_ci OR nome_campo LIKE ‘%$consulta%’

    Valeu!

  8. Ótima dica funciona!!!
    Assim não importa se foi digitado com acentuação ou maiúsculo e minusculo que vai ser consultado no banco.
    Parabéns

Deixe uma resposta