wake-up-neo.com

Fehlerbehebung bei "Unzulässiger Mix von Sortierungen" in mysql

Der folgende Fehler wird angezeigt, wenn versucht wird, eine Auswahl durch eine in MySQL gespeicherte Prozedur auszuführen.

Ungültige Mischung von Kollatierungen (latin1_general_cs, IMPLICIT) und (latin1_general_ci, IMPLICIT) für Operation '='

Irgendeine Idee, was hier schief gehen könnte?

Die Sortierung der Tabelle lautet latin1_general_ci und die der Spalte in der where-Klausel ist latin1_general_cs.

177
user355562

Dies wird im Allgemeinen dadurch verursacht, dass zwei Zeichenfolgen inkompatibler Sortierreihenfolge verglichen werden oder versucht wird, Daten unterschiedlicher Sortierreihenfolge in einer kombinierten Spalte auszuwählen.

Mit der Klausel COLLATE können Sie die in der Abfrage verwendete Sortierung angeben.

Die folgende WHERE-Klausel gibt beispielsweise immer den Fehler aus, den Sie gepostet haben:

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

Ihre Lösung besteht darin, eine gemeinsam genutzte Kollatierung für die beiden Spalten in der Abfrage anzugeben. Hier ist ein Beispiel, das die COLLATE-Klausel verwendet:

SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;

Eine weitere Option ist die Verwendung des Operators BINARY:

BINARY str ist die Abkürzung für CAST (str AS BINARY).

Ihre Lösung könnte ungefähr so ​​aussehen:

SELECT * FROM table WHERE BINARY a = BINARY b;

oder,

SELECT * FROM table ORDER BY BINARY a;
183
defines

TL; DR

