Distinguir acentos, mayúsculas y minúsculas en MySQL

Publicado: 02/02/2011 en base de datos, MySQL
Etiquetas:, , , , , ,

You can read the English version of this post in http://phpsblog.agustinvillalba.com/how-to-distinguish-upper-lowercase-and-utf8-characters-mysql/

En esta ocasión vamos a hablar sobre cómo generar una sencilla query de selección de datos en una base de datos MySQL, cuyo charset ha sido establecido como utf8_general_ci pero necesitamos distinguir entre datos con acentos y datos sin acentos, y yendo un poco más allá, si fuera posible, que no distinga entre palabras que empiecen con mayúsculas o minúsculas pero sí los acentos. Veamos cómo podríamos implementarlo.

Definición de nuestro problema

Expliquemos un poco mejor cuál es nuestro problema. Tenemos una base de datos MySQL en la que tenemos a su vez una tabla, por ejemplo, “usuarios”, con varios campos: id, nombre y apellido. Esta base de datos fue creada con un charset utf8_general_ci. Pero, ¿qué significa esto? Obviamente, utf8 es el charset que nuestra base de datos va a gestionar. En segundo lugar, “general” indica que no utilizaremos el juego de caracteres propio de un idioma en concreto, (podríamos tener “utf8_spanish_ci”, para el juego de carácteres propios del español), sino el utf8 de forma genérica. En último lugar, “ci” significa “Case Insensitive”, de forma que en nuestras búsquedas, el motor de MySQL no hará distinciones entre “MAYÚSCULA”, “Mayúscula” o “mayúscula”. Lo que no es tan conocido es que el ser Case Insensitive también implica que no distinga los acentos del español, de forma que tampoco hace distinciones entre “mayúscula” o “mayuscula”.

De forma que si tenemos 3 registros en nuestra tabla usuarios, en los cuales tenemos los siguientes valores para el campo nombre: “José”, “Jose” y “JOSÉ”, y ejecutamos la siguiente query:

SELECT * FROM `usuarios` WHERE `nombre` = 'jose'

Ésta nos devolverá los 3 registros de nuestra base de datos, sin distinciones.

Distinguir acentos, mayúsculas y minúsculas en MySQL

Para poder distinguir acentos, mayúsculas y minúsculas en nuestras búsquedas en una base de datos MySQL con charset utf8_general_ci necesitamos indicarle al motor MySQL que en ésta búsqueda en concreto no aplique el charset utf8_general_ci, sino que utilice otro, el conocido como utf8_bin, y para ello hemos de utilizar la palabra reservada COLLATE. Con este charset le estamos indicando a MySQL que la comparación en la búsqueda la haga a nivel binario, con lo cual ahora sí podrá distinguir entre palabras con acentos y sin acentos, mayúsculas y minúsculas dado que (obviamente) un carácter con acento es bianariamente distinto a uno sin acento así como las mayúsculas. De forma que nuestra query anterior, para que distinga acentos, sería algo así:

SELECT * FROM `usuarios` WHERE `nombre` = 'josé' COLLATE utf8_bin

Esta query nos devolvería un registro nulo, dado que no existe exactamente “josé” en nuestra base datos. Para poder obtener un valor deberíamos ejecutar la misma query pero comparando con el valor “José” o “JOSÉ”, dado que con el COLLATE utf8_bin “josé” <> “jose” <> “José” <> “JOSÉ” etc.

Pero resulta que necesitamos que distinga acentos pero que no tenga en cuenta mayúsculas o minúsculas. ¿Cómo podemos solucionarlo?

Distinguir acentos, sin distinguir mayúsculas o minúsculas en MySQL

Para que MySQL pueda distinguir los acentos pero no las mayúsculas o minúsculas, no tenemos que hacer nada especial. Simplemente lo que haremos será convertir a todos los caracteres en mayúscula del valor almacenado en el campo nombre y convertir a todos los caracteres en mayúsculas del valor del parámetro a comparar, de forma que internamente en el momento de la búsqueda no existan minúsculas y así poder comparar con seguridad. Para ello utilizaremos la palabra reservada UPPER de MySQL. De forma, que nuestra query anterior, para que no distinga mayúsculas y minúsculas pero sí acentos, sería la siguiente:

SELECT * FROM `usuarios` WHERE UPPER(`nombre`) = UPPER('josé) COLLATE utf8_bin

Esta query nos devolvería como resultado los nombres “José” y “JOSÉ”, dado que no hacemos distinciones de mayúsculas, tal y como queríamos conseguir.

You can read the English version of this post in http://phpsblog.agustinvillalba.com/how-to-distinguish-upper-lowercase-and-utf8-characters-mysql/

Anuncios
comentarios
  1. kinderpaco dice:

    Perfecta explicación y funciona (probado por mi parte).

    Muchas gracias, sigue así.

  2. Jose Luis dice:

    Hola. A proposito de tu articulo, me gustaria saber cual es la norma habitual en el registro de usuarios en cuanto a permitir distinguir mayusculas de minusculas y nombres acentuados de no acentuados. Es decir, es común dejar que un usuario se registre con nombre “José” cuando haya otro usuario ya registrado con nombre “Jose”???

    Y lo mismo para “jose” y “Jose”

    Muchas gracias. Un saludo.

  3. Leonardo Perez dice:

    Con esto puedes solucionar el problema independientemente de la instalacion de mysql.

    CAST(CONCAT_WS(‘ ‘,campo1,campo2,campo3,campoN) AS CHAR) Like ‘%$busq%’

  4. Dani dice:

    Hola, muy bueno tu articulo, pero que pasa si estas haciendo una consulta sobre una vista.
    En ese caso tu solucion falla y no encuentro ninguna forma de solucionarlo.
    Alguna idea?
    Gracias por adelantado

  5. eduardobpe dice:

    Por cierto, las consultas SQL donde he añadido la cadena de texto “COLLATE utf8_bin” como la que se explica en este post:
    SELECT * FROM `usuarios` WHERE UPPER(`nombre`) = UPPER(‘josé’) COLLATE utf8_bin
    sólo me funcionan si en mi código php especifico que me voy a conectar a la base de datos con una codificación de caracteres utf-8. Esto último lo hago escribiendo la instrucción
    mysql_query(“SET NAMES utf8”,$con); antes de realizar la consulta SQL a la BD.

    mysql_query(“SET NAMES utf8”,$con);
    mysql_query(SELECT * FROM `usuarios` WHERE UPPER(`nombre`) = UPPER(‘josé’) COLLATE utf8_bin,$con);

    $con es una variable que representa una conexión abierta con el servidor de base de datos.

    Estoy utilizando para mis pruebas XAMPP; y aunque en phpMyAdmin he especificado que el cotejamiento de la conexión al servidor, de mi base de datos y de las tablas y sus columnas sea utf8_general_ci, cuando utilizo una consulta con un WHERE que lleva tildes (por ej: WHERE nombre=’josé’), si no utilizo la instrucción mysql_query(“SET NAMES utf8”,$con); antes de mysql_query(SELECT * FROM `usuarios` WHERE UPPER(`nombre`) = UPPER(‘josé’) COLLATE utf8_bin,$con); , la consulta me mostraría resultados donde nombre=jose o nombre=josé; es decir, no tiene en cuenta las tildes a pesar de usar “COLLATE utf8_bin”.

  6. eduardobpe dice:

    El artículo tiene ya un tiempo, pero tras pasar un rato muyyyyyy largo buscando información en Internet acerca de esto, lo que comentas en este post es lo que andaba buscando. Muchas gracias.

  7. Jharrii dice:

    Woooww muchas gracias muy duro encontrar la informacion pero sirvio de mucho…

  8. Janckos dice:

    Hola!
    Yo necesito que no discrimine (distinga) entre mayuscula, minúsculas y acentos. Me he fijado en el cotejamiento de mi base de datos y desconozco porqué aparece como tal el: latin1_swedish_ci; necesito saber si es posible cambiar el cotejamiento y de qué manera, sin que esto afecte la forma en que se representan los caracteres especiales del español en los campos de tipo varchar.

    Espero su comentarios, muchas gracias.

    • Hola!
      Tu base de datos aparece con cotejamiento latin1_swedish_ci porque es la que pone por defecto el phpMyAdmin y el MySQL al crear una nueva base de datos si no se especifica ningún cotejamiento en concreto.
      Claro que puedes cambiarlo. Debes ir con el phpMyAdmin a la base de datos (no a ninguna tabla en concreto, sino a las especificaciones de la base de datos) y en la sección “Operaciones” (esto puede variar entre distintas versiones del phpMyAdmin) puedes elegir un nuevo cotejamiento para tu BD.
      El cambio de cotejamiento podría afectar a los caracteres que tengas almacenados, sobre todo a aquellos que tengan acento, signos de apertura de exclamación, etc. No es que te vaya a suceder con seguridad, pero es bastante probable que afecte a ciertos caracteres.
      Un saludo.

  9. Salvador dice:

    Gracias, muchas Gracias!!

    Saludos!!

  10. Raul dice:

    Muchas gracias por la explicacion, nos sirvio de mucho… gracias maestro!!!!

  11. Ata dice:

    Gracias señor!!!

    Este problema, por desgracia se suele dar bastante y da muchos quebraderos de cabeza

  12. Carlos Fernández San Millán dice:

    Gracias Agustín.

    Claro y conciso como nos tienes acostumbrados … Así da gusto.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s