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

Задача
Сравнение строк чувствительно к регистру тогда, когда это нежелательно, или наоборот.

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

Обсуждение
В примерах предыдущих разделов не учитывался регистр букв. Но в некоторых случаях необходимо иметь уверенность в том, что строковая операция чувствительна (или не чувствительна) к регистру. В этом разделе рассказано о том, как добиться этого для обычных сравнений.

По умолчанию сравнение строк в MySQL не чувствительно к регистру:

mysql> SELECT name, name = 'lead', name = 'LEAD' FROM metal;

+----------+----------------+------------------+
| name | name = 'lead' | name = 'LEAD' |
+----------+----------------+------------------+
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 0 | 0 |
| lead | 1 | 1 |
| mercury | 0 | 0 |
| platinum | 0 | 0 |
| silver | 0 | 0 |
| tin | 0 | 0 |
+----------+----------------+------------------+

Нечувствительность к регистру затрагивает и сравнения на взаимный порядок:

mysql> SELECT name, name < 'lead', name < 'LEAD' FROM metal;

+----------+----------------+------------------+
| name | name < 'lead' | name < 'LEAD' |
+----------+----------------+------------------+
| copper | 1 | 1 |
| gold | 1 | 1 |
| iron | 1 | 1 |
| lead | 0 | 0 |
| mercury | 0 | 0 |
| platinum | 0 | 0 |
| silver | 0 | 0 |
| tin | 0 | 0 |
+----------+-----------------+------------------+

Если вы знакомы со схемой сортировки ASCII, то знаете, что коды ASCII для букв нижнего регистра больше, чем коды букв верхнего регистра, так что результаты второго столбца должны были бы вас удивить.


Такие результаты показывают, что по умолчанию упорядочение строк производится без учета регистра букв, так что и A, и a считаются лексически меньшими, чем B. Сравнения строк чувствительны к регистру, только если хотя бы один из операндов является двоичной строкой. Существуют следующие методы
контроля чувствительности к регистру в операциях сравнения строк:

• Чтобы сделать чувствительным к регистру сравнение, которое само по себе таким не было, приведите один из операндов в двоичную форму, используя ключевое слово BINARY. Не имеет значения, какую именно из строк вы сделаете двоичной, – как только одна из них станет двоичной, сравнение станет чувствительным к регистру:

mysql> SELECT name, name = BINARY 'lead', BINARY name = 'LEAD' FROM metal;

+----------+--------------------------+----------------------------+
| name | name = BINARY 'lead' | BINARY name = 'LEAD' |
+----------+--------------------------+----------------------------+
| copper | 0 | 0 |
| gold | 0 | 0 |
| iron | 0 | 0 |
| lead | 1 | 0 |
| mercury | 0 | 0 |
| platinum | 0 | 0 |
| silver | 0 | 0 |
| tin | 0 | 0 |
+----------+--------------------------+----------------------------+

Начиная с MySQL 3.23 в качестве оператора приведения типа используется BINARY.



• Чтобы сделать нечувствительной к регистру операцию сравнения, которая должна была бы учитывать регистр, преобразуйте обе строки к одному регистру, используя функцию UPPER() или LOWER():

mysql> SELECT UPPER('A'), UPPER('b'), UPPER('A') < UPPER('b');

+-------------+-------------+-----------------------------+
| UPPER('A') | UPPER('b') | UPPER('A') < UPPER('b') |
+-------------+-------------+-----------------------------+
| A | B | 1 |
+-------------+-------------+-----------------------------+

mysql> SELECT LOWER('A'), LOWER('b'), LOWER('A') < LOWER('b');

+--------------+--------------+-------------------------------+
| LOWER('A') | LOWER('b') | LOWER('A') < LOWER('b') |
+--------------+--------------+-------------------------------+
| a | b | 1 |
+--------------+--------------+-------------------------------+

Эти же приемы можно применять и к функциям сравнения строк.


Например, функция STRCMP() принимает два строковых аргумента и возвращает –1, 0 или 1 в зависимости от того, лексически меньше, равна или больше первая строка по отношению ко второй. До версии MySQL 4.0.0 включительно функция STRCMP() была чувствительна к регистру; она всегда воспринимала свои аргументы как двоичные строки независимо от их реального типа:

mysql> SELECT STRCMP('Abc','abc'), STRCMP('abc','abc'), STRCMP('abc','Abc');