Ändern Sie entweder die Sortierung einer (oder beider) Zeichenfolgen so, dass sie übereinstimmen, oder fügen Sie Ihrem Ausdruck eine COLLATE -Klausel hinzu.


  1. Was ist das für ein "Kollation" Zeug überhaupt?

    Wie unter - Zeichensätze und Sortierfolgen im Allgemeinen dokumentiert:

    Ein Zeichensatz ist ein Satz von Symbolen und Kodierungen. Eine Sortierung ist eine Reihe von Regeln zum Vergleichen von Zeichen in einem Zeichensatz. Verdeutlichen wir die Unterscheidung anhand eines Beispiels für einen imaginären Zeichensatz.

    Angenommen, wir haben ein Alphabet mit vier Buchstaben: "A", "B", "a", "b". Wir geben jedem Buchstaben eine Nummer: "A" = 0, "B" = 1, "a" = 2, "b" = 3. Der Buchstabe " A ”ist ein Symbol, die Zahl 0 ist die Kodierung für“ A ”und die Kombination aller vier Buchstaben und ihrer Kodierungen ist ein Zeichensatz .

    Angenommen, wir möchten zwei Zeichenfolgenwerte vergleichen, "A" und "B". Am einfachsten ist dies, wenn Sie sich die Codierungen ansehen: 0 für „A“ und 1 für „B“. Da 0 kleiner als 1 ist, sagen wir, dass "A" kleiner als "B" ist. Wir haben gerade eine Sortierung auf unseren Zeichensatz angewendet. Die Kollatierung ist ein Regelsatz (in diesem Fall nur eine Regel): „Vergleichen Sie die Kodierungen.“ Wir bezeichnen diese einfachste aller möglichen Kollatierungen als binär . Kollation.

    Was aber, wenn wir sagen wollen, dass Klein- und Großbuchstaben gleichwertig sind? Dann hätten wir mindestens zwei Regeln: (1) Behandle die Kleinbuchstaben "a" und "b" als äquivalent zu "A" und "B"; (2) Vergleichen Sie dann die Codierungen. Wir nennen dies eine Sortierung ohne Berücksichtigung der Groß-/Kleinschreibung . Es ist etwas komplexer als eine binäre Kollation.

    Im wirklichen Leben haben die meisten Zeichensätze viele Zeichen: nicht nur "A" und "B", sondern ganze Alphabete, manchmal mehrere Alphabete oder östliche Schriftsysteme mit Tausenden von Zeichen, zusammen mit vielen speziellen Symbolen und Satzzeichen. Auch im wirklichen Leben gibt es in den meisten Kollatierungen viele Regeln, nicht nur für die Unterscheidung von Groß- und Kleinschreibung, sondern auch für die Unterscheidung von Akzenten (ein „Akzent“ ist ein Zeichen, das mit einem Buchstaben wie in „_Ö_“ verbunden ist). und für Zuordnungen mit mehreren Zeichen (z. B. die Regel "_Ö_" = "OE" in einer der beiden deutschen Kollationen).

    Weitere Beispiele finden Sie unter Beispiele für den Effekt der Sortierung .

  2. Okay, aber wie entscheidet MySQL, welche Kollatierung für einen bestimmten Ausdruck verwendet wird?

    Wie unter Sortierung der Ausdrücke dokumentiert:

    In den allermeisten Anweisungen ist es offensichtlich, welche Kollatierung MySQL verwendet, um eine Vergleichsoperation aufzulösen. In den folgenden Fällen sollte beispielsweise klar sein, dass die Sortierung die Sortierung der Spalte _charset_name_ ist:

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

    Bei mehreren Operanden kann es jedoch zu Mehrdeutigkeiten kommen. Zum Beispiel:

    _SELECT x FROM T WHERE x = 'Y';
    _

    Soll der Vergleich die Sortierung der Spalte x oder des Zeichenfolgenliteral _'Y'_ verwenden? Sowohl x als auch _'Y'_ haben Kollatierungen. Welche Kollatierung hat also Vorrang?

    Standard-SQL löst solche Fragen mit sogenannten „Coercibility“ -Regeln.

    [ deletia ]

    MySQL verwendet Coercibility-Werte mit den folgenden Regeln, um Mehrdeutigkeiten aufzulösen:

    • Verwenden Sie die Kollatierung mit dem niedrigsten Koerzitivfeldwert.

    • Wenn beide Seiten die gleiche Durchsetzbarkeit haben, dann:

      • Wenn beide Seiten Unicode oder nicht Unicode sind, liegt ein Fehler vor.

      • Wenn eine der Seiten einen Unicode-Zeichensatz und eine andere Seite einen Nicht-Unicode-Zeichensatz hat, gewinnt die Seite mit dem Unicode-Zeichensatz, und die automatische Zeichensatzkonvertierung wird auf die Nicht-Unicode-Seite angewendet. Die folgende Anweisung gibt beispielsweise keinen Fehler zurück:

        _SELECT CONCAT(utf8_column, latin1_column) FROM t1;
        _

        Es wird ein Ergebnis mit dem Zeichensatz _utf8_ und der gleichen Sortierung wie _utf8_column_ zurückgegeben. Werte von _latin1_column_ werden vor der Verkettung automatisch in _utf8_ konvertiert.

      • Für eine Operation mit Operanden aus demselben Zeichensatz, die jedoch eine __bin_-Kollatierung und eine __ci_- oder __cs_-Kollatierung mischen, wird die __bin_-Kollatierung verwendet. Dies ähnelt der Art und Weise, wie Operationen, die nicht-binäre und binäre Zeichenfolgen mischen, die Operanden als binäre Zeichenfolgen auswerten, mit der Ausnahme, dass dies eher für Sortierungen als für Datentypen gilt.

  3. Was ist also eine "illegale Mischung von Kollatierungen"?

    Eine "unzulässige Mischung von Kollatierungen" liegt vor, wenn ein Ausdruck zwei Zeichenfolgen mit unterschiedlichen Kollatierungen vergleicht, jedoch die gleiche Durchsetzbarkeit aufweist und die Durchsetzbarkeitsregeln nicht zur Lösung des Konflikts beitragen können. Dies ist die Situation, die unter dem dritten Aufzählungspunkt im obigen Zitat beschrieben ist.

    Der in der Frage Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '=' angegebene Fehler besagt, dass es einen Gleichheitsvergleich zwischen zwei Nicht-Unicode-Zeichenfolgen mit gleicher Durchsetzbarkeit gab. Darüber hinaus erfahren wir, dass die Kollatierungen nicht explizit in der Anweisung angegeben wurden, sondern aus den Quellen der Zeichenfolgen (z. B. Spaltenmetadaten) impliziert wurden.

  4. Das ist alles sehr gut, aber wie behebt man solche Fehler?

    Wie aus den oben zitierten manuellen Auszügen hervorgeht, kann dieses Problem auf verschiedene Arten gelöst werden, von denen zwei sinnvoll und zu empfehlen sind:

    • Ändern Sie die Sortierung einer (oder beider) Zeichenfolgen so, dass sie übereinstimmen und keine Mehrdeutigkeit mehr besteht.

      Wie dies getan werden kann, hängt davon ab, woher die Zeichenfolge stammt: Literalausdrücke verwenden die in der Systemvariablen collation_connection angegebene Sortierung. Werte aus Tabellen nehmen die in ihren Spaltenmetadaten angegebene Kollatierung an.

    • Erzwinge, dass eine Saite nicht erzwungen werden kann.

      Ich habe das folgende Zitat aus dem obigen weggelassen:

      MySQL weist Koerzibilitätswerte wie folgt zu:

      • Eine explizite COLLATE -Klausel hat eine Durchsetzbarkeit von 0. (Überhaupt nicht durchsetzbar.)

      • Die Verkettung von zwei Zeichenfolgen mit unterschiedlichen Kollatierungen hat eine Koerzitivität von 1.

      • Die Sortierung einer Spalte oder eines gespeicherten Routineparameters oder einer lokalen Variablen hat eine Koerzitivkraft von 2.

      • Eine "Systemkonstante" (die Zeichenfolge, die von Funktionen wie USER() oder VERSION() zurückgegeben wird) hat eine Coerzibilität von 3.

      • Die Kollation eines Literalen hat eine Koerzitivität von 4.

      • NULL oder ein Ausdruck, der von NULL abgeleitet ist, hat eine Coerzibilität von 5.

      Wenn Sie also einfach eine COLLATE -Klausel zu einer der im Vergleich verwendeten Zeichenfolgen hinzufügen, wird die Verwendung dieser Kollatierung erzwungen.

    Während die anderen fürchterlich schlecht wären, wenn sie nur zur Behebung dieses Fehlers eingesetzt würden:

    • Erzwingen Sie, dass eine (oder beide) Zeichenfolgen einen anderen Wert für die Erzwingbarkeit haben, damit eine Zeichenfolge Vorrang hat.

      Die Verwendung von CONCAT() oder CONCAT_WS() würde zu einer Zeichenfolge mit einer Coerzibilität von 1 führen. und (wenn in einer gespeicherten Routine) die Verwendung von Parametern/lokalen Variablen zu Zeichenfolgen mit einer Durchsetzbarkeit von 2 führen würde.

    • Ändern Sie die Codierungen einer (oder beider) Zeichenfolgen, sodass eine Unicode ist und die andere nicht.

      Dies könnte durch Umcodierung mit CONVERT(expr USING transcoding_name) geschehen. oder durch Ändern des zugrunde liegenden Zeichensatzes der Daten (z. B. Ändern der Spalte, Ändern von character_set_connection für Literalwerte oder Senden dieser vom Client in einer anderen Codierung und Änderung character_set_client /Hinzufügen eines Zeichensatz-Einführers). Beachten Sie, dass das Ändern der Codierung zu anderen Problemen führt, wenn einige gewünschte Zeichen im neuen Zeichensatz nicht codiert werden können.

    • Ändern Sie die Codierungen einer (oder beider) Zeichenfolgen so, dass sie identisch sind, und ändern Sie eine Zeichenfolge, um die relevante __bin_-Kollatierung zu verwenden.

      Methoden zum Ändern von Codierungen und Kollatierungen wurden oben detailliert beschrieben. Dieser Ansatz wäre wenig nützlich, wenn tatsächlich erweiterte Kollatierungsregeln angewendet werden müssen, als sie durch die Kollatierung __bin_ geboten werden.

