Solução de problemas “Mix ilegal de agrupamentos” erro no mysql

Estou recebendo o erro abaixo ao tentar fazer uma seleção através de um procedimento armazenado no MySQL.

Mix ilegal de agrupamentos (latin1_general_cs, IMPLICIT) e (latin1_general_ci, IMPLICIT) para operação ‘=’

Alguma ideia do que pode estar errado aqui?

O agrupamento da tabela é latin1_general_ci e o da coluna na cláusula latin1_general_cs é latin1_general_cs .

Isso geralmente é causado pela comparação de duas cadeias de intercalação incompatível ou pela tentativa de selecionar dados de diferentes agrupamentos em uma coluna combinada.

A cláusula COLLATE permite especificar o agrupamento usado na consulta.

Por exemplo, a seguinte cláusula WHERE sempre fornecerá o erro que você postou:

 WHERE 'A' COLLATE latin1_general_ci = 'A' COLLATE latin1_general_cs 

Sua solução é especificar um agrupamento compartilhado para as duas colunas na consulta. Aqui está um exemplo que usa a cláusula COLLATE :

 SELECT * FROM table ORDER BY key COLLATE latin1_general_ci; 

Outra opção é usar o operador BINARY :

BINARY str é a abreviação de CAST (str AS BINARY).

Sua solução pode ser algo como isto:

 SELECT * FROM table WHERE BINARY a = BINARY b; 

ou,

 SELECT * FROM table ORDER BY BINARY a; 

TL; DR

