Dépannage de l'erreur "Mélange illégal de classements" dans mysql

Question

Je reçois le message d'erreur ci-dessous lorsque je tente d'effectuer une sélection via une procédure stockée dans MySQL.

Mélange illégal de collations (latin1_general_cs, IMPLICIT) et (latin1_general_ci, IMPLICIT) pour l'opération '='

Une idée sur ce qui pourrait mal se passer ici?

La collation de la table est latin1_general_ci et celle de la colonne de la clause where est latin1_general_cs .

Answer 1

Cela est généralement causé par la comparaison de deux chaînes de l'incompatibilité de classement ou en tentant de sélectionner les données de classement différent dans un combiné de la colonne.

La clause COLLATE vous permet de spécifier le classement utilisé dans la requête.

Par exemple, le WHERE clause de toujours donner l'erreur que vous avez posté:

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

Votre solution est de spécifier un partage de classement pour les deux colonnes dans la requête. Voici un exemple qui utilise de l' COLLATE clause de:

SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;

Une autre option est d'utiliser l' BINARY opérateur:

BINAIRE str est une abréviation de " CAST(str COMME BINAIRE).

Votre solution pourrait ressembler à quelque chose comme ceci:

SELECT * FROM table WHERE BINARY a = BINARY b;

Ou,

SELECT * FROM table ORDER BY BINARY a;

Answer 2

TL;DR

Soit modifier le classement d'un (ou les deux) des cordes de sorte qu'ils correspondent, ou bien ajouter un COLLATE clause à votre expression.

---

1. Qu'est-ce que ce "classement" des trucs de toute façon?

   Comme l'a documenté en vertu des Jeux de Caractères et les Classements en Général:

   Un jeu de caractères est un ensemble de symboles et de codages. Un classement est un ensemble de règles pour la comparaison des caractères dans un jeu de caractères. Nous allons faire la distinction clair avec un exemple d'un imaginaire jeu de caractères.

   Supposons que nous disposons d'un alphabet à quatre lettres: "A", "B", "a", "b". Nous donnons à chaque lettre un nombre: "A" = 0, "B" = 1, "a" = 2, "b" = 3. La lettre "A" est un symbole, le nombre 0 est l' encodage pour "A", et la combinaison de tous les quatre lettres et de leur encodage est un jeu de caractères.

   Supposons que l'on veut comparer deux valeurs de chaîne, "A" et "B". Le moyen le plus simple pour ce faire est de regarder les codages: 0 pour "A" et 1 pour "B". Parce que 0 est inférieur à 1, on dit "A" est inférieur à "B". Ce que nous venons de faire est d'appliquer un classement à notre jeu de caractères. Le classement est un ensemble de règles (une seule règle dans ce cas): "comparer les codages." Nous appelons cela le plus simple de tous les possibles classements un binaire de classement.

   Mais que faire si nous voulons dire que les minuscules et les majuscules sont-elles équivalentes? Ensuite, nous aurions au moins deux règles: (1) traiter les lettres minuscules "a" et "b" comme équivalent de "A" et "B"; (2), puis comparer les codages. Nous appelons cela un casse classement. C'est un peu plus complexe qu'un classement binaire.

   Dans la vraie vie, la plupart des jeux de caractères nombre de caractères: pas seulement "A" et "B" mais l'ensemble des alphabets, parfois plusieurs alphabets ou orientale systèmes d'écriture avec des milliers de personnages, avec de nombreux symboles et des signes de ponctuation. Aussi dans la vraie vie, la plupart des classements ont beaucoup de règles, pas juste pour savoir si pour distinguer lettercase, mais aussi pour savoir si de distinguer les accents (un "accent" est une marque attachée à un personnage comme dans l'allemand "Ö"), et pour plusieurs mappages de caractères (comme la règle de la "Ö" = "OE" dans l'un des deux allemands classements).

   D'autres exemples sont donnés des Exemples de l'Effet de Classement.

2. Ok, mais comment MySQL déterminer le classement à utiliser pour une expression donnée?

   Comme l'a documenté en vertu de Classement des Expressions:

   Dans la grande majorité des états, il est évident que ce classement MySQL utilise pour résoudre une opération de comparaison. Par exemple, dans les cas suivants, il devrait être clair que le classement est le classement de la colonne charset_name:

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

   Cependant, avec plusieurs opérandes, il peut y avoir ambiguïté. Par exemple:

   SELECT x FROM T WHERE x = 'Y';
   

   Si la comparaison utilise le classement de la colonne x, ou de la chaîne de caractères littérale 'Y'? Les deux x et 'Y' ont des classements, de sorte que le classement l'emporte?

   Le Standard SQL résout ces questions à l'aide de ce que l'on appelait "coercibility" les règles.

   [ deletia ]

   MySQL utilise coercibility valeurs avec les règles suivantes pour résoudre les ambiguïtés:

   • Utilisez le classement le plus bas coercibility valeur.

   • Si les deux côtés ont la même coercibility, puis:

     • Si les deux côtés sont en Unicode, ou les deux côtés ne sont pas Unicode, c'est une erreur.

     • Si l'un des côtés a un jeu de caractères Unicode, et d'un autre côté a un non-jeu de caractères Unicode, le côté avec le jeu de caractères Unicode victoires, et le caractère automatique de conversion est appliqué à la non-Unicode côté. Par exemple, l'instruction suivante ne retourne pas une erreur:

       SELECT CONCAT(utf8_column, latin1_column) FROM t1;
       

       Il renvoie un résultat qui a un jeu de caractères utf8 et le même classement que utf8_column. Les valeurs de latin1_column sont automatiquement converties en utf8 avant la concaténation.

     • Pour un fonctionnement avec des opérandes à partir du même jeu de caractères mais qui mélange un _bin classement et une _ci ou _cs classement, l' _bin classement est utilisé. Ceci est similaire à la façon dont les opérations qui mélange non binaire et binaire des chaînes d'évaluer les opérandes comme des chaînes binaires, sauf que c'est pour les classements plutôt que de types de données.

