select concat(fName,' ',lName) fullname
from tbl
where concat(' ',fName,' ',lName,' ') like '% Mahendra %'
and concat(' ',fName,' ',lName,' ') like '% dhoni %'
This will most certainly put to rest any hopes of a well performing query
A variation on the theme
select concat(fName,' ',lName) fullname
from tbl
where (concat(fName,' ',lName) like '%Mahendra%dhoni%'
or concat(lName,' ',fName) like '%Mahendra%dhoni%')
The 2nd version doesn’t care about full part matching, e.g. dhoni will match madhonie
Both of these queries find the name correctly. Note that there are % before and after the name to match, as well as % for every space in the name.
create table tbl (fname varchar(100), lname varchar(100));
insert tbl select 'Mahendra singh', 'dhoni';
select concat(fName,' ',lName) fullname
from tbl
where (concat(fName,' ',lName) like '%Mahendra%dhoni%'
or concat(lName,' ',fName) like '%Mahendra%dhoni%');
select concat(fName,' ',lName) fullname
from tbl
where (concat(fName,' ',lName) like '%dhoni%Mahendra%'
or concat(lName,' ',fName) like '%dhoni%Mahendra%');
LIKE SQL — это оператор, который используется для поиска строк, содержащих определённый шаблон символов. Подробнее о нём читайте в нашей статье.
- Синтаксис LIKE SQL
- Примеры использования LIKE SQL
Использовать оператор LIKE в SQL очень просто: достаточно прописать стандартную команду выбора SELECT * FROM и задать условие через WHERE, где и будет использован оператор LIKE.
Шаблон LIKE SQL:
SELECT column1, column2, ...FROM table_nameWHERE columnN LIKE pattern;
Существует два подстановочных знака, которые используются в сочетании с оператором LIKE SQL:
- % — знак процента представляет собой ноль, один или несколько символов;
- _ — подчёркивание представляет собой один символ.
Примеры использования LIKE SQL
Рассмотрим SQL LIKE примеры. Представим, что вы хотите найти все имена, начинающиеся с буквы J. Для этого достаточно использовать следующий запрос:
SELECT * FROM table_name WHERE name LIKE 'J%';
В данном случае символ % используется для указания любого количества символов после J. Таким образом, запрос найдёт все имена, которые начинаются с буквы J, независимо от того, какие символы следуют за ней.
Ещё один пример — поиск всех адресов электронной почты, содержащих слово gmail. Для этого можно использовать следующий запрос:
SELECT * FROM table_name WHERE email LIKE '%gmail%';
Здесь символы % используются для указания, что слово gmail может быть в любом месте в адресе электронной почты.
Также можно использовать символ _ для указания одного символа. Например, запрос ниже найдет все имена, состоящие из шести символов. Эти имена должны начинаться с буквы J и заканчиваться буквой n:
SELECT * FROM table_name WHERE name LIKE 'J____n';
Здесь каждый символ _ указывает на любой один символ.
Иногда символы % и _ сами могут быть частью искомой строки. В таких случаях их нужно экранировать. Например, запрос ниже найдет все имена, содержащие символ %:
SELECT * FROM table_name WHERE name LIKE '%%%';
Это было короткое руководство по работе с SQL LIKE. Также вы можете почитать о других основных командах SQL.
Самым популярным способом доступа к реляционным базам данных является язык запросов SQL. Именно с ним мы и будем знакомиться в этой главе. Мне кажется, проще всего начинать знакомство с доступа к данным, потому что это самый важный и часто используемый компонент и знание, которое необходимо программистам и тестерам.
Для те, кто знает и тем более говорит на английском язык запросов будет прост, потому что построение команд по своей структуре похоже, как мы строим предложения, чтобы попросить голосовой помощник сделать что-то.
Для тестирования нам понадобиться какая-то база данных, на которой мы будем тренироваться. Так как создание самой базы и таблиц я решил отложить на потом, я подготовил файл, который создаст для вас две таблицы.
Phone:
| Phoneid | Firstname | Lastname | Phone | Cityid |
|---|---|---|---|---|
| 1 | John | Doe | 4144122 | 1 |
| 2 | Steve | Doe | 414124 | 1 |
| 3 | Johnatan | Something | 4142947 | 2 |
| 4 | Donald | Trump | 414251123 | 2 |
| 5 | Alice | Cooper | 414254234 | 2 |
| 6 | Michael | Jackson | 4142544 | 3 |
| 7 | John | Abama | 414254422 | 3 |
| 8 | Andre | Jackson | 414254422 | 3 |
| 9 | Mark | Oh | 414254422 | |
| 10 | Charly | Lownoise | 414254422 |
City
| Cityid | cityname |
|---|---|
| 1 | Toronto |
| 2 | Vancouver |
| 3 | Montreal |
Итак, скачайте файл testdb.sql
Если вы используете VS Code, то подключитесь к базе данных mysql, откройте новое окно для SQL запросов, скопируйте в него содержимое файла testdb.sql, и нажмите кнопку выполнения.
Если вы используете командную строку, то подключитесь к базе данных, скопируйте содержимое файла testdb.sql в буфер обмена и теперь кликните правой кнопкой в окне терминала. Команды из файла должны вставиться и выполниться в терминале. Выполняться все, кроме последней, вам скорей всего придется нажать Enter, чтобы завершить последнюю команду.
Теперь мы готовы к изучению SQL. Когда вы работаете с Excel таблицей, что вы можете с ней сделать? Искать данные в таблице поиском, добавлять новые строки, изменять существующие, удалять строки. То же самое можно делать и с базой данных, давайте начнем знакомиться с тем, как можно отображать содержимое таблицы и искать данные.
SELECT доступ к одной таблице
Команда SELECT достаточно простая, потому что она выглядит и звучит вполне логично и последовательно. Да, она может быть и сложной, потому что позволяет достаточно многое, и чтобы не пугать вас, я даже не буду пытаться показывать сейчас максимальную версию.
Начнем с самой простой версии:
SELECT колонки FROM базаданных.таблица
Большими буквами я выделил ключевые слова языка запросов SQL, а русскими маленькими буквами показано то, что мы должны заменить на реальные значения. Если перевести эту команду, то она будет звучать:
ВЫБРАТЬ колонки ИЗ база.данных.таблица
Если исправить склонение в последнем слове, то все будет звучать совсем ясно и понятно.
Колонки – это список имен колонок через запятую. Если вы хотите выбрать все колонки, то можно указать символ звездочки *.
У нас есть таблица City, давайте выберем из нее все записи и все колонки. Все колонки, значит нужно заменить слово «колонки» на символ звездочки, а на месте таблицы пишем city и в результате получаем
SELECT * FROM testdb.сity
Не забываем, что если выполнять из командной строки в mysql, то нужно в конце добавить точку запятой, но очень часто она не нужна, поэтому я в своих запросах буду опускать этот символ.
В результате мы должны увидеть следующее:
+--------+-----------+ | cityid | cityname | +--------+-----------+ | 1 | Toronto | | 2 | Vancouver | | 3 | Montreal | +--------+-----------+ 3 rows in set (0.00 sec)
Если мы пишем множество запросов, неужели каждый раз придется писать имя базы данных перед именем таблицы? Нет, это не обязательно. Если вы работаете с определенной базой, то можно как бы перейти в нее, или можно еще сказать выбрать ее. Для этого выполняем команду:
USE базаданных
Слово USE означает «использовать». То есть мы просим сервер использовать определенную базу для всех последующих запросов, пока снова не выберем другую. В нашем случае база данных это testdb, так что выполняем команду:
USE testdb
Теперь имя базы перед именем таблицы указывать не нужно, а значит запрос на получения всех колонок и всех строк из таблицы city может выглядеть теперь так:
SELECT * FROM сity
Это достаточно важный пункт, поэтому не забывайте его. В дальнейшем я буду писать запросы с учетом, что текущая база данных это testdb и поэтому перед именем таблицы указывать имя базы не буду.
В зависимости от настроек и используемой базы данных имена SQL может быть чувствительным к регистру и нет. Все чаще сталкиваюсь с тем, что MySQL по умолчанию ставится чувствительным к регистру, а значит имя таблицы нужно указать именно так, как это было при создании. Чтобы было проще, я все имена давал в нижем регистре.
Это значит, что следующие две команды могут завершиться ошибкой:
SELECT * FROM City SELECT * FROM CITY
Потому что называние города написано в неверном регистре.
Писать команды SQL большими буквами не обязательно. Вот их как раз можно писать в любом регистре и следующая команда завершиться удачно:
select * from city
Или даже эта
SeLeCt * FrOm city
Я не помню уже почему, то много лет назад, еще в 90-е годы я привык писать все слова, которые относятся к SQL большими буквами, чтобы они выделялись. На мой взгляд это читается проще, но вы не обязаны следовать этому же подходу.
Если в качестве колонок указать звездочку, то отображаются все поля в том порядке, в котором они создавались в базе данных. Мы можем перечислить имена через запятую:
SELECT cityid, cityname FROM city
В этом случае у нас есть возможность указать имена в любом порядке и указать сначала имя города, а потом идентификатор:
SELECT cityname, cityid FROM city
Или можно отобразить только имя города:
SELECT cityname FROM city
Настоятельно рекомендую повторять все, что мы здесь рассматриваем, потому что именно практика позволяет лучше запомнить материал.
Пробелы в именах объектов базы данных
У тебя может возникнуть вопрос – а что, а можно создавать имена таблица или колонок из нескольких слов и как тогда MySQL будет работать с пробелами? Создавать объекты с пробелами можно, но в этом случае имя нужно окружить специальными символами, которые зависят от базы данных, в MySQL это символ ` который находится слева от цифры 1 на большинстве клавиш.
Так что теоретически наш запрос может выглядеть так:
SELECT `Adress id`, Name FROM `Address Table`
Обратите внимание, что колонка Address id содержит пробел, поэтому вначале и в конце стоит символ `. У колонки Name нет пробелов, поэтому ничего добавлять не нужно. У имени таблицы так же есть пробел.
Если в имени объекта есть пробел, то ` является обязательным, если пробела нет, то можно поставить, а можно и опустить. Это значит, следующие запросы одинаково корректны:
SELECT `cityname` FROM `city`; SELECT cityname FROM `city`; SELECT `cityname` FROM city; SELECT cityname FROM city;
Все они корректны и все будут работать.
Хотя все примеры мы рассматриваем и тестируем под MySQL, почти все они будут работать и в других базах данных, но вот разделитель в разных базах может отличаться. В MS SQL Server это квадратные скобки:
SELECT [cityname] FROM [city];
Очень часто программисты стараются создавать таблицы и колонки без пробелов, поэтому не так часто можно увидеть запросы, в которых используются символы, которыми окружаются имена объектов.
Фильтрация выборки WHERE
Отлично, мы научились выбирать все данные из таблицы или определенные колонки, а теперь хорошо бы научиться еще и выбирать только определенные строки.
Формат команды выборки начинает усложняться и уже начинает выглядеть так:
ВЫБРАТЬ колонки ИЗ таблица ГДЕ фильтр
В качестве фильтра можно указывать имя колонки, по которой мы хотим фильтровать и значение, которое мы ищем. Например, если мы хотим найти все записи из нашего телефонного справочника, где фамилия владельца это Doe, то мы должны в фильтре указать:
lastname = 'Doe'
Здесь lastname – это имя колонки, поэтому его просто указываем без каких-то дополнений. Когда сервер будет читать этот запрос, то он увидит слово lastname, попробует найти это имя среди известных ему имен и без проблем сможет найти его, так что вопросов нет.
Фамилия – это строка, которая неизвестна MySQL. Для него это просто текст, и он не уверен, где начинается строка и заканчивается. Чтобы проще было определить начало и конец произвольных строк, мы должны помещать их в одинарные кавычки, как в примере выше.
Взглянем на следующий пример:
lastname = Mc Donald
Без одинарных кавычек MySQL не сможет понять этот фильтр, потому что он будет думать – нужно ли искать только по Mc или нужно искать по Mc Donald. А если все это объединить в одинарные кавычки, то фильтр станет корректным.
lastname = 'Mc Donald'
Если символы, которыми мы окружаем имена объектов являются НЕ обязательными, то одинарные кавычки являются обязательными и их опускать НЕЛЬЗЯ.
Итак, полный запрос, который все записи людей с фамилией Doe будет выглядеть так:
SELECT * FROM phone WHERE lastname = 'Doe';
В результате вы должны увидеть только две строки:
+---------+-----------+----------+---------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+----------+---------+--------+ | 1 | John | Doe | 4144122 | 1 | | 2 | Steve | Doe | 414124 | 1 | +---------+-----------+----------+---------+--------+ 2 rows in set (0.01 sec)
В большом городе может оказаться слишком много людей с фамилией Doe и когда мы ищем телефон, то скорей всего мы знаем, что нужного нам человека зовут Steve. Мы можем искать сразу по двум колонкам – имени и фамилии, просто объединив обе проверки с помощью слова AND:
SELECT * FROM phone WHERE lastname = 'Doe' AND firstname = 'Steve';
В ответ должна быть отображена только одна строка:
+---------+-----------+----------+--------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+----------+--------+--------+ | 2 | Steve | Doe | 414124 | 1 | +---------+-----------+----------+--------+--------+ 1 row in set (0.00 sec)
Взглянем по-другому – мы ищем по фамилии и хотим увидеть всех, чья фамилия Doe или Jackson. Просто возможно человек поменял фамилию, и мы не знаем, под какой из них остался зарегистрирован телефон. Нам нужна записи, где колонка lastname равна Doe или Jackson. Именно так мы и должны писать наш запрос, объединив две проверки с помощью ИЛИ, в английском это OR:
SELECT * FROM phone WHERE lastname = 'Doe' OR lastname = 'Jackson';
Результат
+---------+-----------+----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+----------+-----------+--------+ | 1 | John | Doe | 4144122 | 1 | | 2 | Steve | Doe | 414124 | 1 | | 6 | Michael | Jackson | 4142544 | 3 | | 8 | Andre | Jackson | 414254422 | 3 | +---------+-----------+----------+-----------+--------+ 4 rows in set (0.00 sec)
Ok, фамилии меняют после свадьбы и хотя у меня в таблице все имена мужские (я только сейчас сообразил и это сделано не специально), допустим, что мы знаем имя и это Andre. Возможно вы захотите написать запрос так:
SELECT * FROM phone WHERE lastname = 'Doe' OR lastname = 'Jackson' AND firstname = 'Andre';
Может показаться, что в результате должна быть только одна запись – Andre Jackson, но это не так, мы увидим три записи:
+---------+-----------+----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+----------+-----------+--------+ | 1 | John | Doe | 4144122 | 1 | | 2 | Steve | Doe | 414124 | 1 | | 8 | Andre | Jackson | 414254422 | 3 | +---------+-----------+----------+-----------+--------+ 3 rows in set (0.00 sec)
Дело в том, что наш запрос говорит, что мы хотим увидеть всех с фамилией Doe ИЛИ всех с фамилией Jackson и именем Andre. Чтобы проще было понять проблему я добавлю скобки, чтобы показать, как сгруппированы проверки:
lastname = 'Doe' OR (lastname = 'Jackson' AND firstname = 'Andre')
Как раз скобки мы и должны использовать, чтобы исправить проблему:
(lastname = 'Doe' OR lastname = 'Jackson') AND firstname = 'Andre'
Здесь мы уже говорим, что у человека может быть фамилия Doe или Jackson, но имя обязательно должно быть Andre.
Вот теперь мы увидим в результате только одну запись:
+---------+-----------+----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+----------+-----------+--------+ | 8 | Andre | Jackson | 414254422 | 3 | +---------+-----------+----------+-----------+--------+ 1 row in set (0.00 sec)
Для подобных задач в SQL есть более красивый синтаксис – использовать слово IN, что можно перевести как одно из. Формат такой:
Колонка in (значения, перечисленные через запятую)
То есть запрос, где мы искали одну из двух фамилий, можно переписать так:
SELECT * FROM phone WHERE lastname IN ('Doe', 'Jackson');
На мой взгляд это читается на много проще. Если прочитать это предложение по-русски, то все будет звучать так:
ВЫБРАТЬ все ИЗ телефоны ГДЕ фамилия ОДНА ИЗ ('Doe', 'Jackson')
На мой взгляд наглядно. Если добавить еще и условие с именем, то запрос будет выглядеть так:
SELECT *
FROM phone
WHERE lastname IN ('Doe', 'Jackson') AND firstname = 'Andre';
Тоже достаточно просто читается и не нужно заморачиваться со скобками, чтобы указать на приоритет, как мы объединяем ИЛИ и И.
Когда мы ищем по числам, то их оборачивать в одинарные кавычки не нужно. Допустим, что мы хотим найти запись в справочнике под номером 1. Именно под номером, а не первую под счету. Такой запрос может выглядеть так:
SELECT * FROM phone WHERE phoneid = 1
В случае с числами еще очень часто может потребоваться искать числа больше или меньше какого-то значения. Допустим, что нужно найти все записи, где id телефона меньше 5. В нашем случае это будет первые 4 строки. Как и в математике, так и в программировании можно использовать символы:
- < меньше
- > больше
- >= больше или равно
В нашем случае можно использовать < 5 как в следующем примере:
SELECT * FROM phone WHERE phoneid < 5
Результат:
+---------+-----------+-----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+-----------+-----------+--------+ | 1 | John | Doe | 4144122 | 1 | | 2 | Steve | Doe | 414124 | 1 | | 3 | Johnatan | Something | 4142947 | 2 | | 4 | Donald | Trump | 414251123 | 2 | +---------+-----------+-----------+-----------+--------+
Если мы хотим включить в выборку и строку с phoneid равных 5, то можно увеличить число до 6 или использовать меньше или равно <=
SELECT * FROM phone WHERE phoneid
Результат:
+---------+-----------+-----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+-----------+-----------+--------+ | 1 | John | Doe | 4144122 | 1 | | 2 | Steve | Doe | 414124 | 1 | | 3 | Johnatan | Something | 4142947 | 2 | | 4 | Donald | Trump | 414251123 | 2 | | 5 | Alice | Cooper | 414254234 | 2 | +---------+-----------+-----------+-----------+--------+
Усложняем задачу, ищем записи с id больше 3 и меньше 7. И снова мы можем воспользоваться AND, чтобы объединить две проверки:
SELECT * FROM phone WHERE phoneid > 3 and phoneid < 7
Чтобы проще читать и красивее все выглядело можно то же самое записать:
SELECT * FROM phone WHERE 3 < phoneid and phoneid < 7
Для этой задачи есть вариант решения проще, по крайней мере для некоторых – использовать between:
SELECT * FROM phone WHERE phoneid between 4 and 6;
Обратите внимание, что я использую числа 4 и 6, а не 3 и 7, потому что between включает граничные значение, это то же самое, что и:
SELECT * FROM phone WHERE phoneid >= 4 and phoneid
С точки зрения чтения это звучит лучше: выбрать все из телефонов, где id между 4 и 6. Звучит хорошо, но я почему-то почти не использую эту конструкцию. Мне больше нравится решать то же самое с помощью математических конструкций > или
Если работать со строками, то тут SQL предоставляет нам некую гибкость, мы можем искать по шаблону. Допустим, что мы хотим найти всех, у кого имя начинается с буквы J. Для этого используем новое слово LIKE. В английском это слово очень часто можно перевести как “нравиться” или “выглядеть как”, в зависимости от того, в качестве какой части речи использовать это слово. В данном случае это второй вариант. После этого мы можем использовать в качестве шаблона специальные символы:
% заменяет любое количество любых символов
_ заменяет один, но любой символ
Так как нам нужно найти всех, у кого первая бука J, а потом идет любое количество любых символов, то наш шаблон будет выглядеть как ‘J%’
SELECT * FROM phone WHERE firstname LIKE 'J%'
Результат:
+---------+-----------+-----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+-----------+-----------+--------+ | 1 | John | Doe | 4144122 | 1 | | 3 | Johnatan | Something | 4142947 | 2 | | 7 | John | Abama | 414254422 | 3 | +---------+-----------+-----------+-----------+--------+ 3 rows in set (0.00 sec)
А что если мы хотим найти любую фамилию, в которой содержится хотя бы одна буква A. Для этого можно указать % перед и после буквы A:
SELECT * FROM phone WHERE lastname LIKE '%a%'
Символ % означает любое количество любых символов, значит до и после A может быть что угодно и в любом количестве.
Отлично, но что, если мы не знаем только одну букву. Например, моя фамилия Флёнов, но очень часто приходиться писать Фленов только потому, что буква ё не поддерживается. Очень часто это проблема печати – в паспорте, в бумажном журнале или в книге.
SELECT * FROM phone WHERE lastname LIKE 'Фл_нов’
Подчеркивание означает один и только один символ. Недостаток именно этого запроса – он возвращает не только Фленов и Флёнов, но, возможно, и какие-то другие вариации, если они существуют Фланов, Флонов и т.д. Но возможно именно это нам и нужно.
Пустые поля NULL
Если выбрать все содержимое таблицы phone, то в последних двух строках будет не число, а какое странное NULL:
SELECT * FROM phone;
Результат
+---------+-----------+-----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+-----------+-----------+--------+ | 1 | John | Doe | 4144122 | 1 | | 2 | Steve | Doe | 414124 | 1 | | 3 | Johnatan | Something | 4142947 | 2 | | 4 | Donald | Trump | 414251123 | 2 | | 5 | Alice | Cooper | 414254234 | 2 | | 6 | Michael | Jackson | 4142544 | 3 | | 7 | John | Abama | 414254422 | 3 | | 8 | Andre | Jackson | 414254422 | 3 | | 9 | Mark | Oh | 414254422 | NULL | | 10 | Charly | Lownoise | 414254422 | NULL | +---------+-----------+-----------+-----------+--------+
NULL – это не строка и не число, это отсутствующее значение, то есть в этих двух строках в колонке cityid отсутствует. NULL можно перевести как ноль, но правильнее все же переводить это слово как “несуществующий” или “недействительный”.
Если поле с числом равно 0, то это число, просто оно нулевое. А если поле с числом равно NULL, то это уже не число и не ноль, это значит, что там вообще числа нет, черная дыра, пробоина, все что угодно, но только не число.
Я только что ляпнул новое понятие – поле. Это пересечение колонки и строки. Это то, куда мы записываем значение какой-то колонки/строки.
Особенно такие вещи могут путать в случае работы со строками. Некоторые программы для работы с запросами отображают пустую строку и отсутствующее значение как просто пустоту. Но это не так. Просто в обоих случаях отобразить нечего.
Для базы данных есть огромная разница – мы храним пустую строку или в поле нет вовсе значения, потому что это разные вещи. Если строка пустая, то это все же строка, просто у нее нет длины, но если значения нет, то строки не существует.
Скорость у машины может быть нулевая, если машина стоит или какое-то число, если машина едет. А если машины нет? Скорости тоже не будет в принципе, и мы не можем сказать, что скорость нулевая у машины, которой просто нет.
Работа с нулевыми полями отличается, потому что если попробовать выполнить запрос:
select * from phone where cityid = null;
то ничего не вернется. Казалось бы, мы же сравниваем число символом сравнения с NULL, но это не работает. Дело в том, что сравнивать с помощью равенства нельзя, вместо этого нужно использовать слово is:
select * from phone where cityid is null;
А если мы хотим найти все строки, в которых поле не пустое, а имеет какое-то значение. Тут нужно использовать is not:
select * from phone where cityid is not null;
На этом пока с основами получения данных закончим. В процессе рассмотрения дальнейшего материала мы познакомимся с еще более сложными запросами на практике.
Сортировка данных в запросах SQL
Когда мы выполняем запрос, то база данных может вернуть данные в любой последовательности, хотя чаще всего возвращает строки в том порядке, в котором они хранятся и чаще всего это будет совпадать со значением ключевой колонки, в нашем случае это phoneid.
Если вы хотите отсортировать по фамилии, то мы должны это явно сказать серверу. Для этого используется ORDER BY, который ставиться в конце запроса:
select * from phone order by lastname;
Результат
+---------+-----------+-----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+-----------+-----------+--------+ | 7 | John | Abama | 414254422 | 3 | | 5 | Alice | Cooper | 414254234 | 2 | | 1 | John | Doe | 4144122 | 1 | | 2 | Steve | Doe | 414124 | 1 | | 6 | Michael | Jackson | 4142544 | 3 | | 8 | Andre | Jackson | 414254422 | 3 | | 10 | Charly | Lownoise | 414254422 | NULL | | 9 | Mark | Oh | 414254422 | NULL | | 3 | Johnatan | Something | 4142947 | 2 | | 4 | Donald | Trump | 414251123 | 2 | +---------+-----------+-----------+-----------+--------+
Обратите внимание, что первая колонка теперь не отсортирована, а вот в lastname все значения возрастают начиная с буквы A в сторону Z. Нет, это происходит не всегда. Если мы не указали направление сортировки, то используется ASC, возрастание, то есть это то же самое, что написать:
select * from phone order by lastname asc;
А теперь посмотрите на колонку имени – оно не по возрастающей. Мы попросили отсортировать по фамилии, а когда фамилия одинаковая, то сервер имеет право вернуть данные в любом порядке и в данном случае ему удобно вывести в соответствии с ключевой колонкой phoneid. У Michael Jackson первая колонка равна 6 и это меньше 8, что мы видим у Andre.
Если вы хотите, чтобы в случае одинаковой фамилии данные сортировались по имени, то нужно указать обе колонки именно в таком порядке:
select * from phone order by lastname asc, firstname asc;
следующий запрос вернет то же самое, потому что не забываем, что ASC – возрастание это сортировка по умолчанию:
select * from phone order by lastname, firstname;
Теперь данные будут сначала отсортированы по фамилии и если фамилия одинакова, то по имени и в обоих случаях по возрастающей.
Можно сортировать по любому количеству колонок, если это реально принесет выгоду.
Если вы хотите отсортировать таблицу по фамилии, но в обратном порядке, то вместо ASC нужно указать DESC – убывание:
select * from phone order by lastname desc;
Результат:
+---------+-----------+-----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+-----------+-----------+--------+ | 4 | Donald | Trump | 414251123 | 2 | | 3 | Johnatan | Something | 4142947 | 2 | | 9 | Mark | Oh | 414254422 | NULL | | 10 | Charly | Lownoise | 414254422 | NULL | | 6 | Michael | Jackson | 4142544 | 3 | | 8 | Andre | Jackson | 414254422 | 3 | | 1 | John | Doe | 4144122 | 1 | | 2 | Steve | Doe | 414124 | 1 | | 5 | Alice | Cooper | 414254234 | 2 | | 7 | John | Abama | 414254422 | 3 | +---------+-----------+-----------+-----------+--------+
Добавить запись в таблицу
Мы разобрались с базовыми возможностями выборки данных и сегодня давайте посмотрим, как можно добавлять новые данные. Самый простой формат вставки данных в базу данных наверно
INSERT имя таблицы VALUES (значения колонок)
С именем таблицы вопросов нет. Если мы хотим вставить значения в таблицу телефонов, то пишем:
INSERT phone VALUES (значения колонок)
При такой команде в скобках нужно обязательно указать значения для каждой колонки. Строковые значения должны быть в одинарных кавычках, числовые могут быть в кавычках, но лучше все же без них. Это уже более глубокий вопрос, который мы скорей всего рассмотрим чуть позже.
Мы пока типы полей не рассматривали, но в некоторые колонки вставлять данные нельзя. К таким относится ключевое поле, если оно настроено как авто увеличиваемое. Некоторые базы позволяют изменять даже автоматически увеличиваемые поля, но даже в этом случае это не очень хорошо.
MySQL относится как раз к тем базам, которые могут позволить вставлять даже в автоматические поля, хотя повторюсь, я это не рекомендую. Первое поле в обеих таблицах, которые я создал для примеров этой работы как раз является автоматически увеличиваемым и ключом. Об этом подробнее во время создания таблиц, а сейчас просто для общего развития такой небольшой отступ от основной темы.
Итак, мы должны перечислить значения всех колонок, а их у нас в таблице 5, из которых первое и последние числа, значит их указываем без кавычек и указываем именно число. Первое поле ключ и его значение указывать не обязательно, но если вы сделаете это, то обязательно укажите уникальное число, которого до сих пор не было. Я создал таблицу с 10 строками, и первая колонка содержит значения от 1 до 10. Следующее значение 11, поэтому можно указать его.
Итак, запрос на вставку записи с ID равным 11 будет выглядеть так:
INSERT phone VALUES (11, 'Anna', 'Koko', '41213213', 1);
Как я уже сказал, я сделал первую колонку автоматически увеличиваемой, поэтому значение для нее указывать не обязательно. Если вы не хотите самостоятельно искать следующее свободное число, просто не указывайте его, а вместо числа можно использовать NULL, как мы помним это как бы отсутствующее значение:
INSERT phone VALUES (null, 'Elen', 'Rokoko', '41213183', 1);
Если мы показываем, что для первой колонки передано NULL, то есть мы не хотим указывать значение, то для автоматически увеличиваемых полей сервер сам найдет следующее свободное и будет использовать его. В нашем случае должно быть 12. Проверим:
mysql> select * from phone;
Результат:
+---------+-----------+-----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+-----------+-----------+--------+ | 1 | John | Doe | 4144122 | 1 | | 2 | Steve | Doe | 414124 | 1 | | 3 | Johnatan | Something | 4142947 | 2 | | 4 | Donald | Trump | 414251123 | 2 | | 5 | Alice | Cooper | 414254234 | 2 | | 6 | Michael | Jackson | 4142544 | 3 | | 7 | John | Abama | 414254422 | 3 | | 8 | Andre | Jackson | 414254422 | 3 | | 9 | Mark | Oh | 414254422 | NULL | | 10 | Charly | Lownoise | 414254422 | NULL | | 11 | Anna | Koko | 41213213 | 1 | | 12 | Elen | Rokoko | 41213183 | 1 | +---------+-----------+-----------+-----------+--------+ 12 rows in set (0.00 sec)
Поля таблиц могут быть настроены так, что они будут обязательными и нет. Я для этого примера намеренно сделал все поля необязательными, а значит мы можем просто передать вместо значений для каждой колонки только NULL:
INSERT phone VALUES (null, null, null, null, null);
Проверим результат:
SELECT * FROM phone WHERE phoneid = 13;
И вот что, что мы получили
+---------+-----------+----------+-------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+----------+-------+--------+ | 13 | NULL | NULL | NULL | NULL | +---------+-----------+----------+-------+--------+ 1 row in set (0.04 sec)
Указывать отсутствующее значение (NULL) для всех колонок, для которых мы не хотим указывать реальное значение – странно и глупо. Вместо этого после имени таблицы в скобках можно указать имена колонок, значения которых мы хотим указать:
INSERT phone (phoneid, phone, firstname) VALUES (14, ‘4184719’, ‘Mary’);
В этом запросе после имени таблицы в скобках указаны имена колонок phoneid, phone и firstname. Я намеренно указал имена не в том порядке, как они созданы в таблице, ведь реально имя находиться в таблице вторым, а здесь третьим.
Именно в таком же порядке должны быть предоставлены значения в круглых скобках после слова VALUES. Как видите значения тоже идут в таком же порядке – ID, номер телефона и только потом имя.
Таким образом мы можем опускать любые необязательные поля, но только необязательные. Если колонка обязательно должна иметь значение, то мы обязаны указать ее в операторе INSERT и предоставить значение.
У нас необязательных значений нет, так что теоретически мы можем выполнить такую команду:
INSERT phone () VALUES ();
Будет вставлена новая строка, у которой будут заданы только колонки, для которых есть значения по умолчанию или автоматически увеличиваемые. Эта команда идентична уже той, что мы выполняли:
INSERT phone VALUES (null, null, null, null, null);
Она выполниться успешно только если в таблице нет колонок с обязательными полями без значения по умолчанию.
SQL — Обновление данных
Бывают такие случаи, когда данные вставил в таблицу и они больше никогда не меняются. Но в реальной жизни нередко данные подвержены изменениям и у нас должна быть возможность сделать это.
Минимальная команда изменения данных:
UPDATE таблица SET колонка1 = значение, колонка2 = значение . . . WHERE фильтр
В секции WHERE мы можем писать такие же условия, как мы делали и при SELECT. В остальном в принципе все понятно.
Давайте посмотрим на содержимое строки с ID = 14:
SELECT * FROM phone WHERE phoneid = 14;
Результат:
+---------+-----------+----------+---------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+----------+---------+--------+ | 14 | Mary | NULL | 4184719 | NULL | +---------+-----------+----------+---------+--------+ 1 row in set (0.00 sec)
Здесь у нас Мэри, но у нее не было указано фамилии. Давайте обновим эту строку и укажим фамилию.
UPDATE phone SET lastname = 'Poppins' WHERE
Стоп, что указать в качестве фильтра WHERE? Можно указать имя, но если в базе данных будет несколько записей людей с именем Mary, то мы обновим их все. Не думаю, что мы этого хотим.
По номеру телефона. . . Возможно это сработает, если номер действительно уникальный.
Если у нас есть колонка с уникальными значениями, то лучше использовать ее, тогда мы точно будем знать, что обновлена именно нужная нам запись. Именно поэтому создают в базах данных ключевые поля, как я это сделал с phoneid и самый простой способ добиться уникальности – сделать колонку автоматически увеличиваемой или сохранять в ней что-то типа уникального GUID.
Некоторые базы данных даже не позволяют обновлять данные в таблице, если в ней нет уникальной колонки, потому что база данных в таком случае не может гарантировать, что будет обновлена или удалена именно нужная колонка.
Если забыть про наличие phoneid, которую я заведомо и продуманно создал, то мы можем вставить в таблицу две записи с абсолютно одинаковыми значениями. Допустим, что у нас есть такая таблица:
+-----------+-----------+-----------+ | firstname | lastname | cityid | +-----------+-----------+-----------+ | Mary | NULL | 4184719 | | Mary | NULL | 4184719 | | NULL | NULL | NULL | +-----------+-----------+-----------+
Как мы можем обновить вторую запись Mary, без уникального кода id? А первую? Да все равно какую из них! Записи идентичны и с обновлением проблема. Самый простой способ – удалить обе записи и вставить новые. Да, это решит проблему, но все же.
Именно поэтому некоторые не разрешают изменять данные, если нет первичного ключа, который гарантирует уникальность данных, потому что хотя бы этот ключ и будет различать записи.
С другой стороны, при наличии первичного уникального ключа, которым является personid, желательно использовать его:
UPDATE phone SET lastname = 'Poppins' WHERE personid = 14
Если ты потерялся и все еще не понимаешь, что такое первичный ключ, мы еще будем говорить на тему ключей, когда будем создавать таблицы. Я помню мне тоже на первом этапе знакомства с базами данных было не совсем понятно было что это такое, зачем это нужно. Пока просто помните, что первичный ключ, это колонка (может и не одна), которая гарантирует уникальность каждой строки.
Мы можем обновлять не одну, а сразу несколько колонок, указав их значения через запятую. Давайте изменим сразу фамилию и телефон:
UPDATE phone
SET lastname = 'Poppins',
phone = '48171738'
WHERE personid = 14
Удаление данных из базы данных
Самая простая тема – это удаление данных. Самый простой вариант удалить данные – выполнить оператор:
DELETE FROM имятаблицы
Что удалиться? Все!
Если мы не хотим удалять все, то мы можем добавить уже знакомую нам секцию WHERE:
DELETE FROM phone WHERE firstname = 'Mary'
Здесь мы удаляем все записи, где телефон принадлежит человеку с именем Mary. Если их больше одного, то будут удалены все.
Если нужно удалить только конкретную запись, то мы снова можем использовать первичный ключ:
DELETE FROM phone WHERE phoneid = 14
В этом примере мы удалим запись, где id телефона равен 14.
If you are avoiding stored procedures like the plague, or are unable to do a mysql_dump due to permissions, or running into other various reasons.
I would suggest a three-step approach like this:
1) Where this query builds a bunch of queries as a result set.
# =================
# VAR/CHAR SEARCH
# =================
# BE ADVISED USE ANY OF THESE WITH CAUTION
# DON'T RUN ON YOUR PRODUCTION SERVER
# ** USE AN ALTERNATE BACKUP **
SELECT
CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME,
' WHERE ', A.COLUMN_NAME, ' LIKE '%stuff%';')
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE
A.TABLE_SCHEMA != 'mysql'
AND A.TABLE_SCHEMA != 'innodb'
AND A.TABLE_SCHEMA != 'performance_schema'
AND A.TABLE_SCHEMA != 'information_schema'
AND
(
A.DATA_TYPE LIKE '%text%'
OR
A.DATA_TYPE LIKE '%char%'
)
;
.
# =================
# NUMBER SEARCH
# =================
# BE ADVISED USE WITH CAUTION
SELECT
CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME,
' WHERE ', A.COLUMN_NAME, ' IN ('%1234567890%');')
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE
A.TABLE_SCHEMA != 'mysql'
AND A.TABLE_SCHEMA != 'innodb'
AND A.TABLE_SCHEMA != 'performance_schema'
AND A.TABLE_SCHEMA != 'information_schema'
AND A.DATA_TYPE IN ('bigint','int','smallint','tinyint','decimal','double')
;
.
# =================
# BLOB SEARCH
# =================
# BE ADVISED THIS IS CAN END HORRIFICALLY IF YOU DONT KNOW WHAT YOU ARE DOING
# YOU SHOULD KNOW IF YOU HAVE FULL TEXT INDEX ON OR NOT
# MISUSE AND YOU COULD CRASH A LARGE SERVER
SELECT
CONCAT('SELECT CONVERT(',A.COLUMN_NAME, ' USING utf8) FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME,
' WHERE CONVERT(',A.COLUMN_NAME, ' USING utf8) IN ('%someText%');')
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE
A.TABLE_SCHEMA != 'mysql'
AND A.TABLE_SCHEMA != 'innodb'
AND A.TABLE_SCHEMA != 'performance_schema'
AND A.TABLE_SCHEMA != 'information_schema'
AND A.DATA_TYPE LIKE '%blob%'
;
Results should look like this:
2) You can then just Right Click and use the Copy Row (tab-separated)
3) Paste results in a new query window and run to your heart’s content.
Detail: I exclude system schema’s that you may not usually see in your workbench unless you have the option Show Metadata and Internal Schemas checked.
I did this to provide a quick way to ANALYZE an entire HOST or DB if needed or to run OPTIMIZE statements to support performance improvements.
I’m sure there are different ways you may go about doing this but here’s what works for me:
-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO ANALYZE THEM
SELECT CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';
-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO OPTIMIZE THEM
SELECT CONCAT('OPTIMIZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';
Tested On MySQL Version: 5.6.23
WARNING: DO NOT RUN THIS IF:
- You are concerned with causing Table-locks (keep an eye on your client-connections)
You are unsure about what you are doing.
You are trying to anger you DBA. (you may have people at your desk with the quickness.)
Cheers, Jay ;-]
Привет, Хабр! Наши друзья из Softpoint подготовили интересную статью про Microsoft SQL Server. В ней разбирается два практических примера использования полнотекстового поиска:
- Поиск по «бесконечным» строкам (напр., Комментарии) в противовес обычному поиску через LIKE;
- Поиск по номерам документов с префиксами. Там, где обычно полнотекстовый поиск применять нельзя: ему мешают постоянные префиксы. Разбирается 2 подхода: предварительная обработка номера документа и добавление собственной библиотеки-word breaker’а.
Присоединяйтесь!
Передаю слово автору
Эффективный поиск в гигабайтах накопленных данных — своеобразный «священный Грааль» учетных систем. Все хотят его найти и обрести бессмертную славу, но в процессе поисков раз за разом выясняется, что единственного чудодейственного решения нет.
Ситуация осложняется тем, что пользователи обычно хотят искать по вхождению подстроки — где-то выясняется, что нужный номер договора «закопан» посередине комментария; где-то оператор не помнит точно фамилию клиента, зато запомнил, что зовут его «Алексей Евграфович»; где-то просто нужно опустить повторяющуюся форму собственности ПОЮБЛ и искать сразу по названию организации. Для классических реляционных СУБД такой поиск — очень плохая новость. Чаще всего такой поиск по подстроке сводится к методичному пролистыванию каждой строки таблицы. Не самая эффективная стратегия, особенно если размер таблицы дорастает до нескольких десятков гигабайт.
В поисках альтернативы часто вспоминаю про «полнотекстовый поиск». Радость от найденного решения обычно быстро проходит после беглого обзора существующей практики. Быстро выясняется, что, по народному мнению, полнотекстовый поиск:
- Сложно настраивается
- Медленно обновляется
- Вешает систему при обновлении
- Имеет какой-то
дурацкийнепривычный синтаксис - Не находит то, что спрашивают
Набор мифов можно продолжать долго, но еще Платон учил нас быть скептиками и не принимать слепо чужое мнение на веру. Давайте разберем, так страшен ли черт, как его малюют?
И, пока мы глубоко не погрузились в исследование, сразу договоримся о важном условии. Механизм полнотекстового поиска умеет гораздо больше, чем обычный поиск по строке. Например, можно определить словарь синонимов и по слову «контакт» находить «телефон». Или искать слова без учета формы и окончаний. Эти опции могут оказаться очень полезными для пользователей, но в этой статье мы рассматриваем полнотекстовый поиск только как альтернативу классическому поиску по строке. То есть, искать будем только ту подстроку, которая будет задана в строке поиска, без учета синонимов, без приведения слов к «нормальной» форме и прочей магии.
Как работает полнотекстовый поиск MS SQL
Функционал полнотекстового поиска в MS SQL частично вынесен из основной службы СУБД (ближе к концу статьи мы увидим, почему это может быть крайне полезно). Для поиска формируется особенный индекс со своей структурой, непохожей на привычные сбалансированные деревья.
Важно, что для создания индекса полнотекстового поиска необходимо, чтобы в ключевой таблице существовал уникальный индекс, состоящий всего из одной колонки — именно его полнотекстовый поиск будет использовать для идентификации строк таблицы. Часто у таблицы уже есть такой индекс по Primary Key, но иногда его придется создавать дополнительно.
Заполнение индекса полнотекстового поиска происходит асинхронно и вне транзакции. После изменения строки таблицы она ставится в очередь на обработку. Процесс обновления индекса получает из строки таблицы (row) все строковые значения, «подписанные» на индекс, и разбивает их на отдельные слова. После этого слова могут быть приведены к некоей «стандартной» форме (например, без окончаний), чтобы проще было искать по формам слова. Выкидываются «стоп-слова» (предлоги, артикли и другие слова, не несущие смысла). Оставшиеся соответствия «слово-ссылка на строку» записываются в индекс полнотекстового поиска.
Получается, каждая колонка таблицы, входящая в индекс, проходит такой конвейер:
Длинная строка -> wordbreaker -> набор частей (слов) -> stemmer -> нормализованные слова -> [опционально] исключение стоп-слов -> запись в индекс
Как было сказано, процесс обновления индекса асинхронный. Из этого следует:
- Обновление не блокирует действия пользователя
- Обновление ждет завершения транзакции изменения строки и начинает применять изменения не раньше, чем случится commit
- Изменения в полнотекстовом индексе применяются с некоторой задержкой относительно основной транзакции. То есть, между добавлением строки и моментом, когда ее можно будет найти, будет задержка, зависящая от длины очереди обновления индекса
- Число элементов, содержащихся в индексе, можно мониторить запросом:
SELECT
cat.name,
FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') AS [ItemCount]
FROM sys.fulltext_catalogs AS catПрактические испытания. Поиск физ. лиц по ФИО
Наполнение таблицы данными
Для экспериментов создадим новую пустую базу с одной таблицей, где будут храниться «контрагенты». Внутри поля «описание» будет строка с названием договора, где будет упоминаться ФИО контрагента. Как-то так:
«Договор с Боровик Демьян Емельянович»
Или так:
«Дог. с Боровик-Романов Анатолий Авдеевич»
Да, от такой «архитектуры» хочется сразу застрелиться, но, к сожалению, такое применение «комментариев» или «описаний» нередко среди бизнес-пользователей.
Дополнительно, добавим несколько полей «для веса»: если в таблице будет только 2 колонки, простое сканирование прочитает ее за мгновения. Нам нужно «раздуть» таблицу, чтобы скан оказался долгим. Это же приближает нас и к реальным бизнес-кейсам: мы ведь в таблице храним не только «описание», но и много другой [бес]полезной информации.
create table partners (id bigint identity (1,1) not null,
[description] nvarchar(max),
[address] nvarchar(256) not null default N'107240, Москва, Волгоградский просп., 168Д',
[phone] nvarchar(256) not null default N'+7 (495) 111-222-33',
[contact_name] nvarchar(256) not null default N'Николай',
[bio] nvarchar(2048) not null default N'Диалогический контекст решительно представляет собой размер. Казуистика, следовательно, заполняет метаязык. Можно предположить, что обсценная идиома параллельна. Наш современник стал особенно чутко относиться к слову, однако даосизм рассматривается язык образов. Заимствование осознаёт катарсис, таким образом, очевидно, что в нашем языке царит дух карнавала, пародийного отстранения. Отношение к современности вязко. Моцзы, Сюнъцзы и другие считали, что освобождение кумулятивно. Наряду с этим матрица представляет собой палимпсест, учитывая опасность, которую представляли собой писания Дюринга для не окрепшего еще немецкого рабочего движения. Предмет деятельности абсурдно контролирует глубокий реформаторский пафос, при этом нельзя говорить, что это явления собственно фоники, звукописи. Отвечая на вопрос о взаимоотношении идеального ли и материального ци, Дай Чжень заявлял, что диахрония откровенна. Закон внешнего мира осмысляет культурный голос персонажа. Гений ясен не всем.')
-- пользуясь случаем, передаю привет сервису Яндекс.Реферат. Спасибо ему за увлекательную биографию наших контрагентовСледующий вопрос — где взять столько уникальных фамилий, имен и отчеств? Я, по старой привычке, поступил как нормальный российский студент, т.е. пошёл в Википедию:
- Имена взял со страницы Категория: Русские мужские имена
- Отчества вручную переписал из имен, изменив окончания
- С фамилиями оказалось немного сложнее. В конце концов, нашлась категория «Однофамильцы». Немного шаманства с Python и в отдельной таблице оказалось 46,5 тыс. фамилий. (скрипт для скачивания фамилий доступен здесь)
Конечно, среди фамилий попадались странные варианты, но для целей исследования это было вполне допустимо.
Я написал sql-скрипт, который к каждой фамилии прикрепляет случайное число имен и отчеств. 5 минут ожидания и в отдельной таблице было уже 4,5 млн. комбинаций. Неплохо! На каждую фамилию приходилось от 20 до 231 комбинации имя+отчество, в среднем получилось по 97 комбинаций. Распределение по именам и отчествам оказалось немного смещённым «влево», но придумывать более взвешенный алгоритм показалось избыточным.
Данные подготовлены, можно начинать наши эксперименты.
Настройка полнотекстового поиска
Создадим полнотекстовый индекс на уровне MS SQL. Для начала нам нужно создать хранилище для этого индекса — полнотекстовый каталог.
USE [like_vs_fulltext]
GO
CREATE FULLTEXT CATALOG [basic_ftc] WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION [dbo]
GOКаталог есть, пытаемся добавить полнотекстовый индекс для нашей таблицы… и ничего не получается.
Как я говорил, для полнотектстового индекса нужен обычный индекс с одной уникальной колонкой. Вспоминаем, что нужное поле у нас уже есть – уникальный идентификатор id. Создадим по нему уникальный кластерный индекс (хотя хватило бы и некластерного):
create unique clustered index ndx1 on partners (id)После создания нового индекса мы наконец-то можем добавить индекс полнотекстового поиска. Подождем несколько минут, пока индекс заполнится (помним, что он обновляется асинхронно!). Можно переходить к тестам.
Тестирование
Начнем с самого простого сценария, приближенного к реальному применению поиска. Смоделируем «просмотр списка» — выборку окна из 45 строк с отбором по маске поиска. Выполняем запрос с новым полнотекстовым индексом, засекаем время — 0 сек — отлично!
Теперь старый, проверенный поиск через «лайк». На формирование результата ушло 3 секунды. Не так уж и плохо, тотального разгрома не получилось. Может тогда и нет смысла сложно настраивать полнотекстовый поиск — всё и так отлично работает?
На самом деле, мы упустили одну важную деталь: запрос выполнялся без сортировки. Во-первых, такой запрос в паре с «выбором первых N записей» возвращает негарантированный результат. Каждый запуск может возвращать случайные N записей и нет никакой гарантии, что два последовательных запуска дадут одинаковый набор данных. Во-вторых, если мы говорим про «просмотр списка скользящим окном» — обычно это самое «окно» отсортировано по какой-либо колонке, например, по имени. Оператору ведь нужно знать, что он получит, когда перейдет к следующему «окну».
Корректируем эксперимент. Добавляем сортировку, скажем, по номеру телефона:
Полнотекстовый поиск побеждает с оглушительным счетом: 0 секунд против 172 секунд!
Если посмотреть на планы запросов, становится понятно, почему так выходит. Из-за добавления упорядочения в текст запроса, при выполнении появилась операция сортировки. Это так называемая «блокирующая» операция, которая не может завершить запрос, пока не получит весь объем данных для сортировки. Мы не можем забрать первые попавшиеся 45 записей, нам надо отсортировать весь набор данных.
И вот на этапе получения данных для сортировки происходит драматическая разница. Поиску с «like» приходится просматривать всю доступную таблицу. На это и уходит 172 секунды. А вот у полнотекстового поиска есть своя оптимизированная структура, которая сразу возвращает ссылки на все нужные записи.
Но должна же быть и ложка дёгтя? Есть такая. Как было сказано в начале, полнотекстовый поиск может искать только от начала слова. И если мы захотим найти «Ивана Поддубного» по подстроке «*дуб*», полнотекстовый поиск не покажет ничего полезного.
К счастью, для поиска по ФИО это не самый востребованный сценарий.
Поиск документа по номеру
Попробуем что-нибудь посложнее. Второй популярный вариант использования поиска – нахождение документа по части его номера. Причем, часто номер документа состоит из двух частей: буквенного префикса и собственно номера, содержащего лидирующие нули.
Никаких пробелов или служебных символов между этими частями нет. При этом, искать по полному номеру чудовищно неудобно – приходится помнить, сколько лидирующих нулей после префикса должно стоять перед началом значащей части. Получается, что полнотекстовый поиск «из коробки» просто бесполезен в таком сценарии. Попробуем это исправить.
Для теста я создал новую таблицу document, в которую добавил 13,5 млн. записей с уникальными номерами вида «ОРГ». Нумерация шла по порядку, все номера начинались с «ОРГ». Можно начинать.
Предварительное разбиение номера
Полнотекстовый поиск умеет эффективно искать слова. Ну так давайте ему поможем и заранее разобьем «неудобный» номер на удобные слова. План действий такой:
- Добавим в исходную таблицу дополнительную колонку, где будет храниться специально преобразованный номер
- Добавим триггер, который при изменении номера будет разбивать его на несколько мелких частей, разделенных пробелом
- Полнотекстовый поиск уже умеет разбивать строку на части по пробелам, так что он без проблем проиндексирует наш модифицированный номер
Посмотрим, как это будет работать.
Добавим дополнительную колонку в таблицу.
alter table document add number_parts nvarchar(128) not null default ''Триггер, заполняющий новую колонку, можно написать «в лоб», игнорируя возможные дубли (сколько повторяющихся троек в номере «МНГ0000012»?) А можно добавить немного XML-магии и записывать только уникальные части. Первая реализация будет быстрее, вторая – даст более компактный результат. По сути, выбор стоит между скоростью записи и скоростью чтения, выбирайте, что в вашей ситуации важнее. Сейчас же просто пройдемся скриптом, который обработает уже существующие номера.
Добавляем полнотекстовый индекс
create fulltext index on document (number_parts)
key index ndx1
with change_tracking = AutoИ проверяем результат. Эксперимент тот же — моделирование «оконной» выборки из списка документов. Не повторяем предыдущих ошибок и сразу выполняем запрос с сортировкой, в данном случае по дате.
Работает! Теперь попробуем номер подлиннее:
И тут случается осечка. Длина поисковой строки больше, чем длина сохраненных «слов». По сути, в базе поиска просто нет ни одной строки в 4 символа, поэтому он честно возвращает пустой результат. Придётся бить поисковую строку на части:
Другое дело! У нас снова работает быстрый поиск. Да, он накладывает свои накладные расходы на обслуживание, но результат оказывается в сотни раз быстрее классического поиска. Отмечаем попытку засчитанной, но попробуем как-то упростить сопровождение – в следующем разделе.
Разобьем на слова по-своему!
В самом деле, кто сказал, что слова должны разделяться пробелами? Может быть, я хочу, чтобы между словами были нули! (и, если можно, префикс чтобы тоже как-то игнорировался и не мешался под ногами). В общем-то, ничего невозможного в этом нет. Вспомним схему работы полнотекстового поиска из начала статьи – за разбиение на слова отвечает отдельный компонент, wordbreaker, и, по счастью, Microsoft позволяет реализовать свой собственный «разбиватель слов».
И вот тут начинается интересное. Wordbreaker – это отдельная dll, которая подключается к движку полнотекстового поиска. В официальной документации сказано, что сделать эту библиотеку очень просто – достаточно реализовать интерфейс IWordBreaker. И приведена пара коротких листингов инициализации на C++. Очень удачно, я как раз нашел подходящий самоучитель!
(источник)
Если серьезно, документации по созданию собственного worbreaker’а в интернете исчезающе мало. Ещё меньше примеров и шаблонов. Но я все-таки нашёл проект доброго человека, который написал на C++ реализацию, разбивающую слова не по разделителям, а просто тройками (да, прямо как в предыдущем разделе!) Более того, в папке проекта уже есть заботливо скомпилированный бинарник, который надо просто подключить к движку поиска.
Просто подключить… На самом деле не очень просто. Пройдёмся по шагам:
Необходимо скопировать библиотеку в папку с SQL Server:
Зарегистрировать новый «язык» в полнотекстовом поиске
exec master.dbo.xp_instance_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWAREMicrosoftMSSQLSERVERMSSearchCLSID{d225281a-7ca9-4a46-ae7d-c63a9d4815d4}', 'DefaultData', 'REG_SZ', 'sqlngram.dll'
exec master.dbo.xp_instance_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWAREMicrosoftMSSQLSERVERMSSearchCLSID{0a275611-aa4d-4b39-8290-4baf77703f55}', 'DefaultData', 'REG_SZ', 'sqlngram.dll'
exec master.dbo.xp_instance_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWAREMicrosoftMSSQLSERVERMSSearchLanguagengram', 'Locale', 'REG_DWORD', 1
exec master.dbo.xp_instance_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWAREMicrosoftMSSQLSERVERMSSearchLanguagengram', 'WBreakerClass', 'REG_SZ', '{d225281a-7ca9-4a46-ae7d-c63a9d4815d4}'
exec master.dbo.xp_instance_regwrite 'HKEY_LOCAL_MACHINE', 'SOFTWAREMicrosoftMSSQLSERVERMSSearchLanguagengram', 'StemmerClass', 'REG_SZ', '{0a275611-aa4d-4b39-8290-4baf77703f55}'
exec sp_fulltext_service 'verify_signature' , 0;
exec sp_fulltext_service 'update_languages';
exec sp_fulltext_service 'restart_all_fdhosts';
exec sp_help_fulltext_system_components 'wordbreaker';Вручную отредактировать несколько ключей в реестре (автор собирался автоматизировать процесс, но с 2016 года новостей нет. Впрочем, это изначально был «пример реализации», спасибо и на этом)
Подробно шаги описаны на странице проекта.
Готово. Удалим старый полнотекстовый индекс, потому что двух полнотекстовых индексов для одной таблицы быть не может. Создадим новый и проиндексируем наши номера документов. В качестве ключевой колонки указываем сами номера, никаких суррогатных предразбитых колонок больше не нужно. Обязательно указываем «язык номер 1», чтобы использовался именно свежеустановленный wordbreaker.
drop fulltext index on document
go
create fulltext index on document (number Language 1)
key index ndx1
with change_tracking = AutoПроверяем?
Работает! Работает так же быстро, как все примеры, рассмотренные выше.
Проверим по длинной строке, на которой споткнулся предыдущий вариант:
Поиск работает прозрачно для пользователя и программиста. Wordbreaker самостоятельно разбивает поисковую строку на части и находит нужный результат.
Получается, теперь нам не нужны дополнительные колонки и триггеры, то есть решение оказывается проще (читай: надёжнее), чем наша предыдущая попытка. Ну в плане поддержки такая реализация оказывается проще и прозрачнее, меньше вероятность возникновения ошибок.
Так, стоп, я сказал «надёжнее»? Мы ведь только что подключили какую-то стороннюю библиотеку к нашей СУБД! А что будет, если она упадет? Ещё ненароком утянет за собой всю службу базы данных!
Тут нужно вспомнить, как в начале статьи я упоминал про службу полнотекстового поиска, отделённую от основного процесса СУБД. Именно здесь становится понятно, почему это важно. Библиотека подключается к службе полнотекстового индексирования, которая может работать с пониженными правами. И, что более важно, если сторонние компоненты упадут, упадет только служба индексирования. Поиск на время остановится (но он и так асинхронный), а ядро СУБД продолжит работать, как будто ничего не случилось.
Подытожив. Добавление собственного wordbreaker’а может оказаться довольно сложной задачей. Но при игре «в долгую» эти усилия окупаются большей гибкостью и простотой обслуживания. Выбор, как обычно, за вами.
Зачем всё это нужно?
Пытливый читатель наверняка уже не раз задался вопросом: «всё это здорово, но как мне использовать эти возможности, если я не могу изменить поисковые запросы из моего приложения?». Резонный вопрос. Подключение полнотектстового поиска MS SQL требует изменения синтаксиса запросов и часто это просто невозможно в имеющейся архитектуре.
Можно попытаться обмануть приложение, «подсунув» вместо обычной таблицы одноимённую table-valued function, которая уже будет выполнять поиск так, как нам хочется. Можно попытаться привязать поиск как некий внешний источник данных. Есть ещё одно решение – Softpoint Data Cluster – специальная служба, которая устанавливается «впроброс» между исходным приложением и службой SQL Server, слушает проходящий трафик и может менять запросы «на лету» по специальным правилам. С помощью таких правил мы можем находить обычные запросы с LIKE и переделывать их на CONTAINS с обращением к полнотекстовому поиску.
К чему такие сложности? Всё-таки скорость поиска подкупает. В высоконагруженной системе, где операторы часто ищут записи по миллионным таблицам, скорость отклика имеет решающее значение. Экономия времени на самой частой операции выливается в десятки дополнительных обработанных заявок, а это живые деньги, которым рад любой бизнес. В конце концов, несколько дней или даже недель на изучение и внедрение технологии окупятся возросшей эффективностью операторов.
Все скрипты, упоминаемые в статье, доступны в репозитории github.com/frrrost/mssql_fulltext
Об авторе
Александр Денисов — Аналитик производительности баз MS SQL Server. Последние 6 лет в составе команды Softpoint помогаю находить узкие места в чужих запросах и выжимать максимум из БД клиентов.