MySQL / 4. Работа со строками

Управление чувствительностью к регистру при поиске по образцу

Задача
Поиск по образцу чувствителен к регистру в тех случаях, когда вы этого не хотите, или наоборот.

Решение
Измените чувствительность строк к регистру.

Обсуждение
По умолчанию операция LIKE не чувствительна к регистру:

mysql> SELECT name, name LIKE '%i%', name LIKE '%I%' FROM metal;

+ ----------+--------------------- + ---------------------+
| name     | name LIKE '%i%' | name LIKE '%I%' |
+ ----------+ --------------------+--------------------- +
| copper   |                          0 |                          0 |
| gold       |                           0 |                         0 |
| iron        |                           1 |                         1 |
| lead        |                          0 |                         0 |
| mercury  |                         0 |                          0 |
| platinum |                          1 |                          1 |
| silver      |                          1 |                          1 |
| tin          |                          1 |                          1 |
+---------- + ---------------------+-------------------- +

В настоящий момент не чувствительна к регистру и операция REGEXP.

mysql> SELECT name, name REGEXP 'i', name REGEXP 'I' FROM metal;

+----------- +-------------------- + --------------------+
| name       | name REGEXP 'i' | name REGEXP 'I' |
+ -----------+ --------------------+ --------------------+
| copper     |                        0 |                         0 |
| gold         |                        0 |                         0 |
| iron         |                         1 |                         1 |
| lead         |                        0 |                         0 |
| mercury  |                         0 |                         0 |
| platinum  |                         1 |                        1 |
| silver       |                         1 |                        1 |
| tin           |                         1 |                        1 |
+ ----------+ ---------------------+------------------- +

Однако до версии MySQL 3.23.4 операции REGEXP были чувствительны к регистру:

mysql> SELECT name, name REGEXP 'i', name REGEXP 'I' FROM metal;

+---------- +-------------------- +-------------------- +
| name      | name REGEXP 'i' | name REGEXP 'I' |
+---------- +-------------------- +-------------------- +
| copper    |                        0 |                         0 |
| gold        |                        0 |                         0 |
| iron         |                        1 |                         0 |
| lead        |                        0 |                         0 |
| mercury  |                        0 |                         0 |
| platinum  |                       1 |                          0 |
| silver       |                       1 |                          0 |
| tin           |                       1 |                          0 |
+ -----------+------------------ + ---------------------+

Обратите внимание на то, что текущее поведение REGEXP (нечувствительность к регистру) может привести к некоторым интуитивно непонятным результатам:

mysql> SELECT 'a' REGEXP '[[:lower:]]', 'a' REGEXP '[[:upper:]]';

+--------------------------- + ----------------------------+
| 'a' REGEXP '[[:lower:]]' | 'a' REGEXP '[[:upper:]]' |
+--------------------------- +---------------------------- +
|                                   1 |                                    1 |
+ ---------------------------+-----------------------------+

Оба выражения истинны, так как в случае нечувствительности к регистру [:lower:] и [:upper:] эквиваленты. Изменить нежелательное для вас поведение операции поиска по образцу в отношении чувствительности к регистру можно посредством тех же приемов,
что и для операции сравнения строк:

• Чтобы сделать поиск по образцу чувствительным к регистру, используйте двоичную строку для любого из операндов (например, при помощи ключевого слова BINARY). Следующий запрос показывает, что обычно недвоичный столбец name не чувствителен к регистру:

mysql> SELECT name, name LIKE '%i%%', name REGEXP 'i' FROM metal;

+ ----------+ ------------------------+ -------------------+
| name      | name LIKE '%i%%' | name REGEXP 'i' |
+ ----------+ ------------------------+ -------------------+
| copper   |                               0 |                        0 |
| gold       |                               0 |                        0 |
| iron        |                               1 |                        1 |
| lead        |                              0 |                        0 |
| mercury  |                              0 |                        0 |
| platinum  |                              1 |                       1 |
| silver       |                              1 |                       1 |
| tin           |                              1 |                       1 |
+---------- + ------------------------+------------------- +

