Cómo funciona MySQL "sin distinción de mayúsculas y minúsculas" y "insensible a los acentos" en UTF-8
case-insensitive non-ascii-characters (2)
Tengo un esquema en "utf8 - UTF-8 Unicode" como conjunto de caracteres y una recopilación de "utf8_spanish_ci".
Todas las tablas internas son InnoDB con el mismo conjunto de caracteres y compilación como se mencionó.
Aquí viene el problema:
con una consulta como
SELECT *
FROM people p
WHERE p.NAME LIKE ''%jose%'';
Obtengo 83 filas de resultados. Debería tener 84 resultados, porque lo sé.
Cambiando donde para:
WHERE p.NAME LIKE ''%JOSE%'';
Tengo exactamente las mismas 83 filas. Con combinaciones como JoSe, Jose, JOSe, etc. Se reportan las mismas 83 filas.
El problema viene cuando los acentos juegan en el juego. Si hacer
WHERE p.NAME LIKE ''%josé%'';
No obtengo resultados. 0 filas.
Pero si lo hago:
WHERE p.NAME LIKE ''%JOSÉ%'';
Solo obtengo una fila resultante, entonces 1 fila. Esta es la única fila en la que se acentúa "jose" y se capitaliza.
He intentado con josÉ, o JoSÉ o cualquier combinación que haga, siempre y cuando la letra acentuada permanezca en mayúscula o no, ya que realmente se almacena en la base de datos y aún retorna la única fila. Si de repente cambio "É" por "é" en cualquier combinación que haga con el uso de mayúsculas en JOSE, no devuelve filas.
Así que conclusiones:
- Caso insensible si no hay caracteres latinos en el juego.
- Se distinguen mayúsculas y minúsculas si aparecen caracteres latinos.
- Sensible al acento, como si busco JOSE o jose, solo obtengo 83 filas, en lugar de las 84 filas que necesito.
¿Lo que quiero?
- Para buscar "jose", "JOSE", "José", "JOSÉ", "JÒSE", "jöse", "JoSÈ", ... tienen que devolver las 84 filas que sé que existen. Lo que debo hacer para que mis búsquedas sean insensibles a mayúsculas y minúsculas.
Soluciones como COLLATION en LIKE no funcionan para mí, no sé por qué ...
¿Que puedo hacer?
¡Gracias por adelantado!
EDITAR:
Si hago algo como:
WHERE p.NAME LIKE ''%jose%'' COLLATE utf8_general_ci;
Me sale el error
COLLATION ''utf8_general_ci'' is not valid for CHARACTER SET ''latin1''
¡Y también he cambiado todas las posibles colaciones en las columnas!
Y si hago algo como:
WHERE p.NAME LIKE _utf8 ''%jose%'' COLLATE utf8_general_ci;
Se reportan las mismas 83 filas, como si no hubiera hecho nada ...
En caso de que alguien más se tope con este problema, he encontrado una manera de resolver el problema, al menos para mí.
Estoy usando PHP para insertar y recuperar registros de la base de datos. A pesar de que mi base de datos, las tablas y las columnas son utf8, así como la codificación de los archivos PHP, la verdad es que la codificación utilizada en la conexión entre PHP y MySQL se realiza mediante latin1. Me las arreglé para encontrar esto usando
$mysqli->character_set_name();
donde $mysqli es tu objeto.
Para que las búsquedas comiencen a funcionar como se espera, devolviendo registros insensibles al acento e insensibles a mayúsculas y minúsculas para los caracteres con acento o no, tengo que establecer explícitamente el conjunto de caracteres de la conexión.
Para hacer esto, solo tienes que hacer lo siguiente:
$mysqli->set_charset(''utf8'');
donde $ mysqli es tu objeto mysqli. Si tiene una clase de administración de base de datos que envuelve su funcionalidad de base de datos, esto es fácil de aplicar a una aplicación completa. Si no es así, debe establecer esto explícitamente en cualquier lugar donde abra una conexión.
¡Espero que esto ayude a alguien, ya que me estaba volviendo loco por esto!
Ya ha intentado utilizar una intercalación insensible al acento para su búsqueda y pedido.
http://dev.mysql.com/doc/refman/5.0/en/charset-collation-implementations.html
La cuestión es que su columna NAME parece estar almacenada en el conjunto de caracteres latin1 (8 bits). Es por eso que mySQL se está quejando de esta manera:
COLLATION ''utf8_general_ci'' is not valid for CHARACTER SET ''latin1''
Puede obtener los resultados que desea si lo intenta
WHERE CONVERT(p.NAME USING utf8) LIKE _utf8 ''%jose%'' COLLATE utf8_general_ci;
¡Pero ten cuidado!
Cuando utiliza cualquier tipo de función (en este ejemplo, CONVERTIR) en la columna en una declaración WHERE, anula los intentos de MySQL de optimizar su búsqueda con índices. Si este proyecto va a ser grande (es decir, si tendrá muchas filas en sus tablas), necesita almacenar sus datos en formato utf8, no latin1. (Probablemente ya sepa que el término de búsqueda LIKE ''%whatever%'' también anula la indexación de MySQL).