for - Solución de problemas de error de "combinación no válida de intercalaciones" en mysql

php mysql collation (13)

Estoy obteniendo el siguiente error al intentar hacer una selección a través de un procedimiento almacenado en MySQL.

Mezcla ilegal de intercalaciones (latin1_general_cs, IMPLICIT) y (latin1_general_ci, IMPLICIT) para la operación ''=''

¿Alguna idea sobre qué podría estar yendo mal aquí?

La intercalación de la tabla es latin1_general_ci y la de la columna en la cláusula latin1_general_cs es latin1_general_cs .

Agregar mi 2c a la discusión para futuros googlers.

Estaba investigando un problema similar en el que recibí el siguiente error al usar funciones personalizadas que recibían un parámetro varchar:

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

Usando la siguiente consulta:

Pude ver que el DB estaba usando utf8_general_ci , mientras que las tablas se definieron usando utf8_unicode_ci :

Tenga en cuenta que las vistas tienen una intercalación NULL . Parece que las vistas y las funciones tienen definiciones de intercalación aunque esta consulta muestra nulo para una vista. La intercalación utilizada es la intercalación de DB que se definió cuando se creó la vista / función.

La triste solución fue cambiar la intercalación de db y recrear las vistas / funciones para obligarlas a usar la intercalación actual.

Cambiar la intercalación de la base de datos:
ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci;

Espero que esto ayude a alguien.

TL; DR

Cambie la intercalación de una (o ambas) cadenas para que coincidan, o bien agregue una cláusula COLLATE a su expresión.

¿Qué es esta cosa de "colación" de todos modos?
Como se documenta en Conjuntos de caracteres y colaciones en general :
Un conjunto de caracteres es un conjunto de símbolos y codificaciones. Una intercalación es un conjunto de reglas para comparar personajes en un juego de caracteres. Hagamos la distinción clara con un ejemplo de un conjunto de caracteres imaginarios.
Supongamos que tenemos un alfabeto con cuatro letras: " A ", " B ", " a ", " b ". Le damos a cada letra un número: " A " = 0, " B " = 1, " a " = 2, " b " = 3. La letra " A " es un símbolo, el número 0 es la codificación de " A " , y la combinación de las cuatro letras y sus codificaciones es un juego de caracteres .
Supongamos que queremos comparar dos valores de cadena, " A " y " B ". La forma más sencilla de hacerlo es mirar las codificaciones: 0 para " A " y 1 para " B ". Como 0 es menor que 1, decimos que " A " es menor que " B ". Lo que acabamos de hacer es aplicar una intercalación a nuestro conjunto de caracteres. La intercalación es un conjunto de reglas (una sola regla en este caso): "comparar las codificaciones". Llamamos colación binaria a esta colación simple, la más simple de todas.
Pero, ¿y si queremos decir que las letras minúsculas y mayúsculas son equivalentes? Entonces tendríamos al menos dos reglas: (1) tratar las letras minúsculas " a " y " b " como equivalentes a " A " y " B "; (2) luego compara las codificaciones. Llamamos a esto una intercalación insensible a mayúsculas y minúsculas . Es un poco más complejo que una intercalación binaria.
En la vida real, la mayoría de los juegos de caracteres tienen muchos caracteres: no solo " A " y " B " sino alfabetos enteros, a veces alfabetos múltiples o sistemas de escritura orientales con miles de caracteres, junto con muchos símbolos especiales y signos de puntuación. También en la vida real, la mayoría de las intercalaciones tienen muchas reglas, no solo para distinguir entre mayúsculas y minúsculas, sino también para distinguir los acentos (un "acento" es una marca unida a un personaje como en alemán " Ö "), y para múltiples mapeos de caracteres (como la regla de que " Ö " = " OE " en una de las dos colaciones alemanas).
Se dan ejemplos adicionales en Ejemplos del efecto de colación .
De acuerdo, pero ¿cómo decide MySQL qué colación usar para una expresión determinada?
Como se documenta en Collation of Expressions :
En la gran mayoría de las declaraciones, es obvio qué colación utiliza MySQL para resolver una operación de comparación. Por ejemplo, en los siguientes casos, debe quedar claro que la intercalación es la intercalación de la columna charset_name :
SELECT x FROM T ORDER BY x; SELECT x FROM T WHERE x = x; SELECT DISTINCT x FROM T;
Sin embargo, con operandos múltiples, puede haber ambigüedad. Por ejemplo:
SELECT x FROM T WHERE x = ''Y'';
¿Debería la comparación usar la intercalación de la columna x , o de la cadena literal ''Y'' ? Tanto x como ''Y'' tienen intercalaciones, de modo que ¿qué colación tiene prioridad?
El estándar SQL resuelve tales preguntas usando lo que solía llamarse reglas de "coercibilidad".
[ deletia ]
MySQL usa valores de coercibilidad con las siguientes reglas para resolver ambigüedades:
- Use la intercalación con el valor de coercibilidad más bajo.
- Si ambos lados tienen la misma coercibilidad, entonces:
  - Si ambos lados son Unicode, o ambos lados no son Unicode, es un error.
  - Si uno de los lados tiene un juego de caracteres Unicode y otro lado tiene un juego de caracteres no Unicode, el lado con el juego de caracteres Unicode gana, y la conversión automática del juego de caracteres se aplica al lado que no es Unicode. Por ejemplo, la siguiente declaración no devuelve un error:
    SELECT CONCAT(utf8_column, latin1_column) FROM t1;
    Devuelve un resultado que tiene un conjunto de caracteres de utf8 y la misma intercalación que utf8_column . Los valores de latin1_column se convierten automáticamente a utf8 antes de la concatenación.
  - Para una operación con operandos del mismo conjunto de caracteres pero que mezcla una intercalación _bin y una intercalación _ci o _cs , se _bin intercalación _bin . Esto es similar a cómo las operaciones que mezclan cadenas no binarias y binarias evalúan los operandos como cadenas binarias, excepto que se trata de intercalaciones en lugar de tipos de datos.
