Risoluzione dei problemi dell’errore “Illegal mix of collations” in mysql

Sto ottenendo l’errore di sotto quando provo a fare una selezione attraverso una stored procedure in MySQL.

Mix illegale di regole di confronto (latin1_general_cs, IMPLICIT) e (latin1_general_ci, IMPLICIT) per l’operazione ‘=’

Qualche idea su cosa potrebbe andare storto qui?

Le regole di confronto della tabella sono latin1_general_ci e quella della colonna nella clausola latin1_general_cs è latin1_general_cs .

    Ciò è in genere causato confrontando due stringhe di regole di confronto incompatibili o tentando di selezionare i dati di diverse regole di confronto in una colonna combinata.

    La clausola COLLATE consente di specificare le regole di confronto utilizzate nella query.

    Ad esempio, la seguente clausola WHERE darà sempre l’errore che hai postato:

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

    La soluzione è specificare una collazione condivisa per le due colonne all’interno della query. Ecco un esempio che utilizza la clausola COLLATE :

     SELECT * FROM table ORDER BY key COLLATE latin1_general_ci; 

    Un’altra opzione è utilizzare l’operatore BINARY :

    BINARY str è la scorciatoia per CAST (str AS BINARY).

    La tua soluzione potrebbe essere simile a questa:

     SELECT * FROM table WHERE BINARY a = BINARY b; 

    o,

     SELECT * FROM table ORDER BY BINARY a; 

    TL; DR

    Modifica le regole di confronto di una (o entrambe) delle stringhe in modo che corrispondano, oppure aggiungi una clausola COLLATE alla tua espressione.


    1. Che cosa è questa roba da “raccolta” comunque?

      Come documentato in Set di caratteri e Collations in generale :

      Un set di caratteri è un insieme di simboli e codifiche. Un confronto è un insieme di regole per confrontare i caratteri in un set di caratteri. Rendiamo chiara la distinzione con un esempio di un set di caratteri immaginari.

      Supponiamo di avere un alfabeto con quattro lettere: ” A “, ” B “, ” a “, ” b “. Diamo a ogni lettera un numero: ” A ” = 0, ” B ” = 1, ” a ” = 2, ” b ” = 3. La lettera ” A ” è un simbolo, il numero 0 è la codifica per ” A ” e la combinazione di tutte e quattro le lettere e le loro codifiche è un set di caratteri .

      Supponiamo di voler confrontare due valori di stringa, ” A ” e ” B “. Il modo più semplice per farlo è quello di guardare le codifiche: 0 per ” A ” e 1 per ” B “. Poiché 0 è minore di 1, diciamo che ” A ” è minore di ” B “. Quello che abbiamo appena fatto è applicare una collation al nostro set di caratteri. Le regole di confronto sono un insieme di regole (solo una regola in questo caso): “confronta le codifiche”. Chiamiamo questo più semplice di tutte le possibili regole di confronto binario .

      Ma cosa succede se vogliamo dire che le lettere minuscole e maiuscole sono equivalenti? Quindi avremmo almeno due regole: (1) trattare le lettere minuscole ” a ” e ” b ” come equivalenti a ” A ” e ” B “; (2) quindi confrontare le codifiche. Chiamiamo questo metodo di confronto senza distinzione tra maiuscole e minuscole . È un po ‘più complesso di un confronto binario.

      Nella vita reale, molti set di caratteri hanno molti personaggi: non solo ” A ” e ” B ” ma interi alfabeti, a volte più alfabeti o sistemi di scrittura orientali con migliaia di caratteri, insieme a molti simboli speciali e segni di punteggiatura. Anche nella vita reale, la maggior parte delle regole di confronto ha molte regole, non solo per distinguere tra lettere maiuscole, ma anche per distinguere gli accenti (un “accento” è un segno collegato a un carattere come in tedesco ” Ö “), e per più mappature dei caratteri (come la regola che ” Ö ” = ” OE ” in una delle due regole di confronto tedesche).

      Ulteriori esempi sono forniti sotto Esempi di effetto di confronto .

    2. Ok, ma come fa MySQL a decidere quali regole di confronto usare per una determinata espressione?

      Come documentato in Collation of Expressions :

      Nella grande maggioranza delle affermazioni, è ovvio quale collation MySQL usa per risolvere un’operazione di confronto. Ad esempio, nei seguenti casi, dovrebbe essere chiaro che le regole di confronto sono le regole di confronto della colonna charset_name :

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

      Tuttavia, con più operandi, può esserci ambiguità. Per esempio:

       SELECT x FROM T WHERE x = 'Y'; 

      Il confronto dovrebbe utilizzare le regole di confronto della colonna x , o della stringa letterale 'Y' ? Sia x che 'Y' hanno regole di confronto, quindi quale fascicolazione ha la precedenza?

      SQL standard risolve tali domande usando quelle che venivano definite regole di “coercibilità”.

        [ deletia ] 

      MySQL utilizza i valori di coercibilità con le seguenti regole per risolvere le ambiguità:

      • Utilizza la fascicolazione con il valore di coercibilità più basso.

      • Se entrambe le parti hanno la stessa coercizione, allora:

        • Se entrambi i lati sono Unicode, o entrambi i lati non sono Unicode, si tratta di un errore.

        • Se uno dei lati ha un set di caratteri Unicode e un altro lato ha un set di caratteri non Unicode, il lato con il set di caratteri Unicode vince e la conversione automatica dei set di caratteri viene applicata al lato non Unicode. Ad esempio, la seguente dichiarazione non restituisce un errore:

           SELECT CONCAT(utf8_column, latin1_column) FROM t1; 

          Restituisce un risultato che ha un set di caratteri di utf8 e le stesse regole di confronto di utf8_column . I valori di latin1_column vengono automaticamente convertiti in utf8 prima della concatenazione.

        • Per un’operazione con operandi dello stesso set di caratteri ma che mischia una collazione _bin e una collazione _ci o _cs , viene utilizzata la _bin confronto _bin . Questo è simile al modo in cui le operazioni che combinano stringhe non binarie e binarie valutano gli operandi come stringhe binarie, tranne che per le regole di confronto piuttosto che per i tipi di dati.

    3. Quindi cos’è un “miscuglio illegale di regole di confronto”?

      Un “miscuglio illegale di regole di confronto” si verifica quando un’espressione confronta due stringhe di diverse regole di confronto ma di uguale coercibilità e le regole di coercibilità non possono aiutare a risolvere il conflitto. È la situazione descritta sotto il terzo punto elenco nella citazione sopra.

      Il particolare errore riportato nella domanda, Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '=' , ci dice che c’era un confronto di uguaglianza tra due stringhe non Unicode di uguale coercibilità. Ci dice inoltre che le regole di confronto non sono state fornite esplicitamente nell’istruzione, ma piuttosto sono state implicite dalle fonti delle stringhe (come i metadati delle colonne).

    4. Va tutto bene, ma come si risolvono tali errori?

      Come suggeriscono gli estratti del manuale sopra citati, questo problema può essere risolto in diversi modi, due dei quali sono ragionevoli e raccomandabili:

      • Modificare le regole di confronto di una (o entrambe) delle stringhe in modo che corrispondano e non vi sia più alcuna ambiguità.

        Il modo in cui ciò può essere fatto dipende da dove è arrivata la stringa: le espressioni letterali accettano le regole di confronto specificate nella variabile di sistema collation_connection ; i valori delle tabelle accettano le regole di confronto specificate nei relativi metadati della colonna.

      • Forza una stringa per non essere coercibile.

        Ho omesso la seguente citazione da quanto sopra:

        MySQL assegna i valori di coercibilità come segue:

        • Una clausola COLLATE esplicita ha una coercibilità di 0. (Non coercibile affatto).

        • La concatenazione di due stringhe con diverse regole di confronto ha una coercibilità di 1.

        • La collazione di una colonna o di un parametro di routine memorizzato o di una variabile locale ha una coercibilità di 2.

        • Una “costante di sistema” (la stringa restituita da funzioni come USER() o VERSION() ) ha una coercibilità di 3.

        • La collazione di un letterale ha una coercibilità di 4.

        • NULL o un’espressione derivata da NULL ha una coercibilità di 5.

        Pertanto, l’aggiunta semplicemente di una clausola COLLATE a una delle stringhe utilizzate nel confronto imporrà l’utilizzo di tale confronto.

      Mentre gli altri sarebbero terribilmente male se fossero schierati solo per risolvere questo errore:

      • Forzare uno (o entrambi) le stringhe per avere un altro valore di coercibilità in modo che si abbia la precedenza.

        L’uso di CONCAT() o CONCAT_WS() risulterebbe in una stringa con una coercibilità di 1; e (se in una stored procedure) l’uso di parametri / variabili locali risulterebbe in stringhe con una coercibilità di 2.

      • Cambia le codifiche di uno (o entrambi) delle stringhe in modo che uno sia Unicode e l’altro no.

        Questo potrebbe essere fatto tramite transcodifica con CONVERT( expr USING transcoding_name ) ; oppure modificando il set di caratteri sottostante dei dati (ad es. modificando la colonna, cambiando character_set_connection per valori letterali o inviandoli dal client in una codifica diversa e cambiando character_set_client / aggiungendo un introduttore di set di caratteri). Si noti che la modifica della codifica porterà ad altri problemi se alcuni caratteri desiderati non possono essere codificati nel nuovo set di caratteri.

      • Modificare le codifiche di una (o entrambe) delle stringhe in modo che siano entrambe uguali e modificare una stringa per utilizzare la collazione _bin rilevante.

        I metodi per cambiare codifiche e regole di confronto sono stati dettagliati in precedenza. Questo approccio sarebbe di scarsa utilità se si ha effettivamente bisogno di applicare regole di confronto più avanzate rispetto a quelle offerte dalla _bin collation.

    Aggiungendo il mio 2c alla discussione per futuri googler.

    Stavo indagando su un problema simile in cui ho ricevuto il seguente errore durante l’utilizzo di funzioni personalizzate che hanno ricevuto un parametro varchar:

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

    Utilizzando la seguente query:

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

    Sono stato in grado di dire che il DB stava usando utf8_general_ci , mentre le tabelle erano definite usando utf8_unicode_ci :

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

    Si noti che le viste hanno regole di confronto NULL . Sembra che viste e funzioni abbiano definizioni di collazione anche se questa query mostra null per una vista. Le regole di confronto utilizzate sono le regole di confronto DB definite durante la creazione della vista / funzione.

    La triste soluzione era quella di modificare le regole di confronto e ricreare le viste / funzioni per costringerle a utilizzare le regole di confronto correnti.

    • Modifica delle regole di confronto di db:

       ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci; 

    Spero che questo possa aiutare qualcuno.

    A volte può essere pericoloso convertire i set di caratteri, specialmente nei database con enormi quantità di dati. Penso che l’opzione migliore sia usare l’operatore “binario”:

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

    Puoi provare questo script , che converte tutti i tuoi database e tabelle in utf8.

    Ho avuto un problema simile, stavo cercando di usare la procedura FIND_IN_SET con una variabile stringa.

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

    e stava ricevendo l’errore

    Codice errore: 1267. Miscela non valida di regole di confronto (utf8_unicode_ci, IMPLICIT) e (utf8_general_ci, IMPLICIT) per l’operazione ‘find_in_set’

    Risposta breve:

    Non è necessario modificare le variabili collation_YYYY, basta aggiungere le regole di confronto corrette accanto alla dichiarazione della variabile , ad es

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

    Risposta lunga:

    Per prima cosa ho controllato le variabili di confronto:

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

    Poi ho controllato le regole di confronto delle tabelle:

     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; 

    Ciò significa che la mia variabile è stata configurata con le regole di confronto predefinite di utf8_general_ci mentre la mia tabella è stata configurata come utf8_unicode_ci .

    Aggiungendo il comando COLLATE accanto alla dichiarazione della variabile, la variabile di confronto ha confrontato le regole di confronto configurate per la tabella.

    A MySQL davvero non piace mescolare le regole di confronto a meno che non possa costringerle allo stesso (che chiaramente non è fattibile nel tuo caso). Non puoi semplicemente forzare le stesse regole di confronto da utilizzare tramite una clausola COLLATE ? (o il più semplice collegamento BINARY se applicabile …).

    Soluzione se i letterali sono coinvolti.

    Sto usando Pentaho Data Integration e non riesco a specificare la syntax sql. L’utilizzo di una ricerca DB molto semplice ha generato l’errore “Miscelazione illegale di regole di confronto (cp850_general_ci, COERCIBLE) e (latin1_swedish_ci, COERCIBLE) per l’operazione ‘='”

    Il codice generato era “SELECT DATA_DATE AS latest_DATA_DATE FROM hr_cc_normalised_data_date_v WHERE PSEUDO_KEY =?”

    Per dirla in breve, la ricerca è stata una visione e quando l’ho pubblicata

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

    che spiega da dove proviene “cp850_general_ci”.

    La vista è stata semplicemente creata con ‘SELECT’ X ‘, ……’ Secondo i letterali manuali come questo dovrebbe ereditare il set di caratteri e le regole di confronto dalle impostazioni del server definite correttamente come ‘latin1’ e ‘latin1_general_cs’ come questo chiaramente non è successo l’ho forzato nella creazione della vista

     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; 

    ora mostra latin1_general_cs per entrambe le colonne e l’errore è andato via. 🙂

    Se le colonne con cui hai problemi sono “hash”, allora considera quanto segue …

    Se “hash” è una stringa binaria, dovresti usare davvero il tipo di dati BINARY(...) .

    Se “hash” è una stringa esadecimale, non hai bisogno di utf8, e dovresti evitarlo a causa di controlli sui caratteri, ecc. Ad esempio, l’ MD5(...) di MySQL MD5(...) fornisce una stringa esadecimale a 32 byte a lunghezza fissa. SHA1(...) fornisce una stringa esadecimale a 40 byte. Questo potrebbe essere memorizzato in CHAR(32) CHARACTER SET ascii (o 40 per sha1).

    O, meglio ancora, memorizzare UNHEX(MD5(...)) in BINARY(16) . Questo taglia la metà della dimensione della colonna. (Tuttavia, lo rende piuttosto non stampabile). SELECT HEX(hash) ... se vuoi che sia leggibile.

    Il confronto tra due colonne BINARY non ha problemi di collation.

    Una ansible soluzione è convertire l’intero database in UTF8 (vedere anche questa domanda ).

    Un’altra fonte del problema con le regole di confronto è la tabella mysql.proc . Controlla le regole di confronto delle tue procedure e funzioni di archiviazione:

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

    Prestare attenzione anche alle colonne mysql.proc.collation_connection e mysql.proc.character_set_client .

    Ho usato ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci; , ma non ha funzionato.

    In questa query:

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

    Questo lavoro per me:

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

    Sì, solo un concat .

    Questo codice deve essere inserito all’interno di Esegui query / query SQL sul 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; 

    Sostituire table_name e column_name con il nome appropriato.