Регистрозависимый поиск
На днях в одном из своих проектов я столкнулся с очень необычным поведением 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';
Наоборот 🙂
@Oleg
Спасибо.
Подскажите пожалуйста в чем может быть ошибка:
Отправляю запрос к базе например:
«SELECT * FROM fruct, name_fru WHERE fruct.id=name_fru.id_fruct AND fruct.Name_fruct = ‘$_POST[nawsel1]’ AND fruct.sort = ‘$_POST[nawsel2]'»;
Отбор в базе проходит но нет передачи данных в массив возврата {zakaz:[ ]}
@pupok
Конкретно на ваш вопрос — я не знаю ответ, мало кода. Возможно где-то ошибка до вызова запроса или после, при обработке.
По поводу самого кода: так писать нельзя. Код подвержен SQL-инъекциям. К тому же индексы массива оборачивайте в кавычки:
$_POST['nawsel2'].А можно с вами пообщаться в частной переписке через ящик )) если да напишите пожалуйста ящик.
Добрый день.
Увидела ваш макрос «Экспорт таблиц из Excel в HTML», почитала, понравилось как написано, хотя с этими тэгами еще не разобралась до конца.
У меня вопрос от обратного: есть ли у вас макросы для извлечения информации из интернета в таблицы Excel. Как, допустим, в макросе обратиться к исходному коду страницы, чтобы получить данные со странички в интернете того то или того то.
Предположим ищу запчасть к автомобилю 70-32417км, выходит куча листов где кто-то продает, кто-то покупает. Мне надо запчасть в 1 столбик, продавец во 2 столбик, цена в 3 столбик, что ещё в 4 и т.д. И как разделить в макросе, чтобы только сообщения выбирались только по продажам?
Если бы был справочный материал как изучить все свойства и методы, что относится к исходному коду страницы, может быть и проще было, но когда есть пример всегда проще поизучать.
Всего доброго.
Заранее благодарна, Вика
@Вика
Не думаю, что на VBA (язык программирования в Excel) вообще можно подобное написать.
Обычно для извлечения данных с веб страниц используют парсеры. Вот статья про это:
http://tarlyun.com/blog/2011/04/07/parsim-kontent-s-internet-magazina/