¿Cómo puedo hacer una comparación de cadenas SQL que distinga entre mayúsculas y minúsculas en MySQL?
Tengo una función que devuelve cinco caracteres con mayúsculas y minúsculas mixtas. Si hago una consulta sobre esta cadena, devolverá el valor independientemente del caso.
¿Cómo puedo hacer que las consultas de cadenas MySQL distingan entre mayúsculas y minúsculas?
Utilice esto para realizar una consulta que distinga entre mayúsculas y minúsculas:
SELECT * FROM `table` WHERE BINARY `column` = 'value'
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
El juego de caracteres y la intercalación predeterminados son latin1 y latin1_swedish_ci, por lo que las comparaciones de cadenas no binarias no distinguen entre mayúsculas y minúsculas de forma predeterminada. Esto significa que si busca con col_name LIKE 'a%', obtendrá todos los valores de columna que comiencen con A o a. Para que esta búsqueda distinga entre mayúsculas y minúsculas, asegúrese de que uno de los operandos tenga una intercalación binaria o que distinga entre mayúsculas y minúsculas. Por ejemplo, si está comparando una columna y una cadena que tienen el conjunto de caracteres latin1, puede usar el operador COLLATE para hacer que cualquiera de los operandos tenga la intercalación latin1_general_cs o latin1_bin:
col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin
Si desea que una columna siempre se trate distinguiendo entre mayúsculas y minúsculas, declarela con una intercalación binaria o que distinga entre mayúsculas y minúsculas.
La respuesta publicada por Craig White tiene una gran penalización de rendimiento.
SELECT * FROM `table` WHERE BINARY `column` = 'value'
porque no utiliza índices. Entonces, debe cambiar la clasificación de la tabla como se menciona aquí https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html .
O
La solución más fácil es utilizar un valor BINARIO.
SELECT * FROM `table` WHERE `column` = BINARY 'value'
P.ej
mysql> EXPLAIN SELECT * FROM temp1 WHERE BINARY col1 = "ABC" AND col2 = "DEF" ;
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | temp1 | ALL | NULL | NULL | NULL | NULL | 190543 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
contra
mysql> EXPLAIN SELECT * FROM temp1 WHERE col1 = BINARY "ABC" AND col2 = "DEF" ;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
| 1 | SIMPLE | temp1 | range | col1_2e9e898e | col1_2e9e898e | 93 | NULL | 2 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+
enter code here
1 fila en conjunto (0,00 seg)
En lugar de utilizar el operador =, es posible que desee utilizar LIKE o LIKE BINARY
// this returns 1 (true)
select 'A' like 'a'
// this returns 0 (false)
select 'A' like binary 'a'
select * from user where username like binary 'a'
Llevará 'a' y no 'A' en su estado
La forma más correcta de realizar una comparación de cadenas que distinga entre mayúsculas y minúsculas sin cambiar la clasificación de la columna que se consulta es especificar explícitamente un juego de caracteres y una clasificación para el valor con el que se compara la columna.
select * from `table` where `column` = convert('value' using utf8mb4) collate utf8mb4_bin;
¿Por qué no utilizar binary?
No es aconsejable utilizar el binaryoperador porque compara los bytes reales de las cadenas codificadas. Si compara los bytes reales de dos cadenas codificadas utilizando diferentes juegos de caracteres, dos cadenas que deben considerarse iguales pueden no ser iguales. Por ejemplo, si tiene una columna que usa el latin1juego de caracteres y el juego de caracteres de su servidor/sesión es utf8mb4, cuando compare la columna con una cadena que contiene un acento como 'café', ¡no coincidirá con las filas que contienen esa misma cadena! Esto se debe a que en latin1é está codificado como el byte 0xE9pero en utf8él hay dos bytes: 0xC3A9.
¿ Por qué utilizar converttambién collate?
Las intercalaciones deben coincidir con el conjunto de caracteres. Entonces, si su servidor o sesión está configurado para usar el latin1juego de caracteres, debe usar, collate latin1_binpero si su juego de caracteres lo está, utf8mb4debe usar collate utf8mb4_bin. Por lo tanto, la solución más sólida es convertir siempre el valor al juego de caracteres más flexible y utilizar la clasificación binaria para ese juego de caracteres.
¿Por qué aplicar el converty collateal valor y no a la columna?
Cuando aplica cualquier función de transformación a una columna antes de realizar una comparación, evita que el motor de consultas utilice un índice si existe uno para la columna, lo que podría ralentizar drásticamente su consulta. Por lo tanto, siempre es mejor transformar el valor siempre que sea posible. Cuando se realiza una comparación entre dos valores de cadena y uno de ellos tiene una intercalación especificada explícitamente, el motor de consultas utilizará la intercalación explícita, independientemente del valor al que se aplique.
Sensibilidad al acento
Es importante tener en cuenta que MySql no sólo distingue entre mayúsculas y minúsculas para las columnas que utilizan una _ciintercalación (que suele ser la opción predeterminada), sino que tampoco distingue los acentos . Esto significa que 'é' = 'e'. El uso de una intercalación binaria (o el binaryoperador) hará que las comparaciones de cadenas sean sensibles al acento y a las mayúsculas y minúsculas.
Qué es utf8mb4?
El utf8conjunto de caracteres en MySql es un alias utf8mb3que ha quedado obsoleto en versiones recientes porque no admite caracteres de 4 bytes (lo cual es importante para codificar cadenas como 🐈). Si desea utilizar la codificación de caracteres UTF8 con MySql, entonces debería utilizar el utf8mb4juego de caracteres.