Entonces, ¿qué es una "mezcla ilegal de intercalaciones"?
Se produce una "combinación ilegal de intercalaciones" cuando una expresión compara dos cadenas de intercalaciones diferentes pero de igual coercibilidad y las reglas de coercibilidad no pueden ayudar a resolver el conflicto. Es la situación descrita en el tercer punto en la cita anterior.
El error particular dado en la pregunta, Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation ''='' , nos dice que hubo una comparación de igualdad entre dos cadenas no Unicode de igual coercibilidad. Además, nos dice que las intercalaciones no se dieron explícitamente en el enunciado, sino que fueron implícitas a partir de las fuentes de las cadenas (como los metadatos de columna).
Eso está muy bien, pero ¿cómo se resuelven esos errores?
Como sugieren los extractos manuales citados anteriormente, este problema se puede resolver de varias maneras, dos de las cuales son sensatas y recomendables:
- Cambie la intercalación de una (o ambas) cadenas para que coincidan y ya no haya ninguna ambigüedad.
  Cómo se puede hacer esto depende de dónde ha venido la cadena: las expresiones literales toman la intercalación especificada en la variable del sistema collation_connection ; los valores de las tablas toman la intercalación especificada en sus metadatos de columna.
- Fuerza una cuerda para que no sea coercible.
  Omití la siguiente cita de lo anterior:
  MySQL asigna valores de coercibilidad de la siguiente manera:
  - Una cláusula COLLATE explícita tiene una coercibilidad de 0. (No coercible en absoluto).
  - La concatenación de dos cadenas con diferentes intercalaciones tiene una coercibilidad de 1.
  - La intercalación de una columna o un parámetro de rutina almacenado o variable local tiene una coercibilidad de 2.
  - Una "constante del sistema" (la cadena devuelta por funciones como USER() o VERSION() ) tiene una coercibilidad de 3.
  - La intercalación de un literal tiene una coercibilidad de 4.
  - NULL o una expresión que se deriva de NULL tiene una coercibilidad de 5.
  Por lo tanto, simplemente agregar una cláusula COLLATE a una de las cadenas utilizadas en la comparación forzará el uso de esa intercalación.
Mientras que los otros serían terriblemente mala práctica si se desplegaran simplemente para resolver este error:
- Fuerza uno (o ambos) de las cadenas para tener algún otro valor de coercibilidad para que uno tenga prioridad.
  El uso de CONCAT() o CONCAT_WS() daría como resultado una cadena con una coercibilidad de 1; y (si se trata de una rutina almacenada) el uso de parámetros / variables locales daría como resultado cadenas con una coercibilidad de 2.
- Cambie las codificaciones de una (o ambas) cadenas para que una sea Unicode y la otra no.
  Esto podría hacerse a través de la transcodificación con CONVERT( expr USING transcoding_name ) ; o cambiando el juego de caracteres subyacente de los datos (por ejemplo, modificando la columna, cambiando character_set_connection para valores literales, o enviándolos desde el cliente en una codificación diferente y cambiando character_set_client / agregando un introductor de conjunto de caracteres). Tenga en cuenta que cambiar la codificación dará lugar a otros problemas si algunos caracteres deseados no se pueden codificar en el nuevo conjunto de caracteres.
- Cambie las codificaciones de una (o ambas) de las cadenas para que sean las mismas y cambie una cadena para usar la intercalación _bin correspondiente.
  Los métodos para cambiar codificaciones y colaciones se han detallado anteriormente. Este enfoque sería de poca utilidad si uno realmente necesita aplicar reglas de colación más avanzadas que las que ofrece la colación _bin .