3. Donc ce qui est "illégal mélange de classements"?

   "Illégaux mélange de classements" se produit lorsqu'une expression compare deux chaînes de caractères des différents classements, mais de même coercibility et la coercibility règles ne peuvent pas aider à résoudre le conflit. C'est la situation décrite dans le troisième point dans la citation ci-dessus.

   L'erreur donnée dans la question, Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '=', nous dit qu'il y avait une comparaison d'égalité entre deux non-Unicode de l'égalité des coercibility. En outre, il nous dit que les classements n'ont pas été donnés explicitement dans l'énoncé, mais plutôt ont été déduits à partir des cordes sources (telles que les colonnes de métadonnées).

4. C'est très bien, mais comment fait-on résoudre de telles erreurs?

   Comme le manuel de l'extrait cité ci-dessus le suggère, ce problème peut être résolu de plusieurs façons, dont deux sont sensibles et être recommandé:

   • Modifier le classement d'un (ou les deux) des cordes de sorte qu'ils correspondent et il n'y a plus aucune ambiguïté.

     Comment cela peut être fait dépend d'où la chaîne a venir: Littéral des expressions de prendre le classement spécifié dans l' collation_connection variable système; les valeurs dans les tables de prendre le classement spécifié dans leur colonne de métadonnées.

   • La Force d'une chaîne pour ne pas être contrainte.

     J'ai omis la citation suivante à partir de ci-dessus:

     MySQL attribue coercibility valeurs comme suit:

     • Explicite COLLATE clause a un coercibility de 0. (Pas de contrainte).

     • La concaténation de deux chaînes avec différents classements a un coercibility de 1.

     • Le classement d'une colonne ou d'un stockées routine paramètre ou une variable locale a un coercibility de 2.

     • Un "système constant" (la chaîne de caractères retournée par les fonctions telles que USER() ou VERSION()) a un coercibility de 3.

     • Le classement d'un littéral est une coercibility de 4.

     • NULL ou une expression qui est dérivée à partir de NULL a un coercibility de 5.

     Donc il suffit de rajouter un COLLATE de la clause à l'une des chaînes utilisées dans la comparaison permettra de forcer l'utilisation de ce classement.

   Tandis que les autres seraient terriblement mauvaises pratiques, si elles étaient déployées simplement pour résoudre cette erreur:

   • La Force de l'un (ou les deux) des chaînes avoir quelques autres coercibility valeur, de sorte que l'on prend la priorité.

     L'utilisation d' CONCAT() ou CONCAT_WS() entraînerait une chaîne avec un coercibility de 1; et si, dans un stockées routine) utilisation des paramètres/variables locales entraînerait dans des chaînes avec un coercibility de 2.

   • Modifier les codages de l'un (ou les deux) des chaînes de sorte que l'on est en Unicode et l'autre ne l'est pas.

     Cela pourrait être fait par le biais de transcodage avec CONVERT(; ou par la modification de la sous-jacentes jeu de caractères des données (par exemple, la modification de la colonne, l'évolution expr pour des valeurs littérales, ou de les envoyer par le client dans un autre encodage et l'évolution USING / ajout d'un jeu de caractères introducteur). Notez que la modification de l'encodage conduire à d'autres problèmes si certains caractères désirés ne peuvent pas être codées dans le nouveau jeu de caractères.

   • Modifier les codages de l'un (ou les deux) des cordes de sorte qu'ils sont à la fois le même et de changer une chaîne de caractères à utiliser les transcoding_name classement.

     Méthodes pour modifier les codages et les classements ont été détaillées ci-dessus. Cette approche serait de peu d'utilité si l'on a réellement besoin d'appliquer plus avancé règles de classement que sont offerts par l' ) classement.

Answer 3

L'ajout de mon 2c à la discussion pour l'avenir googlers.

J'enquêtais sur un problème similaire où j'ai eu l'erreur suivante lors de l'aide personnalisée fonctions qui a reçu un varchar paramètre:

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

À l'aide de la requête suivante:

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

J'ai été en mesure de dire que la DB a l'aide de utf8_general_ci, tandis que les tableaux ont été définis à l'aide de utf8_unicode_ci:

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

Notez que les points de vue ont NULLE classement. Il semble que les vues et les fonctions de classement définitions, même si cette requête affiche la valeur null pour un point de vue. Le classement utilisé est le DB classement qui a été défini lors de l'affichage/fonction ont été créés.

Le plus triste, la solution est à la fois modifier la db de classement et de recréer les points de vue et les fonctions pour les forcer à utiliser le classement actuel.

• La modification de la db du classement:

  ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;
  

J'espère que cela aidera quelqu'un.

Answer 4

Parfois, il peut être dangereux de convertir des jeux de caractères, en particulier sur des bases de données contenant d’énormes quantités de données. Je pense que la meilleure option est d'utiliser l'opérateur "binaire":

 e.g : WHERE binary table1.column1 = binary table2.column1
 

Answer 5

Vous pouvez essayer ce script , qui convertit toutes vos bases de données et tables en utf8.