136
eggyal

Hinzufügen meines 2c zur Diskussion für zukünftige Googler.

Ich untersuchte ein ähnliches Problem, bei dem ich die folgende Fehlermeldung erhielt, wenn benutzerdefinierte funktionen verwendet wurden, die einen varchar-Parameter erhalten haben:

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

Verwenden Sie die folgende Abfrage:

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

Ich konnte feststellen, dass der DB utf8_general_ci verwendet, während die Tabellen mit utf8_unicode_ci definiert wurden:

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

Beachten Sie, dass die Ansichten NULL Kollatierung haben. Es scheint, dass Ansichten und Funktionen Sortierdefinitionen haben, obwohl diese Abfrage für eine Ansicht Null anzeigt. Die verwendete Sortierung ist die DB-Sortierung, die beim Erstellen der Ansicht/Funktion definiert wurde.

Die traurige Lösung bestand darin, die DB-Kollatierung zu ändern und die Ansichten/Funktionen neu zu erstellen, um sie zu zwingen, die aktuelle Kollatierung zu verwenden.

  • Ändern der DB-Sortierung:

    ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;
    

Ich hoffe das hilft jemandem.

52
Ariel T

Manchmal kann es gefährlich sein, Zeichensätze zu konvertieren, insbesondere in Datenbanken mit großen Datenmengen. Ich denke, die beste Option ist die Verwendung des "binären" Operators:

e.g : WHERE binary table1.column1 = binary table2.column1
13
Justin Vincent

Ich hatte ein ähnliches Problem und versuchte, die Prozedur FIND_IN_SET mit einer Zeichenfolge variable zu verwenden.

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

und erhielt den Fehler 