A veces puede ser peligroso convertir conjuntos de caracteres, especialmente en bases de datos con grandes cantidades de datos. Creo que la mejor opción es usar el operador "binario":

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

En general, esto se produce al comparar dos cadenas de intercalaciones incompatibles o al intentar seleccionar datos de diferentes intercalaciones en una columna combinada.

La cláusula COLLATE permite especificar la intercalación utilizada en la consulta.

Por ejemplo, la siguiente cláusula WHERE siempre dará el error que publicó:

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

Su solución es especificar una intercalación compartida para las dos columnas dentro de la consulta. Aquí hay un ejemplo que usa la cláusula COLLATE :

SELECT * FROM table ORDER BY key COLLATE latin1_general_ci;

Otra opción es usar el operador BINARY :

BINARY str es la abreviatura de CAST (str AS BINARY).

Su solución podría verse más o menos así:

SELECT * FROM table WHERE BINARY a = BINARY b;

SELECT * FROM table ORDER BY BINARY a;

Este código debe colocarse en Ejecutar consulta SQL / consultas en la base de datos

VENTANA DE CONSULTA DE SQL

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

Reemplace table_name y column_name con el nombre apropiado.

MySQL realmente no le gusta mezclar colaciones a menos que pueda forzarlas a la misma (lo que claramente no es factible en su caso). ¿No puedes obligar a utilizar la misma intercalación mediante una cláusula COLLATE ? (o el atajo BINARY más simple si corresponde ...).

Otra fuente del problema con las intercalaciones es la tabla mysql.proc . Verifique las intercalaciones de sus procedimientos y funciones de almacenamiento:

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

También preste atención a las columnas mysql.proc.collation_connection y mysql.proc.character_set_client .

Puede probar esta secuencia de comandos , que convierte todas sus bases de datos y tablas a utf8.

Si las columnas con las que está teniendo problemas son "hashes", entonces considere lo siguiente ...

Si el "hash" es una cadena binaria, realmente debería usar BINARY(...) datatype.

Si el "hash" es una cadena hexadecimal, no necesita utf8, y debe evitarlo debido a las verificaciones de caracteres, etc. Por ejemplo, el MD5(...) de MySQL MD5(...) produce una cadena hex de longitud fija de 32 bytes. SHA1(...) da una cadena hexadecimal de 40 bytes. Esto podría almacenarse en CHAR(32) CHARACTER SET ascii (o 40 para sha1).

O mejor aún, almacene UNHEX(MD5(...)) en BINARY(16) . Esto corta en la mitad del tamaño de la columna. (Sin embargo, lo hace bastante no imprimible.) SELECT HEX(hash) ... si lo quiere legible.

La comparación de dos columnas BINARY no tiene problemas de intercalación.

Solución si hay literales involucrados.

Estoy usando Pentaho Data Integration y no consigo especificar la sintaxis sql. El uso de una búsqueda DB muy simple dio el error "Mezcla ilegal de intercalaciones (cp850_general_ci, COERCIBLE) y (latin1_swedish_ci, COERCIBLE) para la operación ''=''"

El código generado fue "SELECT DATA_DATE AS latest_DATA_DATE from hr_cc_normalised_data_date_v WHERE PSEUDO_KEY =?"

Cortar la historia corta la búsqueda fue a la vista y cuando emití

que explica de dónde viene el ''cp850_general_ci''.

La vista se creó simplemente con ''SELECT'' X '', ......'' De acuerdo con los literales manuales como este, deberían heredar su conjunto de caracteres y la intercalación de la configuración del servidor que se definieron correctamente como ''latin1'' y ''latin1_general_cs'' como este claramente no sucedió lo forcé en la creación de la 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;

ahora muestra latin1_general_cs para ambas columnas y el error se ha ido. :)

Tuve un problema similar, estaba tratando de usar el procedimiento FIND_IN_SET con una variable de cadena.

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

y estaba recibiendo el error

Código de error: 1267. Mezcla ilegal de intercalaciones (utf8_unicode_ci, IMPLICIT) y (utf8_general_ci, IMPLICIT) para la operación ''find_in_set''

Respuesta corta:

No es necesario cambiar ninguna variable de colación_AAAA, solo agregue la intercalación correcta junto a su declaración de variable , es decir,

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

Respuesta larga:

Primero revisé las variables de colación:

Luego revisé la clasificación de la tabla:

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;

Esto significa que mi variable se configuró con la intercalación predeterminada de utf8_general_ci, mientras que mi tabla se configuró como utf8_unicode_ci .

Al agregar el comando COLLATE junto a la declaración de variable, la intercalación de variables coincidía con la intercalación configurada para la tabla.

Una posible solución es convertir toda la base de datos a UTF8 (vea también esta question ).

Utilicé ALTER DATABASE mydb DEFAULT COLLATE utf8_unicode_ci; , pero no funcionó.

En esta consulta:

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

Este trabajo para mí:

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

Sí, solo un concat .