Altere o agrupamento de uma (ou ambas) das cadeias para que elas correspondam ou, então, adicione uma cláusula COLLATE à sua expressão.


  1. O que é esse material de “colação”?

    Conforme documentado em Conjuntos de caracteres e agrupamentos em geral :

    Um conjunto de caracteres é um conjunto de símbolos e codificações. Um agrupamento é um conjunto de regras para comparar caracteres em um conjunto de caracteres. Vamos esclarecer a distinção com um exemplo de um conjunto de caracteres imaginário.

    Suponha que tenhamos um alfabeto com quatro letras: “ A ”, “ B ”, “ a ”, “ b ”. Nós damos a cada letra um número: “ A ” = 0, “ B ” = 1, “ a ” = 2, “ b ” = 3. A letra “ A ” é um símbolo, o número 0 é a codificação para “ A ” e a combinação das quatro letras e suas codificações é um conjunto de caracteres .

    Suponha que queremos comparar dois valores de string, ” A ” e ” B “. A maneira mais simples de fazer isso é observar as codificações: 0 para “ A ” e 1 para “ B ”. Como 0 é menor que 1, dizemos que ” A ” é menor que ” B “. O que acabamos de fazer é aplicar um agrupamento ao nosso conjunto de caracteres. O agrupamento é um conjunto de regras (apenas uma regra neste caso): “compare as codificações”. Chamamos esse mais simples de todos os agrupamentos possíveis de um agrupamento binário .

    Mas e se quisermos dizer que as letras minúsculas e maiúsculas são equivalentes? Então teríamos pelo menos duas regras: (1) tratar as letras minúsculas “ a ” e “ b ” como equivalentes a “ A ” e “ B ”; (2) então compare as codificações. Chamamos isso de agrupamento sem distinção entre maiúsculas e minúsculas . É um pouco mais complexo que um agrupamento binário.

    Na vida real, a maioria dos conjuntos de caracteres tem muitos caracteres: não apenas “ A ” e “ B ”, mas alfabetos inteiros, às vezes múltiplos alfabetos ou sistemas de escrita orientais com milhares de caracteres, juntamente com muitos símbolos especiais e sinais de pontuação. Também na vida real, a maioria dos collations tem muitas regras, não apenas para distinguir letras, mas também para distinguir acentos (um “acento” é uma marca anexada a um caractere como em alemão “ Ö ”), e para múltiplos. mapeamentos de caracteres (como a regra que ” Ö ” = ” OE ” em um dos dois agrupamentos alemães).

    Outros exemplos são dados em Exemplos do efeito de agrupamento .

  2. Ok, mas como o MySQL decide qual agrupamento usar para uma determinada expressão?

    Conforme documentado em Collation of Expressions :

    Na grande maioria das declarações, é óbvio qual o collation que o MySQL usa para resolver uma operação de comparação. Por exemplo, nos seguintes casos, deve ficar claro que o agrupamento é o agrupamento da coluna charset_name :

     SELECT x FROM T ORDER BY x; SELECT x FROM T WHERE x = x; SELECT DISTINCT x FROM T; 

    No entanto, com vários operandos, pode haver ambiguidade. Por exemplo:

     SELECT x FROM T WHERE x = 'Y'; 

    A comparação deve usar o agrupamento da coluna x ou da string literal 'Y' ? Tanto x quanto 'Y' possuem agrupamentos, de modo que o agrupamento tem precedência?

    O SQL padrão resolve essas questões usando o que costumava ser chamado de regras de “coercibilidade”.

      [ deletia ] 

    O MySQL usa valores de coercibilidade com as seguintes regras para resolver ambiguidades:

    • Use o agrupamento com o menor valor de coercibilidade.

    • Se ambos os lados tiverem a mesma coercibilidade, então:

      • Se ambos os lados forem Unicode ou ambos os lados não forem Unicode, isso é um erro.

      • Se um dos lados tiver um conjunto de caracteres Unicode e outro lado tiver um conjunto de caracteres não-Unicode, o lado com o conjunto de caracteres Unicode vence e a conversão automática de conjuntos de caracteres é aplicada ao lado não-Unicode. Por exemplo, a seguinte instrução não retorna um erro:

         SELECT CONCAT(utf8_column, latin1_column) FROM t1; 

        Ele retorna um resultado que possui um conjunto de caracteres de utf8 e o mesmo agrupamento que utf8_column . Valores de latin1_column são convertidos automaticamente para utf8 antes da concatenação.

      • Para uma operação com operandos do mesmo conjunto de caracteres, mas que mistura um agrupamento _bin e um agrupamento _ci ou _cs , o agrupamento _bin é usado. Isso é semelhante a como as operações que misturam strings não binárias e binárias avaliam os operandos como cadeias binárias, exceto que é para agrupamentos em vez de tipos de dados.

  3. Então, o que é uma “mistura ilegal de agrupamentos”?

    Uma “mistura ilegal de agrupamentos” ocorre quando uma expressão compara duas sequências de diferentes agrupamentos, mas de igual coercibilidade, e as regras de coercibilidade não podem ajudar a resolver o conflito. É a situação descrita sob o terceiro ponto na citação acima.

    O erro específico fornecido na pergunta Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '=' , nos diz que houve uma comparação de igualdade entre duas cadeias não-Unicode de igual coercibilidade. Além disso, ele nos diz que os agrupamentos não foram fornecidos explicitamente na declaração, mas foram implícitos nas fonts das strings (como os metadados da coluna).

  4. Está tudo muito bem, mas como alguém resolve tais erros?

    Como os extratos manuais citados acima sugerem, este problema pode ser resolvido de várias maneiras, das quais duas são sensatas e recomendadas:

    • Altere o agrupamento de uma (ou ambas) das cadeias para que correspondam e não haja mais qualquer ambiguidade.

      Como isso pode ser feito depende de onde a cadeia veio: Expressões literais levam o agrupamento especificado na variável de sistema collation_connection ; os valores das tabelas recebem o agrupamento especificado nos metadados da coluna.

    • Forçar uma string a não ser coercível.

      Eu omiti a seguinte citação do acima:

      O MySQL atribui valores de coercibilidade da seguinte forma:

      • Uma cláusula explícita COLLATE tem uma coercibilidade de 0. (não coercível em tudo).

      • A concatenação de duas cadeias com diferentes agrupamentos tem uma coercibilidade de 1.

      • O agrupamento de uma coluna ou um parâmetro de rotina armazenado ou variável local tem uma coercibilidade de 2.

      • Uma “constante do sistema” (a string retornada por funções como USER() ou VERSION() ) tem uma coercibilidade de 3.

      • O agrupamento de um literal tem uma coercibilidade de 4.

      • NULL ou uma expressão derivada de NULL tem uma coercibilidade de 5.

      Assim, simplesmente adicionar uma cláusula COLLATE a uma das cadeias usadas na comparação forçará o uso dessa comparação.

    Enquanto os outros seriam uma prática terrivelmente ruim se fossem implantados apenas para resolver esse erro:

    • Force uma (ou ambas) das cadeias a ter algum outro valor de coercibilidade para que uma tenha precedência.

      Uso de CONCAT() ou CONCAT_WS() resultaria em uma seqüência de caracteres com uma coercibilidade de 1; e (se em uma rotina armazenada) o uso de parâmetros / variables ​​locais resultaria em seqüências de caracteres com uma coercibilidade de 2.

    • Altere as codificações de uma (ou ambas) das cadeias para que uma seja Unicode e a outra não.

      Isso pode ser feito por transcodificação com CONVERT( expr USING transcoding_name ) ; ou alterando o conjunto de caracteres subjacentes dos dados (por exemplo, modificando a coluna, alterando character_set_connection para valores literais ou enviando-os do cliente em uma codificação diferente e alterando character_set_client / adicionando um introdutor de conjunto de caracteres). Observe que a alteração da codificação causará outros problemas se alguns caracteres desejados não puderem ser codificados no novo conjunto de caracteres.

    • Altere as codificações de uma (ou ambas) das cadeias para que elas sejam as mesmas e altere uma cadeia para usar o agrupamento _bin relevante.

      Métodos para alterar codificações e agrupamentos foram detalhados acima. Essa abordagem seria de pouca utilidade se realmente fosse necessário aplicar regras de agrupamento mais avançadas do que as oferecidas pelo agrupamento _bin .

