[SOLUCIONADO] Error de Collation en consulta SQL

[waspeis]

Buenas compañeros

Tengo en una tabla (actores) almacenados nombre y apellidos de actores. En la aplicación existe la opción de buscar un actor escribiendo el nombre, el apellido o parte de alguno de ellos

La consulta es la siguiente:

Código MySQL:

SELECT id_ac, nombre, apellidos FROM actores WHERE nombre LIKE '%" . $nom . "%' OR apellidos LIKE '%" . $nom . "%'
 

Cuando se escribe un nombre que contiene una vocal acentuada sale el siguiente error:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_unicode_ci,COERCIBLE) for operation 'like'

He observado que la base de datos, las tablas y los campos de las tablas tienen Collation. He cambiado el Collation de los campos de la tabla actores que inicialmente era latin1_swedish_ci por el mismo Collation que el de la base de datos (utf8_unicode_ci). De esta manera no me da error pero tampoco me da ningún resultado

Por ejemplo:

Hay una actriz en la base de datos que se llama Mélanie Thierry. Si escribo Mélanie en el buscador, la consulta no me da ningún resultado. Pero si escribo Melanie, si me da como resultado esta actriz.

Quisiera que me diera resultado cuando escribo en el buscador Mélanie. ¿Sabéis si se puede?

Gracias

[ambichol]

y las tablas de la BD como estan estructuradas...??? podrias poner unm ejemplo...???

[jurena]

varias cosas:
por lo que se refiere a la base de datos, si la collation es utf8_unicode_ci, el charset o codificación debe ser utf8. Pudiera ocurrir que no lo sea y no tendría mucho sentido la collation.
Por otro lado, el archivo html o php donde que genera el formulario de búsqueda, también debe tener la codificación o charset utf8. Eso debería bastar, pero ve con cuidado y no cambies sin más el charset o codificación de la base. Mira a ver qué codificación o charset tiene la base y dínoslo. Para lo demás deberías pedir ayuda en el foro PHP, si es que es es tu lenguaje de programación.

[waspeis]

ambichol

La base de datos contiene 3 tablas (actores, peliculas, pelisactores) de tipo MyISAM y cotejamiento latin1_swedish_ci. La base de datos es de tipo MyISAM y cotejamiento utf8_unicode_ci. La tabla actores tiene los campos id_ac (int), nombre (varchar) y apellidos(varchar) y el cotejamiento de los campos nombre y apellidos es latin1_swedish_ci.

Vi que me daba el error "Illegal mix of collations..." y entonces cambié el cotejamiento de los campos nombre y apellidos de la tabla actores y les puse el mismo cotejamiento que el de la base de datos (utf8_unicode_ci) para evitar el error. Sin embargo no me da ningún resultado cuando hago una búsqueda con una palabra que contiene una vocal acentuada.


jurena

La collation de la BD es utf8_unicode_ci. A qué te refieres con que el charset debe ser utf8?. Tengo que modificar los collation de los campos de las tablas y los collation de las tablas para que sean todos utf8?

El archivo php donde se hace la búsqueda tenía codificación ANSI. Lo he puesto en UTF-8 y he dejado la base de datos tal como estaba al principio, todos los cotejamientos los he dejado en latin1_swedish_ci menos el cotejamiento de la base de datos que estaba en utf8_unicode_ci.

He probado y ahora si escribo en el buscador la palabra Mélanie me sale como resultado la actriz Mélanie Thierry. No obstante si escribo la palabra Melanie me sale el mismo resultado.

Mejor que antes está, al menos la búsqueda me da resultado si escribo la palabra con tilde o sin ella. Pero se podría poner para que de resultado sólo si se escribe la palabra acentuada?

Ya que estamos... qué tipo de codificación es conveniente elegir ANSI o UTF-8?. Ciertamente no me he preocupado de este tema hasta que me ha surgido este pequeño error en la aplicación.

Gracias!

[jurena]

A ver, si el charset o codificación de la base era utf8, las tablas lo tendrían y la collation o cotejamiento adecuado sería cualquiera de los de utf8, entre ellos utf8_unicode_ci, pero no sé cuál es. Para eso tal vez te valga esta consulta, que te traerá el charset y la collation de la base:
SELECT default_character_set_name,default_collation_name
FROM information_schema.schemata WHERE schema_name='tu_base_de_datos';