Fehlercode: 1267. Unzulässige Mischung von Kollatierungen (utf8_unicode_ci, IMPLICIT) und (utf8_general_ci, IMPLICIT) für die Operation 'find_in_set'

Kurze Antwort:

Sie müssen keine collation_YYYY-Variablen ändern. Fügen Sie einfach die richtige Kollatierung neben Ihrer Variablendeklaration hinzu, d. H.

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

Lange Antwort:

Ich habe zuerst die Kollatierungsvariablen geprüft:

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

Dann habe ich die Tabellensortierung überprüft:

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;

Dies bedeutet, dass meine Variable mit der Standardkollatierung von utf8_general_ci konfiguriert wurde, während meine Tabelle als utf8_unicode_ci konfiguriert wurde.

Durch Hinzufügen des Befehls COLLATE neben der Variablendeklaration stimmte die Variablensortierung mit der für die Tabelle konfigurierten Sortierung überein.

7
nkatsar

Sie können versuchen, dieses Skript , das alle Datenbanken und Tabellen in utf8 konvertiert.

5

Wenn die Spalten, mit denen Sie Probleme haben, "Hashes" sind, sollten Sie Folgendes beachten:.

Wenn der "Hash" eine binäre Zeichenfolge ist, sollten Sie wirklich BINARY(...) Datentyp verwenden.

Wenn der "Hash" eine Hex-Zeichenfolge ist, benötigen Sie kein utf8. Dies sollte durch Zeichenprüfungen usw. vermieden werden. MySQLs MD5(...) liefert beispielsweise eine 32-Byte-Hex-Zeichenfolge fester Länge. SHA1(...) liefert einen 40-Byte-Hex-String. Dies könnte in CHAR(32) CHARACTER SET ascii (oder 40 für sha1) gespeichert werden.

Oder noch besser, speichern Sie UNHEX(MD5(...)) in BINARY(16). Dies halbiert die Größe der Säule. (Dies macht es jedoch nicht druckbar.) SELECT HEX(hash) ..., wenn Sie es lesbar machen möchten.

Beim Vergleich zweier BINARY-Spalten treten keine Probleme bei der Sortierung auf.

1
Rick James

MySQL mag das Mischen von Kollatierungen wirklich nicht, es sei denn, sie können sie zu derselben zwingen (was in Ihrem Fall eindeutig nicht möglich ist). Können Sie nicht einfach die gleiche Kollatierung über eine COLLATE-Klausel verwenden? (oder die einfachere BINARY-Verknüpfung, falls zutreffend ...).

1
Alex Martelli

Lösung, wenn Literale beteiligt sind.

Ich verwende Pentaho Data Integration und kann die SQL-Syntax nicht angeben. Mit einer sehr einfachen DB-Suche wurde der Fehler ausgegeben "Ungültige Mischung von Kollatierungen (cp850_general_ci, COERCIBLE) und (latin1_swedish_ci, COERCIBLE) für Operation '= '"

Der generierte Code war "SELECT DATA_DATE AS latest_DATA_DATE FROM hr_cc_normalised_data_date_v WHERE PSEUDO_KEY =?"

Um die Geschichte kurz zu halten, war die Suche nach einer Ansicht und als ich sie herausgab 

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  |     |
+------------+------------+-------------------+------+-----+

was erklärt, woher der 'cp850_general_ci' kommt.

Die Ansicht wurde einfach mit 'SELECT' X ', ......'.__ erstellt. Laut Handbuch sollten Literale wie diese ihren Zeichensatz und ihre Sortierung von Servereinstellungen erben, die korrekt als' latin1 'und' latin1_general_cs definiert wurden Da dies eindeutig nicht der Fall war, habe ich es bei der Erstellung der Sichtweise erzwungen

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;

jetzt wird latin1_general_cs für beide Spalten angezeigt und der Fehler ist verschwunden. :)

1
jc508

Eine weitere Quelle des Problems mit Sortierungen ist mysql.proc table. Überprüfen Sie Kollatierungen Ihrer Speicherprozeduren und -funktionen:

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

Beachten Sie auch die Spalten mysql.proc.collation_connection und mysql.proc.character_set_client.

0
ruvim

Eine mögliche Lösung ist, die gesamte Datenbank nach UTF8 zu konvertieren (siehe auch diese Frage ).

0
utapyngo

Wenn Sie phpMyAdmin installiert haben, können Sie den Anweisungen unter folgendem Link folgen: https://mediatemple.net/community/products/dv/204403914/default-mysql-character-set-and-collation = Sie müssen die Sammlung der Datenbank mit der aller Tabellen sowie den Feldern der Tabellen abgleichen und anschließend alle gespeicherten Prozeduren und Funktionen neu kompilieren. Damit sollte alles wieder funktionieren.