Используем ключевое слово BINARY, чтобы заставить значения name стать чувствительными к регистру:

mysql> SELECT name, BINARY name LIKE '%I%', BINARY name REGEXP 'I' FROM metal;

+----------- +------------------------------- +------------------------------ +
| name       | BINARY name LIKE '%I%' | BINARY name REGEXP 'I' |
+----------- + -------------------------------+ ------------------------------+
| copper    |                                        0 |                                      0 |
| gold        |                                        0 |                                       0 |
| iron         |                                        0 |                                      0 |
| lead         |                                       0 |                                      0 |
| mercury   |                                       0 |                                      0 |
| platinum   |                                      0 |                                       0 |
| silver        |                                      0 |                                       0 |
| tin            |                                      0 |                                       0 |
+ -----------+------------------------------ +------------------------------ +

Использование BINARY заставляет [:lower:] и [:upper:] работать в регулярных выражениях так, как вам хотелось бы. Второе выражение следующего запроса выдает результат, который на самом деле является истинным только для букв верхнего регистра:

mysql> SELECT 'a' REGEXP '[[:upper:]]', BINARY 'a' REGEXP '[[:upper:]]';

+ ----------------------------+-------------------------------------- +
| 'a' REGEXP '[[:upper:]]' | BINARY 'a' REGEXP '[[:upper:]]' |
+ ----------------------------+ -------------------------------------+
|                                    1 |                                                0 |
+---------------------------- + -------------------------------------+

• Поиск по образцу для двоичного столбца чувствителен к регистру. Чтобы сделать его нечувствительным, преобразуйте оба операнда в один регистр. Давайте изменим таблицу metal, добавив в нее столбец binname, аналогичный столбцу name, но имеющий тип VARCHAR BINARY, а не VARCHAR:

mysql> ALTER TABLE metal ADD binname VARCHAR(20) BINARY;
mysql> UPDATE metal SET binname = name;

Первый из представленных ниже запросов показывает, что двоичный столбец binname чувствителен к регистру при поиске по образцу, а второй запрос показывает, как заставить столбец изменить свое поведение с помощью UPPER():

mysql> SELECT binname, binname LIKE '%I%', binname REGEXP 'I'
     > FROM metal;

+ -----------+ -------------------------+ -----------------------+
| binname  | binname LIKE '%I%' | binname REGEXP 'I' |
+ -----------+------------------------- +----------------------- +
| copper    |                                0 |                             0 |
| gold        |                                0 |                             0 |
| iron         |                                0 |                             0 |
| lead         |                               0 |                             0 |
| mercury  |                                0 |                             0 |
| platinum  |                                0 |                            0 |
| silver       |                                0 |                            0 |
| tin           |                                0 |                            0 |
+----------- +------------------------- + ----------------------+

mysql> SELECT binname, UPPER(binname) LIKE '%I%', UPPER(binname) REGEXP 'I'
     > FROM metal;

+ ------------+---------------------------------- +----------------------------------+
| binname   | UPPER(binname) LIKE '%I%' | UPPER(binname) REGEXP 'I' |
+------------ +----------------------------------+ ----------------------------------+
| copper     |                                            0 |                                            0 |
| gold         |                                            0 |                                            0 |
| iron          |                                            1 |                                           1 |
| lead         |                                            0 |                                            0 |
| mercury   |                                            0 |                                           0 |
| platinum   |                                            1 |                                           1 |
| silver        |                                            1 |                                           1 |
| tin            |                                            1 |                                           1 |
+----------- + -----------------------------------+--------------------------------- +

Статьи по MySQL на эту тему:

FULLTEXT поиск и короткие слова
Включение и исключение слов из FULLTEXT - поиска
Поиск с помощью индекса FULLTEXT
Поиск фразы при помощи индекса FULLTEXT

Вернуться в раздел: MySQL / 4. Работа со строками