table - mysql collation utf8mb4_0900_ai_ci



Solução de problemas "Mix ilegal de agrupamentos" erro no mysql (9)

Estou recebendo o erro abaixo ao tentar fazer um select 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 .


Answer #1

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 banco de dados que foi definido quando a view / function foi criada.

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

  • Alterando o agrupamento do banco de dados:

    ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;

Espero que isso ajude alguém.


Answer #2

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 de todas as 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 isso de mais simples de todos os agrupamentos possíveis a 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 insensível a 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 fornecidos 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 . Os 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 as colações não foram dadas explicitamente na declaração, mas foram implícitas nas fontes das strings (como os metadados da coluna).

  4. Tudo isso está muito bem, mas como alguém resolve tais erros?

    Como os extratos manuais citados acima sugerem, esse 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 chegou: 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 de 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 adicionando uma cláusula COLLATE a uma das strings usadas na comparação, forçará o uso desse agrupamento.

    Enquanto os outros seriam uma péssima prática 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 / variáveis ​​locais resultaria em cadeias 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 via 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 .


Answer #3

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

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.


Answer #4

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 variáveis ​​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 variáveis ​​correspondeu ao agrupamento configurado para a tabela.


Answer #5

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;

Answer #6

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 ...).


Answer #7

Solução se os literais estiverem envolvidos.

Eu estou usando Pentaho Data Integration e não consigo especificar a sintaxe do sql. Usando uma pesquisa de banco de dados 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 visualização foi criada simplesmente 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. :)


Answer #8

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 .


Answer #9

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





collation