У меня есть функция, которая возвращает пять символов в смешанном регистре. Если я сделаю запрос к этой строке, она вернет значение независимо от регистра.
Как сделать строковые запросы MySQL чувствительными к регистру?
У меня есть функция, которая возвращает пять символов в смешанном регистре. Если я сделаю запрос к этой строке, она вернет значение независимо от регистра.
Как сделать строковые запросы MySQL чувствительными к регистру?
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
Набор символов и сопоставление по умолчанию - latin1 и latin1_swedish_ci, поэтому небинарные сравнения строк по умолчанию нечувствительны к регистру. Это означает, что если вы выполняете поиск с col_name LIKE 'a%', вы получите все значения столбцов, которые начинаются с A или a. Чтобы сделать этот поиск чувствительным к регистру, убедитесь, что один из операндов имеет чувствительное к регистру или двоичное сопоставление. Например, если вы сравниваете столбец и строку, которые оба имеют набор символов latin1, вы можете использовать оператор COLLATE, чтобы любой операнд имел сопоставление latin1_general_cs или 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
Если вы хотите, чтобы столбец всегда обрабатывался с учетом регистра, объявите его с учетом регистра или двоичной сортировкой.
SELECT 'email' COLLATE utf8_bin = 'Email'
- person piotrekkr; 23.04.2013
ASA
ГДЕ CAST (имя КАК ДВОИЧНОЕ) КАК "% Net%"
- person JScarry; 05.12.2014
Хорошая новость в том, что если вам нужно сделать запрос с учетом регистра, это очень просто сделать:
SELECT * FROM `table` WHERE BINARY `column` = 'value'
latin1_bin
, описанным выше. Возможно, мне придется провести несколько тестов, когда у меня появится шанс, и я опубликую свои результаты.
- person TMH; 20.05.2014
convert(char(0x65,0xcc,0x88) using utf8)
(т.е. e
с добавлением ¨
) и convert(char(0xc3,0xab) using utf8)
(т.е. ë
), но добавление BINARY
сделает их неравными.
- person mvds; 08.06.2015
SELECT BINARY 'Ñ'='Ñ', BINARY 'Ñ'='ñ'
(возвращает 1 и 0)
- person golimar; 02.06.2016
SELECT * FROM `table` WHERE `column` = BINARY 'value'
- person mikep; 18.12.2019
Ответ, опубликованный Крейгом Уайтом, имеет большое снижение производительности
SELECT * FROM `table` WHERE BINARY `column` = 'value'
потому что он не использует индексы. Итак, вам либо нужно изменить параметры сортировки таблицы, как указано здесь https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html.
OR
Самое простое исправление, вы должны использовать ДВОИЧНОЕ значение.
SELECT * FROM `table` WHERE `column` = BINARY 'value'
E.g.
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 |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
VS
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 ряд в комплекте (0,00 сек)
Вместо использования оператора = вы можете использовать LIKE или 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'
В его состоянии будет буква "А", а не "А".
Самый правильный способ выполнить сравнение строк с учетом регистра без изменения сопоставления запрашиваемого столбца - это явно указать набор символов и сопоставление для значения, с которым сравнивается столбец.
select * from `table` where `column` = convert('value' using utf8mb4) collate utf8mb4_bin;
binary
?Использование оператора binary
не рекомендуется, поскольку он сравнивает фактические байты закодированных строк. Если вы сравните фактические байты двух строк, закодированных с использованием разных наборов символов, две строки, которые следует считать одинаковыми, могут не быть равными. Например, если у вас есть столбец, в котором используется набор символов latin1
, а набор символов вашего сервера / сеанса - utf8mb4
, то при сравнении столбца со строкой, содержащей диакритический знак, например, 'café', строки, содержащие ту же строку, не будут совпадать. ! Это потому, что в latin1
é кодируется как байт 0xE9
, а в utf8
это два байта: 0xC3A9
.
convert
так же, как collate
?Сопоставления должны соответствовать набору символов. Итак, если ваш сервер или сеанс настроен на использование набора символов latin1
, вы должны использовать collate latin1_bin
, но если ваш набор символов utf8mb4
, вы должны использовать collate utf8mb4_bin
. Поэтому наиболее надежное решение - всегда преобразовывать значение в наиболее гибкий набор символов и использовать двоичное сопоставление для этого набора символов.
convert
и collate
к значению, а не к столбцу?Когда вы применяете любую функцию преобразования к столбцу перед сравнением, это не позволяет механизму запросов использовать индекс, если он существует для столбца, что может значительно замедлить ваш запрос. Поэтому всегда лучше преобразовать значение, где это возможно. Когда сравнение выполняется между двумя строковыми значениями и одно из них имеет явно заданное сопоставление, механизм запросов будет использовать явное сопоставление, независимо от того, к какому значению оно применяется.
Важно отметить, что MySql не только нечувствителен к регистру для столбцов, использующих сортировку _ci
(которая обычно используется по умолчанию), но также нечувствителен к акценту. Это означает, что 'é' = 'e'
. Использование двоичного сопоставления (или оператора binary
) сделает сравнение строк чувствительным к акценту, а также к регистру.
utf8mb4
?Набор символов utf8
в MySql является псевдонимом для utf8mb3
, который был устарело в последних версиях, поскольку не поддерживает 4-байтовые символы (что важно для кодирования строк, таких как ????). Если вы хотите использовать кодировку символов UTF8 с MySql, тогда вам следует использовать кодировку utf8mb4
.
Чтобы использовать индекс перед использованием BINARY, вы можете сделать что-то подобное, если у вас большие таблицы.
SELECT
*
FROM
(SELECT * FROM `table` WHERE `column` = 'value') as firstresult
WHERE
BINARY `column` = 'value'
В результате подзапроса будет получено действительно небольшое подмножество без учета регистра, из которого вы затем выбираете единственное совпадение с учетом регистра.
Вы можете использовать BINARY с учетом регистра, например
select * from tb_app where BINARY android_package='com.Mtime';
к сожалению, этот sql не может использовать индекс, вы столкнетесь с падением производительности запросов, зависящих от этого индекса.
mysql> explain select * from tb_app where BINARY android_package='com.Mtime';
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | tb_app | NULL | ALL | NULL | NULL | NULL | NULL | 1590351 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
К счастью, у меня есть несколько уловок для решения этой проблемы.
mysql> explain select * from tb_app where android_package='com.Mtime' and BINARY android_package='com.Mtime';
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_app | NULL | ref | idx_android_pkg | idx_android_pkg | 771 | const | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
Ниже приведены версии MySQL, равные или выше 5.5.
Добавить в /etc/mysql/my.cnf
[mysqld]
...
character-set-server=utf8
collation-server=utf8_bin
...
Все другие сопоставления, которые я пробовал, казались нечувствительными к регистру, работал только "utf8_bin".
Не забудьте после этого перезапустить mysql:
sudo service mysql restart
Согласно http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html также есть "latin1_bin".
«Utf8_general_cs» не был принят запуском mysql. (Я прочитал "_cs" как "чувствительный к регистру" - ???).
Не нужно ничего менять на уровне БД, просто вам нужно внести изменения в SQL-запрос, он будет работать.
Пример -
"SELECT * FROM <TABLE> where userId = '" + iv_userId + "' AND password = BINARY '" + iv_password + "'";
Ключевое слово Binary сделает регистр чувствительным.
Отлично!
Делюсь с вами кодом из функции, которая сравнивает пароли:
SET pSignal =
(SELECT DECODE(r.usignal,'YOURSTRINGKEY') FROM rsw_uds r WHERE r.uname =
in_usdname AND r.uvige = 1);
SET pSuccess =(SELECT in_usdsignal LIKE BINARY pSignal);
IF pSuccess = 1 THEN
/*Your code if match*/
ELSE
/*Your code if don't match*/
END IF;
declare pSuccess BINARY;
в начале
- person adinas; 05.08.2018
mysql по умолчанию не чувствителен к регистру, попробуйте изменить сортировку языка на latin1_general_cs
SELECT 'a' = 'A'; # Result: 1
. Это может зависеть от каких-то глобальных настроек. - person donquixote   schedule 13.07.2019