Регистрозависимый поиск

23rd Июнь 2011 | Категории: MySQL | Метки: , ,

На днях в одном из своих проектов я столкнулся с очень необычным поведением MySQL. При поиске (по имени) Убийца_матрёшек и убийца_матрешекMySQL выдавал одинаковый результат! Виной всему оказался тип сравнения для поля pl_nick. Но обо всем по порядку.

Для сохранения строковых данных мы обычно используем поля типа VARCHAR или TEXT. При создании таблицы мы явно указываем, какая будет кодировка и как MySQL будет сравнивать эти поля. В подавляющем большинстве случаев используется кодировка UTF8 или CP1251. Для каждой кодировки существует несколько типов сравнения, узнать которые можно командой SHOW COLLATION.

mysql> SHOW COLLATION LIKE 'utf8%';
+--------------------+---------+-----+---------+----------+---------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8_general_ci    | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin           | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci    | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci  | utf8    | 193 |         | Yes      |       8 |
| utf8_latvian_ci    | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci   | utf8    | 195 |         | Yes      |       8 |
| utf8_slovenian_ci  | utf8    | 196 |         | Yes      |       8 |
| utf8_polish_ci     | utf8    | 197 |         | Yes      |       8 |
| utf8_estonian_ci   | utf8    | 198 |         | Yes      |       8 |
| utf8_spanish_ci    | utf8    | 199 |         | Yes      |       8 |
| utf8_swedish_ci    | utf8    | 200 |         | Yes      |       8 |
| utf8_turkish_ci    | utf8    | 201 |         | Yes      |       8 |
| utf8_czech_ci      | utf8    | 202 |         | Yes      |       8 |
| utf8_danish_ci     | utf8    | 203 |         | Yes      |       8 |
| utf8_lithuanian_ci | utf8    | 204 |         | Yes      |       8 |
| utf8_slovak_ci     | utf8    | 205 |         | Yes      |       8 |
| utf8_spanish2_ci   | utf8    | 206 |         | Yes      |       8 |
| utf8_roman_ci      | utf8    | 207 |         | Yes      |       8 |
| utf8_persian_ci    | utf8    | 208 |         | Yes      |       8 |
| utf8_esperanto_ci  | utf8    | 209 |         | Yes      |       8 |
| utf8_hungarian_ci  | utf8    | 210 |         | Yes      |       8 |
+--------------------+---------+-----+---------+----------+---------+
21 rows in set (0.00 sec)

mysql> SHOW COLLATION LIKE 'cp1251%';
+---------------------+---------+----+---------+----------+---------+
| Collation           | Charset | Id | Default | Compiled | Sortlen |
+---------------------+---------+----+---------+----------+---------+
| cp1251_bulgarian_ci | cp1251  | 14 |         | Yes      |       1 |
| cp1251_ukrainian_ci | cp1251  | 23 |         | Yes      |       1 |
| cp1251_bin          | cp1251  | 50 |         | Yes      |       1 |
| cp1251_general_ci   | cp1251  | 51 | Yes     | Yes      |       1 |
| cp1251_general_cs   | cp1251  | 52 |         | Yes      |       1 |
+---------------------+---------+----+---------+----------+---------+
5 rows in set (0.00 sec)

У сравнений есть несколько ключевых параметров. Во-первых, в каждой кодировке всегда присутствует сравнение по умолчанию. Во-вторых, сравнения делятся на три типа (по суффиксу):

  • _bin (binary) — бинарное сравнение;
  • _ci (case insensitive) — не чувствительное к регистру;
  • _cs (case sensitive) — чувствительное к регистру;

В моем случае использовалось utf8_general_ci;

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

SELECT * FROM `hwm_pl` WHERE BINARY `pl_nick`='Убийца_матрёшек';
SELECT * FROM `hwm_pl` WHERE BINARY `pl_nick`='убийца_матрешек';

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

Чтобы избежать подобного поведения при поиске (без редактирования collation самой таблицы или collation поля), можно:

  • использовать *_cs кодировки;
  • устанавливать тип сравнения перед запросом (для cp1251_general_ci):
     SET collation_connection='cp1251_general_cs';

Subscribe without commenting


  1. 18th Октябрь 2011 в 15:47

    _ci (case insensitive) – чувствительное к регистру;
    _cs (case sensitive) – не чувствительное к регистру;

    Наоборот 🙂

  2. Тарлюн Максим
    18th Октябрь 2011 в 15:51

    @Oleg
    Спасибо.

  3. pupok
    4th Июнь 2013 в 12:59

    Подскажите пожалуйста в чем может быть ошибка:
    Отправляю запрос к базе например:
    «SELECT * FROM fruct, name_fru WHERE fruct.id=name_fru.id_fruct AND fruct.Name_fruct = ‘$_POST[nawsel1]’ AND fruct.sort = ‘$_POST[nawsel2]'»;
    Отбор в базе проходит но нет передачи данных в массив возврата {zakaz:[ ]}

  4. Тарлюн Максим
    4th Июнь 2013 в 13:08

    @pupok
    Конкретно на ваш вопрос — я не знаю ответ, мало кода. Возможно где-то ошибка до вызова запроса или после, при обработке.

    По поводу самого кода: так писать нельзя. Код подвержен SQL-инъекциям. К тому же индексы массива оборачивайте в кавычки: $_POST['nawsel2'].

  5. pupok
    4th Июнь 2013 в 15:07

    А можно с вами пообщаться в частной переписке через ящик )) если да напишите пожалуйста ящик.

  6. Вика
    12th Сентябрь 2013 в 13:30

    Добрый день.
    Увидела ваш макрос «Экспорт таблиц из Excel в HTML», почитала, понравилось как написано, хотя с этими тэгами еще не разобралась до конца.
    У меня вопрос от обратного: есть ли у вас макросы для извлечения информации из интернета в таблицы Excel. Как, допустим, в макросе обратиться к исходному коду страницы, чтобы получить данные со странички в интернете того то или того то.
    Предположим ищу запчасть к автомобилю 70-32417км, выходит куча листов где кто-то продает, кто-то покупает. Мне надо запчасть в 1 столбик, продавец во 2 столбик, цена в 3 столбик, что ещё в 4 и т.д. И как разделить в макросе, чтобы только сообщения выбирались только по продажам?
    Если бы был справочный материал как изучить все свойства и методы, что относится к исходному коду страницы, может быть и проще было, но когда есть пример всегда проще поизучать.
    Всего доброго.
    Заранее благодарна, Вика

  7. Тарлюн Максим
    13th Сентябрь 2013 в 21:36

    @Вика
    Не думаю, что на VBA (язык программирования в Excel) вообще можно подобное написать.
    Обычно для извлечения данных с веб страниц используют парсеры. Вот статья про это:
    http://tarlyun.com/blog/2011/04/07/parsim-kontent-s-internet-magazina/