Полнотекстовый поиск в Sqlite

Для полнотекстового поиска по в SQLite используются модули FTS3, FTS4 и FST5 — это модули виртуальных таблиц SQLite, которые позволяют пользователям выполнять полнотекстовый поиск на множестве документов.

Создание виртуальной таблицы для полнотекстового поиска

Пример создания виртуальной таблицы использующей FST5:

CREATE VIRTUAL TABLE table_name USING FTS5(column1, column2...);

Типы столбцов в выражение USING указывать не надо, поскольку ни FTS, ни ядро SQLite никак не будет использовать эти объявления типов.

Каждая таблица FTS так же имеет столбец «rowid». Этот rowid для таблиц FTS ведет себя практически так же, как столбец rowid в обычной таблице SQLite за исключением того, что значения, сохраняемые в rowid таблицы FTS, остаются неизменными при выполнении над базой данных команды VACUUM. В таблицах FTS этот столбец доступен также по алиасу «docid» вместе с обычными алиасами «rowid», «oid» и «_oid_». Попытка вставить строку с уже существующим в таблице «docid» или изменить «docid» строки на «docid» другой существующей строки является ошибкой, как и в случае с обычной таблицей SQLite.

Пример изменения строки с rowid

UPDATE pages SET title = 'Скачать SQLite' WHERE rowid = 54;

Заполнение таблиц FTS

Таблицы FTS заполняются и редактируются с помощью запросов INSERT, UPDATE и DELETE так же как и обычные таблицы SQLite.

Пример вставки данных:

INSERT INTO posts(title, body) VALUES('Learn SQlite FTS5', 'This tutorial teaches you how to perform full-text search in SQLite using FTS5'), ('Advanced SQlite Full-text Search', 'Show you some advanced techniques in SQLite full-text searching'), ('SQLite Tutorial', 'Help you learn SQLite quickly and effectively');

Выборка данных

Выбираются данные обычным оператором SELECT.

Таблицы FTS могут эффективно запрашиваться с использованием выражений SELECT двух различных форм (стратегий):

• Запрос по rowid. Если выражение WHERE запроса SELECT содержит подвыражение в форме «rowid = ?», где ? является каким-то условием SQL, то FTS может быстро отыскать требуемую строку с использованием индекса, эквивалентного индексу INTEGER PRIMARY KEY в SQLite.
• Полнотекстовый поиск. Если выражение WHERE запроса SELECT содержит подвыражение в форме «<column> MATCH ?», то FTS будет использовать заранее созданный полнотекстовый индекс для нахождения документов, которые удовлетворяют строке полнотекстового запроса, находящейся в правой части выражения MATCH.

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

Пример выборки данных:

SELECT * FROM posts WHERE title = 'Next';

Полнотекстовый поиск ничем не отличается от синтаксиса используемого в MySQL

Пример полнотекстового поиска:

SELECT * FROM posts WHERE posts MATCH 'learn SQLite';

SELECT * FROM posts WHERE posts MATCH 'search AND sqlite OR help';

Встроенные функции

Так же советую обратить внимание на функции: bm25(), highlight() и snippet()

Пример:

SELECT highlight(posts, 0, '<b>', '</b>') title, highlight(posts, 1, '<b>', '</b>') body FROM posts WHERE posts MATCH 'SQLite' ORDER BY rank;

Итог

SqLite обладает необходимым функционалом для полноценно поиска так же как MySQL