Adicionando meu 2c à discussão para futuros googlers.

Eu estava investigando um problema semelhante em que recebi o seguinte erro ao usar funções personalizadas que recebiam um parâmetro varchar:

 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' 

Usando a seguinte consulta:

 mysql> show variables like "collation_database"; +--------------------+-----------------+ | Variable_name | Value | +--------------------+-----------------+ | collation_database | utf8_general_ci | +--------------------+-----------------+ 

Eu era capaz de dizer que o DB estava usando utf8_general_ci , enquanto as tabelas foram definidas usando utf8_unicode_ci :

 mysql> show table status; +--------------+-----------------+ | Name | Collation | +--------------+-----------------+ | my_view | NULL | | my_table | utf8_unicode_ci | ... 

Observe que as visualizações têm agrupamento NULL . Parece que as visualizações e funções têm definições de agrupamento, embora essa consulta mostre nulo para uma exibição. O agrupamento usado é o agrupamento do database que foi definido quando a view / function foi criada.

A triste solução foi alterar o agrupamento do database e recriar as visualizações / funções para forçá-las a usar o agrupamento atual.

  • Alterando o agrupamento do database:

     ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci; 

Espero que isso ajude alguém.

Às vezes pode ser perigoso converter charsets, especialmente em bancos de dados com grandes quantidades de dados. Eu acho que a melhor opção é usar o operador “binário”:

 eg : WHERE binary table1.column1 = binary table2.column1 

Você pode tentar este script , que converte todos os seus bancos de dados e tabelas para utf8.

Eu tive um problema semelhante, estava tentando usar o procedimento FIND_IN_SET com uma variável de seqüência de caracteres.

 SET @my_var = 'string1,string2'; SELECT * from my_table WHERE FIND_IN_SET(column_name,@my_var); 

e estava recebendo o erro

Código de erro: 1267. Mistura ilegal de agrupamentos (utf8_unicode_ci, IMPLICIT) e (utf8_general_ci, IMPLICIT) para operação ‘find_in_set’

Resposta curta:

Não é necessário alterar nenhuma variável collation_YYYY, basta adicionar o agrupamento correto ao lado de sua declaração de variável , ou seja,

 SET @my_var = 'string1,string2' COLLATE utf8_unicode_ci; SELECT * from my_table WHERE FIND_IN_SET(column_name,@my_var); 

Resposta longa:

Eu verifiquei pela primeira vez as variables ​​de agrupamento:

 mysql> SHOW VARIABLES LIKE 'collation%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | +----------------------+-----------------+ | collation_database | utf8_general_ci | +----------------------+-----------------+ | collation_server | utf8_general_ci | +----------------------+-----------------+ 