+------------------------+------------------------+------------------------+
| STRCMP('Abc','abc') | STRCMP('abc','abc') | STRCMP('abc','Abc') |
+------------------------+------------------------+------------------------+
| 1 | 0 | 1 |
+------------------------+------------------------+------------------------+

Однако начиная с MySQL 4.0.1 функция STRCMP() больше не чувствительна к регистру:

mysql> SELECT STRCMP('Abc','abc'), STRCMP('abc','abc'), STRCMP('abc','Abc');

+------------------------+------------------------+------------------------+
| STRCMP('Abc','abc') | STRCMP('abc','abc') | STRCMP('abc','Abc') |
+------------------------+------------------------+------------------------+
| 0 | 0 | 0 |
+------------------------+------------------------+------------------------+

Чтобы сохранить поведение функции в версиях до 4.0.1, сделайте один из ее аргументов двоичной строкой:

mysql> SELECT STRCMP(BINARY 'Abc','abc'), STRCMP(BINARY 'abc','Abc');

+----------------------------------+----------------------------------+
| STRCMP(BINARY 'Abc','abc') | STRCMP(BINARY 'abc','Abc') |
+----------------------------------+----------------------------------+
| 1 | 1 |
+----------------------------------+----------------------------------+

Кстати, заметьте, что нулевое и ненулевое значения, возвращаемые функцией STRCMP(), означают равенство и неравенство соответственно. В этом отличие функции от оператора сравнения =, который возвращает нулевое и не нулевое значения для неравенства и равенства соответственно. Чтобы избежать проблем, запомните основные правила, определяющие, является ли строка двоичной:

• Любую буквенную строку, строковое выражение или строковый столбец можно сделать двоичными, предварив их ключевым словом BINARY. Если же ключевого слова нет, действуют следующие правила.
• Строковое выражение является двоичным, если хотя бы одна из составляющих его строк двоичная, иначе оно не является двоичным. Например,результат, возвращенный выражением CONCAT(), является двоичным, так как второй аргумент – двоичный:

CONCAT('This is a ',BINARY 'binary',' string')

• Чувствительность строкового столбца к регистру определяется его типом. Типы CHAR и VARCHAR по умолчанию не чувствительны к регистру, но их можно объявить как BINARY, тогда они станут чувствительными к регистру. Столбцы типов ENUM, SET и TEXT не чувствительны к регистру, а столбцы типа BLOB – чувствительны.

Итак, операции сравнения чувствительны к регистру, если в них участвует двоичная буквенная строка, или строковое выражение, или столбец типа CHAR BINARY, VARCHAR BINARY или BLOB. Сравнения же, в которых участвуют только недвоичные буквенные строки, или строковые выражения, или столбцы типа CHAR, VARCHAR, ENUM, SET или TEXT, не чувствительны к регистру. Столбцы ENUM и SET не чувствительны к регистру. Более того, поскольку они внутренне хранятся в числовом виде, их нельзя объявлять как чувствительные к регистру в определении таблицы (добавляя ключевое слово BINARY). Но
вы можете поставить ключевое слово BINARY в сравнении перед значениями ENUM и SET, чтобы сделать операцию чувствительной к регистру.

Чувствительность к регистру и скорость выполнения сравнений
Обычно чувствительные к регистру сравнения, содержащие двоичные строки, работают немного быстрее, чем нечувствительные к регистру, так как MySQL не приходится во время операции приводить буквы кодному регистру.

Если оказалось, что вы объявили столбец, используя тип, несовместимый с теми операциями сравнения, которые предполагается для него применять, вы можете изменить тип столбца при помощи предложения ALTER TABLE. Предположим, что у вас есть таблица для хранения новостных статей:

CREATE TABLE news
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
article BLOB NOT NULL,
PRIMARY KEY (id)
);

Столбец article объявлен как BLOB, то есть тип, чувствительный к регистру. Если вы захотите преобразовать столбец так, чтобы он не был чувствителен к регистру, то можете изменить его тип на TEXT, используя одно из предложений ALTER TABLE:

ALTER TABLE news MODIFY article TEXT NOT NULL;
ALTER TABLE news CHANGE article article TEXT NOT NULL;

До версии MySQL 3.22.16 предложение ALTER TABLE ... MODIFY недоступно, и если вы работаете с более ранней версией, то можете использовать только ALTER TABLE ... CHANGE.


FULLTEXT поиск и короткие слова
Буквальная интерпретация метасимволов в шаблонах
Включение и исключение слов из FULLTEXT - поиска
Поиск по образцу с помощью регулярных выражений
Поиск по образцу с помощью шаблонов SQL