utf8_general_ci - mysql data mysqlclient mysqlexception illegal mix of collations for operation union
MySQL mezcla ilegal de colaciones (3)
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?
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 como0xc3a9
en UTF-8 (que MySQL llamautf8
) y0xe9
en Windows-1252 (que MySQL llamalatin1
).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 sulatin1_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:
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ónlatin1_swedish_ci
(con un valor de coercibilidad de 2); conla 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ónutf8_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!
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;
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;
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 columnaTEXT
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 deTEXT
latin1
, cada carácter requiere un solo byte, por lo que la columna puede almacenar hasta 65,535 caracteres. Si la columna se convierte autf8
, 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 columnaTEXT
, 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, utiliceMODIFY
para cambiar columnas individuales.
Está bien. Me encontré con este problema y la mejor solución rápida y rápida es
CONVERT(fos_user.username USING utf8)
Simplemente convierta el juego de caracteres de la mesa por comando como sigue,
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;