Então eu verifiquei o agrupamento da tabela:

 mysql> SHOW CREATE TABLE my_table; CREATE TABLE `my_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `column_name` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=125 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

Isso significa que minha variável foi configurada com o agrupamento padrão de utf8_general_ci enquanto minha tabela foi configurada como utf8_unicode_ci .

Adicionando o comando COLLATE ao lado da declaração de variável, o agrupamento de variables ​​correspondeu ao agrupamento configurado para a tabela.

O MySQL realmente não gosta de misturar collations a menos que possa coagi-los ao mesmo (o que claramente não é viável no seu caso). Você não pode simplesmente forçar o mesmo agrupamento a ser usado através de uma cláusula COLLATE ? (ou o atalho BINARY mais simples se aplicável …).

Solução se os literais estiverem envolvidos.

Eu estou usando Pentaho Data Integration e não consigo especificar a syntax do sql. Usando uma pesquisa de database muito simples, ocorreu o erro “Mix ilegal de agrupamentos (cp850_general_ci, COERCIBLE) e (latin1_swedish_ci, COERCIBLE) para operação ‘='”

O código gerado foi “SELECT DATA_DATE AS latest_DATA_DATE FROM hr_cc_normalised_data_date_v WHERE PSEUDO_KEY =?”

Cortando a história curta a pesquisa foi para uma visão e quando eu emiti

 mysql> show full columns from hr_cc_normalised_data_date_v; +------------+------------+-------------------+------+-----+ | Field | Type | Collation | Null | Key | +------------+------------+-------------------+------+-----+ | PSEUDO_KEY | varchar(1) | cp850_general_ci | NO | | | DATA_DATE | varchar(8) | latin1_general_cs | YES | | +------------+------------+-------------------+------+-----+ 

que explica de onde vem o ‘cp850_general_ci’.

A view foi simplesmente criada com ‘SELECT’ X ‘, ……’ De acordo com os literais manuais como este deve herdar seu conjunto de caracteres e collation de configurações do servidor que foram corretamente definidas como ‘latin1’ e ‘latin1_general_cs’ como esta claramente não aconteceu eu forcei na criação da visão

 CREATE OR REPLACE VIEW hr_cc_normalised_data_date_v AS SELECT convert('X' using latin1) COLLATE latin1_general_cs AS PSEUDO_KEY , DATA_DATE FROM HR_COSTCENTRE_NORMALISED_mV LIMIT 1; 

Agora ele mostra latin1_general_cs para ambas as colunas e o erro desapareceu. 🙂

Se as colunas com as quais você está tendo problemas forem “hashes”, considere o seguinte …

Se o “hash” for uma cadeia binária, você deve realmente usar o tipo de dados BINARY(...) .

Se o “hash” for uma string hexadecimal, você não precisa do utf8 e deve evitar isso por causa de checagens de caracteres, etc. Por exemplo, o MD5(...) do MySQL gera uma cadeia hexadecimal de 32 bytes. SHA1(...) fornece uma cadeia hexadecimal de 40 bytes. Isso pode ser armazenado em CHAR(32) CHARACTER SET ascii (ou 40 para sha1).

Ou, melhor ainda, armazene UNHEX(MD5(...)) em BINARY(16) . Isso corta metade do tamanho da coluna. (Isso, no entanto, torna bastante imprimível.) SELECT HEX(hash) ... se você quiser legível.

Comparar duas colunas BINARY não tem problemas de agrupamento.

Uma possível solução é converter o database inteiro em UTF8 (veja também esta questão ).

Uma outra fonte do problema com agrupamentos é a tabela mysql.proc . Verifique agrupamentos de seus procedimentos e funções de armazenamento:

 SELECT p.db, p.db_collation, p.type, COUNT(*) cnt FROM mysql.proc p GROUP BY p.db, p.db_collation, p.type; 

Preste também atenção às colunas mysql.proc.character_set_client e mysql.proc.character_set_client .

Eu usei ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci; , mas não funcionou.

Nesta consulta:

 Select * from table1, table2 where table1.field = date_format(table2.field,'%H'); 

Esse trabalho pra mim:

 Select * from table1, table2 where concat(table1.field) = date_format(table2.field,'%H'); 

Sim, apenas um concat .

Esse código precisa ser colocado dentro de Executar consultas / consultas SQL no database

SQL QUERY WINDOW

 ALTER TABLE `table_name` CHANGE `column_name` `column_name` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL; 

Por favor substitua table_name e column_name pelo nome apropriado.

    Intereting Posts