El archivo php donde se hace la búsqueda tenía codificación ANSI. Lo he puesto en UTF-8 y he dejado la base de datos tal como estaba al principio, todos los cotejamientos los he dejado en latin1_swedish_ci menos el cotejamiento de la base de datos que estaba en utf8_unicode_ci.

Si tu charset es utf8, bien, pero, si no, tendrás que poner en el archivo el correspondiente a tu la codificación de tu base.

He probado y ahora si escribo en el buscador la palabra Mélanie me sale como resultado la actriz Mélanie Thierry. No obstante si escribo la palabra Melanie me sale el mismo resultado.
Eso es lógico, pues la collation utf8_unicode_ci tiene la terminación ci que significa case insensitive (no diferenciará en tus filtrados mayúsculas y minúsculas ni tampoco vocales con acentos, tildes, etc.)

Mejor que antes está, al menos la búsqueda me da resultado si escribo la palabra con tilde o sin ella. Pero se podría poner para que de resultado sólo si se escribe la palabra acentuada?

No, nunca está bien, si no está del todo bien. Más tarde tendrás otros problemas. Arreglemos los problemas desde la base y para eso tienes que saber el charset y collation por defecto de tu base.

Código MySQL:

Ya que estamos... qué tipo de codificación es conveniente elegir ANSI o UTF-8?. Ciertamente no me he preocupado de este tema hasta que me ha surgido este pequeño error en la aplicación.
 

Hoy en día el charset de referencia es UTF-8, pero si tienes una aplicación que funciona con ANSI y cubre tus necesidades, para qué cambiar nada. ANSI te cubrirá las lenguas occidentales, pero UTF-8 es el charset internacional.

[waspeis]

Este es el resultado de la consulta:

default_character_set_name ---- utf8
default_collation_name ---- utf8_unicode_ci

[waspeis]

He realizado otra prueba.

He modificado el cotejamiento de los campos nombre y apellidos de la tabla actores y los he cambiado a utf8_bin, que sería el equivalente al case sensitive. Así ya puedo buscar Mélanie y me dará como resultado Mélanie Thierry y si intento buscar Melanie no me dará ningún resultado.

Pero esto hace que también distinga las mayúsculas y minúsculas. Si escribo Mélanie sí me dará resultado pero si escribo mélanie no me dará ningún resultado. Quisiera solamente que me tenga en cuenta cuando se escribe una vocal acentuada pero que no me distinga las mayúsculas y minúsculas...

[gnzsoloyo]

Eso ya es un poco complicado, pero una solución sencilla es forzar a que ignore esa situación, aplicando UPPER() o LOWER() sobre ambos valores, de modo de unificar todo en mayúsculas o minúsculas.
Es lo que suele hacerse en casos así en diferentes DBMS.

[waspeis]

Bueno pues al final es así como ha quedado mi consulta:

Código SQL:

SELECT id_ac, nombre, apellidos FROM actores WHERE LOWER(nombre) LIKE '%" . addslashes(mb_strtolower($nom, "UTF-8")) . "%' OR LOWER(apellidos) LIKE '%" . addslashes(mb_strtolower($nom, "UTF-8")) . "%'
 

gnzsoloyo

Con la función LOWER() paso a minúsculas todas las letras de la palabra nombre o apellidos, y lo mismo hago con la función mb_strtolower(). Tuve que utilizar mb_strtolower() en lugar de strtolower() porque esta última no me pasaba a minúsculas las vocales acentuadas. Y la función addslashes() es para los apellidos que tienen comilla simple.

Gracias!


jurena

Esto es lo que me devolvió la consulta
default_character_set_name ---- utf8
default_collation_name ---- utf8_unicode_ci

Al final he dejado el collation de los campos nombre y apellidos de la tabla actores como utf8_bin. Las tablas tienen como collation latin1_swedish_ci y la base de datos tiene como collation utf8_unicode_ci. No se si está del todo bien o si tendrían que coincidir los collation de las tablas con el de la base de datos, pero tal como está ahora me funciona perfectamente. Me distingue los acentos y no me distingue las mayúsculas y minúsculas.

Gracias!

[jurena]

Si lo dejas así, no estará del todo bien para el futuro. Cambia las tablas a collation utf8_unicode_ci, pero a los campos nombre y apellidos déjalos específicamente con collation utf8_bin. Usa siempre las funciones con el prefijo mb, de multibyte, como mb_strtolower(). Esas son las que te funcionán con el charset utf8. Fíjate cuando vayas a aplicar alguna función si es de uso con utf8

Este es mi consejo. Suerte