Jak mogę porównać ciąg znaków SQL z rozróżnianiem wielkości liter w MySQL?

Mam funkcję, która zwraca pięć znaków wielkimi literami. Jeśli wykonam zapytanie na tym ciągu, zwróci ono wartość bez względu na wielkość liter.

Jak ustawić rozróżnianie wielkości liter w zapytaniach MySQL?

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

Domyślnym zestawem znaków i zestawieniem są latin1 i latin1_swedish_ci, więc porównania niebinarnych ciągów znaków domyślnie nie uwzględniają wielkości liter. Oznacza to, że jeśli wyszukujesz z col_name LIKE 'a%', otrzymujesz wszystkie wartości kolumn zaczynające się od A lub a. Aby rozróżnić wielkość liter w wyszukiwaniu, upewnij się, że jeden z operandów ma rozróżnianie wielkości liter lub sortowanie binarne. Na przykład, jeśli porównujesz kolumnę i ciąg znaków, które oba mają zestaw znaków latin1, możesz użyć operatora COLLATE, aby spowodować, że albo operand będzie miał zestawienie latin1_general_cs lub 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

Jeśli chcesz, aby kolumna była zawsze traktowana z uwzględnieniem wielkości liter, zadeklaruj ją za pomocą sortowania z rozróżnianiem wielkości liter lub binarnym.

Dobrą wiadomością jest to, że jeśli chcesz wykonać rozróżnianie wielkości liter, bardzo łatwo jest zrobić:

SELECT *  FROM `table` WHERE BINARY `column` = 'value'

Odpowiedź wysłana przez Craiga White'a ma wysoką karę wydajności

SELECT *  FROM `table` WHERE BINARY `column` = 'value'

ponieważ nie używa indeksów. Więc albo musisz zmienić układ tabeli, jak wspomniano tutaj https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html .

LUB

Najłatwiejsza poprawka, powinieneś użyć BINARNEJ wartości.

SELECT *  FROM `table` WHERE `column` = BINARY 'value'

Na przykład.

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 rząd w zestawie (0,00 s)

Zamiast operatora = możesz użyć LIKE lub 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'

W swoim stanie zajmie „a”, a nie „A”

Aby skorzystać z indeksu przed użyciem BINARY, możesz zrobić coś takiego, jeśli masz duże tabele.

SELECT
   *
FROM
   (SELECT * FROM `table` WHERE `column` = 'value') as firstresult
WHERE
   BINARY `column` = 'value'

Podkwerenda spowodowałoby naprawdę mały podzbiór bez rozróżniania wielkości liter, którego następnie wybrałeś jedyne dopasowanie z rozróżnianiem wielkości liter.

Najodpowiedniejszym sposobem przeprowadzenia porównania łańcucha z rozróżnianiem wielkości liter bez zmiany sortowania kolumny, której dotyczy zapytanie, jest jawne określenie zestawu znaków i zestawienia dla wartości, z którą porównywana jest kolumna.

select * from `table` where `column` = convert('value' using utf8mb4) collate utf8mb4_bin;

Dlaczego nie użyć binary?

Korzystanie z binaryoperatora jest niewskazane, ponieważ porównuje rzeczywiste bajty zakodowanych ciągów. Jeśli porównasz rzeczywiste bajty dwóch ciągów kodowanych przy użyciu różnych zestawów znaków, dwa ciągi, które należy uznać za takie same, mogą nie być równe. Na przykład, jeśli masz kolumnę korzystającą z latin1zestawu znaków, a zestaw znaków serwer / sesja to utf8mb4, to kiedy porównasz kolumnę z ciągiem zawierającym akcent, taki jak „kawiarnia”, nie będzie pasować do wierszy zawierających ten sam ciąg! To dlatego, że w latin1E jest kodowane jako bajt 0xE9, ale w utf8to dwa bajty: 0xC3A9.

Dlaczego warto korzystać convertrównie dobrze collate?

Sortowanie musi pasować do zestawu znaków. Więc jeśli twój serwer lub sesja jest ustawiona na używanie latin1zestawu znaków, musisz użyć, collate latin1_binale jeśli twój zestaw znaków jest taki utf8mb4, musisz użyć collate utf8mb4_bin. Dlatego najbardziej niezawodnym rozwiązaniem jest zawsze przekształcenie wartości w najbardziej elastyczny zestaw znaków i użycie binarnego zestawienia dla tego zestawu znaków.

Po co stosować wartość converti collatedo wartości, a nie do kolumny?

Zastosowanie dowolnej funkcji transformacji do kolumny przed porównaniem uniemożliwia silnikowi zapytań użycie indeksu, jeśli taki istnieje dla kolumny, co może znacznie spowolnić zapytanie. Dlatego zawsze lepiej jest, jeśli to możliwe, przekształcić wartość. Po przeprowadzeniu porównania dwóch wartości ciągów, a jedna z nich ma jawnie określone sortowanie, aparat zapytań użyje jawnego sortowania, niezależnie od tego, do której wartości zostanie zastosowane.

Czułość akcentu

Należy zauważyć, że MySql nie tylko nie rozróżnia wielkości liter w kolumnach korzystających z _cisortowania (co zwykle jest ustawieniem domyślnym), ale także nie uwzględnia rozróżniania akcentów . To znaczy że 'é' = 'e'. Użycie binarnego sortowania (lub binaryoperatora) sprawi, że porównania ciągów będą rozróżniać zarówno akcent, jak i wielkość liter.

Co to jest utf8mb4?

Zestaw utf8znaków w MySql to alias, utf8mb3który został przestarzały w najnowszych wersjach, ponieważ nie obsługuje 4 bajtów znaków (co jest ważne przy kodowaniu ciągów takich jak 🐈). Jeśli chcesz używać kodowania znaków UTF8 z MySql, powinieneś używać utf8mb4zestawu znaków.

Poniżej podano wersje MySQL równe lub wyższe niż 5,5.

Dodaj do /etc/mysql/my.cnf

  [mysqld]
  ...
  character-set-server=utf8
  collation-server=utf8_bin
  ...

Wszystkie inne zestawienia, które próbowałem, wydawały się nie uwzględniać wielkości liter, działało tylko „utf8_bin”.

Nie zapomnij ponownie uruchomić mysql po tym:

   sudo service mysql restart

Według http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html istnieje również „latin1_bin”.

„Utf8_general_cs” nie został zaakceptowany podczas uruchamiania mysql. (Czytam „_cs” jako „rozróżnia małe i wielkie litery” - ???).

Możesz użyć BINARY do rozróżniania wielkości liter w ten sposób

select * from tb_app where BINARY android_package='com.Mtime';

niestety ten sql nie może używać indeksu, będziesz odczuwał spadek wydajności zapytań zależnych od tego indeksu

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 |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+

Na szczęście mam kilka sztuczek, aby rozwiązać ten problem

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 |
+----+-------------+--------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+  

Doskonały!

Udostępniam Ci kod z funkcji porównującej hasła:

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;

Nie trzeba nic zmieniać na poziomie DB, wystarczy zmienić zapytanie SQL, to zadziała.

Przykład -

"SELECT * FROM <TABLE> where userId = '" + iv_userId + "' AND password = BINARY '" + iv_password + "'";

Binarne słowo kluczowe rozróżnia małe i wielkie litery.

Domyślnie mysql nie rozróżnia wielkości liter, spróbuj zmienić sortowanie językowe na latin1_general_cs