utf8_general_ci - mysql data mysqlclient mysqlexception illegal mix of collations for operation union

MySQL mezcla ilegal de colaciones

Question

Después de ver mis registros de prod, he mencionado algunos errores:

[2012-08-31 15:56:43] request.CRITICAL: Doctrine/DBAL/DBALException:
An exception occurred while executing ''SELECT t0.username ....... FROM fos_user t0 WHERE t0.username = ?''
with params {"1":"Nrv/u29e7Kasi"}:
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (utf8_general_ci,COERCIBLE) for operation ''='' 

Sin embargo, tengo el valor predeterminado de UTF-8 bajo la doctrina cfg:

doctrine:
dbal:
charset: UTF8

Parece que todas mis tablas MySQL están en latin1_swedish_ci , así que mi pregunta es:

¿Puedo cambiar manualmente la intercalación a utf8_general_ci para todas mis tablas sin ninguna complicación / precaución?

Answer 1

Es útil entender las siguientes definiciones:

• Una codificación de caracteres detalla cómo cada símbolo se representa en binario (y, por lo tanto, se almacena en la computadora). Por ejemplo, el símbolo é (U + 00E9, letra minúscula latina E con letra aguda) se encoded como 0xc3a9 en UTF-8 (que MySQL llama utf8 ) y 0xe9 en Windows-1252 (que MySQL llama latin1 ).

• Un conjunto de caracteres es el alfabeto de los símbolos que se pueden representar utilizando una codificación de caracteres determinada. Confusamente, el término también se usa para significar lo mismo que la codificación de caracteres.

• Una intercalación es una ordenación en un conjunto de caracteres, de modo que las cadenas pueden compararse. Por ejemplo: la latin1_swedish_ci de MySQL trata las variaciones más acentuadas de un personaje como equivalentes al carácter base, mientras que su latin1_general_ci ordenará antes del siguiente carácter base, pero no equivalentes (también existen otras diferencias, más significativas, como la orden de caracteres como å , ä , ö y ß ).

MySQL decidirá qué intercalación se debe aplicar a una expresión dada como se documenta en Collation of Expressions : en particular, la intercalación de una columna tiene prioridad sobre la de una cadena literal.

La cláusula WHERE de su consulta compara las siguientes cadenas:

1. un valor en fos_user.username , codificado en el conjunto de caracteres de la columna (Windows-1252) y que expresa una preferencia por su intercalación latin1_swedish_ci (con un valor de coercibilidad de 2); con

2. la cadena literal ''Nrv⧧Kasi'' , codificada en el conjunto de caracteres de la conexión (UTF-8, según lo configurado por Doctrine) y que expresa una preferencia por la intercalación de la conexión utf8_general_ci (con un valor de coercibilidad de 4).

Dado que la primera de estas cadenas tiene un valor de coercibilidad más bajo que la segunda, MySQL intenta realizar la comparación utilizando la intercalación de esa cadena: latin1_swedish_ci . Para hacerlo, MySQL intenta convertir la segunda cadena a latin1 pero como el carácter ⧧ no existe en ese conjunto de caracteres, la comparación falla.

Advertencia

Uno debe hacer una pausa por un momento para considerar cómo se codifica actualmente la columna: ¡está intentando filtrar los registros donde fos_user.username es igual a una cadena que contiene un carácter que no puede existir en esa columna !

Si crees que la columna contiene tales caracteres, entonces probablemente escribiste en la columna mientras la codificación de los caracteres de conexión estaba configurada en algo (por ejemplo, latin1 ) que hizo que MySQL interpretara la secuencia de bytes recibida como caracteres, todos en Windows-1252 conjunto de caracteres.

Si este es el caso, antes de continuar, ¡deberías arreglar tus datos!

1. convierta dichas columnas a la codificación de caracteres que se utilizó en la inserción de datos, si es diferente a la codificación predominante:

   ALTER TABLE fos_users MODIFY username VARCHAR(123) CHARACTER SET foo;

2. elimine la información de codificación asociada con dichas columnas al convertirlas al conjunto de caracteres binary :

   ALTER TABLE fos_users MODIFY username VARCHAR(123) CHARACTER SET binary;

3. asocie con tales columnas la codificación en la que los datos se transmitieron realmente convirtiéndolos al conjunto de caracteres relevante.

   ALTER TABLE fos_users MODIFY username VARCHAR(123) CHARACTER SET bar;

Tenga en cuenta que, si se convierte de una codificación de múltiples bytes, es posible que deba aumentar el tamaño de la columna (o incluso cambiar su tipo) para adaptarse a la longitud máxima posible de la cadena convertida.

Una vez que uno está seguro de que las columnas están codificadas correctamente, se podría forzar la comparación para que se realice utilizando una clasificación Unicode mediante:

• convirtiendo explícitamente el valor fos_user.username a un conjunto de caracteres Unicode:

  WHERE CONVERT(fos_user.username USING utf8) = ?

• forzando al literal de cadena a tener un valor de coercibilidad más bajo que la columna (causará una conversión implícita del valor de la columna a UTF-8):

  WHERE fos_user.username = ? COLLATE utf8_general_ci

O uno podría, como usted dice, convertir permanentemente la (s) columna (s) a una codificación Unicode y establecer su intercalación de manera apropiada.

¿Puedo cambiar manualmente la intercalación a utf8_general_ci para todas mis tablas sin ninguna complicación / precaución?

La consideración principal es que las codificaciones Unicode ocupan más espacio que los conjuntos de caracteres de un solo byte, por lo que:

• se puede requerir más almacenamiento;

• las comparaciones pueden ser más lentas; y

• las longitudes de los prefijos de índice deben ajustarse (tenga en cuenta que el máximo está en bytes, por lo que puede representar menos caracteres que anteriormente).

Además, tenga en cuenta que, como se documenta en la sintaxis de ALTER TABLE :

Para cambiar el conjunto de caracteres predeterminado de la tabla y todas las columnas de caracteres ( CHAR , CHAR , TEXT ) a un nuevo conjunto de caracteres, use una declaración como esta:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Para una columna que tiene un tipo de datos de CHAR o uno de los tipos de TEXT , CONVERT TO CHARACTER SET cambiará el tipo de datos según sea necesario para garantizar que la nueva columna sea lo suficientemente larga como para almacenar tantos caracteres como la columna original. Por ejemplo, una columna TEXT tiene dos bytes de longitud, que almacenan la longitud en bytes de los valores en la columna, hasta un máximo de 65,535. Para una columna de TEXT latin1 , cada carácter requiere un solo byte, por lo que la columna puede almacenar hasta 65,535 caracteres. Si la columna se convierte a utf8 , cada carácter puede requerir hasta tres bytes, para una longitud máxima posible de 3 × 65,535 = 196,605 bytes. Esa longitud no se ajustará a los bytes de longitud de una columna TEXT , por lo que MySQL convertirá el tipo de datos a TEXT , que es el tipo de cadena más pequeño para el cual los bytes de longitud pueden registrar un valor de 196,605. De forma similar, una columna CHAR puede convertirse a TEXT .

Para evitar cambios en el tipo de datos del tipo que se acaba de describir, no use CONVERT TO CHARACTER SET . En su lugar, utilice MODIFY para cambiar columnas individuales.

Answer 2

Está bien. Me encontré con este problema y la mejor solución rápida y rápida es

 CONVERT(fos_user.username USING utf8)

Answer 3

Simplemente convierta el juego de caracteres de la mesa por comando como sigue,

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;