LabEx
ВходРегистрация

Полнотекстовое индексирование SQLite

SQLiteBeginner
Практиковаться сейчас

Введение

В этой лабораторной работе вы узнаете, как использовать расширение FTS5 (Full-Text Search 5) в SQLite для эффективного полнотекстового поиска. Вы создадите таблицы FTS5, вставите данные, выполните поиск и оптимизируете запросы с помощью предложений MATCH. Эта лабораторная работа представляет собой практическое введение в полнотекстовое индексирование в SQLite.

Создание базы данных SQLite и включение FTS5

В этом шаге вы создадите базу данных SQLite и включите расширение FTS5 (Full-Text Search 5). FTS5 — это модуль, предоставляющий возможности полнотекстового поиска.

Сначала откройте свой терминал в LabEx VM (виртуальной машине). Ваш путь по умолчанию — /home/labex/project.

  1. Создайте базу данных SQLite:

    Создайте файл базы данных с именем books.db и подключитесь к нему с помощью команды sqlite3:

    sqlite3 books.db

    Эта команда запускает оболочку SQLite, где вы можете выполнять SQL-команды.

  2. Включите расширение FTS5:

    Прежде чем вы сможете использовать FTS5, вам необходимо его включить. Выполните следующую SQL-команду:

    CREATE VIRTUAL TABLE book_search USING fts5();

    Если вы видите ошибку, например Error: no such module: fts5, это означает, что расширение FTS5 недоступно. В большинстве современных версий SQLite FTS5 включен по умолчанию. Если вы столкнулись с этой ошибкой, убедитесь, что ваша версия SQLite является актуальной. Если команда выполняется без ошибок, расширение FTS5 включено.

    Эта команда создает виртуальную таблицу с именем book_search с использованием модуля fts5. Виртуальные таблицы — это функция SQLite, которая позволяет расширять базу данных пользовательскими функциями.

  3. Определите схему таблицы:

    Теперь давайте определим столбцы для нашей таблицы book_search. Мы включим столбцы для title (название), author (автор) и content (содержание). Выполните следующую SQL-команду:

    CREATE VIRTUAL TABLE book_search USING fts5(title, author, content);

    Эта команда создает таблицу book_search с указанными столбцами. Столбцы title, author и content будут проиндексированы FTS5, что позволит вам выполнять полнотекстовый поиск по ним.

  4. Настройте токенизатор (необязательно):

    Вы можете настроить способ, которым FTS5 разбивает текст на токены, указав токенизатор (tokenizer). Токенизатор unicode61 обеспечивает хорошую поддержку Unicode. Чтобы использовать его и удалить диакритические знаки (ударения), воссоздайте таблицу с помощью следующей команды:

    DROP TABLE IF EXISTS book_search;
CREATE VIRTUAL TABLE book_search USING fts5(title, author, content, tokenize="unicode61 remove_diacritics 1");

    Эта команда сначала удаляет существующую таблицу book_search (если она существует), а затем воссоздает ее с помощью токенизатора unicode61. Опция remove_diacritics 1 указывает FTS5 удалять диакритические знаки из текста во время индексации.

Вставка данных в таблицу FTS5

В этом шаге вы вставите данные в таблицу book_search. Эти данные будут использоваться для полнотекстового поиска на последующих шагах.

  1. Вставка данных о книгах:

    Выполните следующие SQL-команды, чтобы вставить примеры данных о книгах в таблицу book_search:

    INSERT INTO book_search (title, author, content) VALUES (
    'The Lord of the Rings',
    'J.R.R. Tolkien',
    'A fantasy epic about hobbits, elves, and the battle against Sauron.'
);

INSERT INTO book_search (title, author, content) VALUES (
    'Pride and Prejudice',
    'Jane Austen',
    'A classic novel about love, class, and society in 19th-century England.'
);

INSERT INTO book_search (title, author, content) VALUES (
    'The Hitchhiker''s Guide to the Galaxy',
    'Douglas Adams',
    'A comedic science fiction series following the misadventures of Arthur Dent.'
);

INSERT INTO book_search (title, author, content) VALUES (
    'To Kill a Mockingbird',
    'Harper Lee',
    'A powerful story about racial injustice in the American South.'
);

INSERT INTO book_search (title, author, content) VALUES (
    '1984',
    'George Orwell',
    'A dystopian novel about totalitarianism and surveillance.'
);

    Эти команды вставляют пять строк в таблицу book_search, каждая из которых представляет книгу. Для каждой книги указаны значения title (название), author (автор) и content (содержание). Обратите внимание на экранированную одинарную кавычку в 'The Hitchhiker''s Guide to the Galaxy'. Одинарные кавычки внутри строки должны быть экранированы путем их удвоения.

  2. Проверка вставки данных:

    Чтобы убедиться, что данные добавлены правильно, выполните следующую команду:

    SELECT * FROM book_search;

    Эта команда извлекает все строки и столбцы из таблицы book_search. Вы должны увидеть данные, которые вы только что вставили.

    Ожидаемый вывод:

    The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
The Hitchhiker's Guide to the Galaxy|Douglas Adams|A comedic science fiction series following the misadventures of Arthur Dent.
To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.
1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

Выполнение базового полнотекстового поиска

В этом шаге вы выполните базовые полнотекстовые поиски с использованием оператора MATCH.

  1. Поиск по одному термину:

    Чтобы найти книги, содержащие слово "fantasy" (фэнтези), выполните следующую SQL-команду:

    SELECT * FROM book_search WHERE book_search MATCH 'fantasy';

    Эта команда ищет термин "fantasy" во всех столбцах таблицы book_search. Оператор MATCH используется для выполнения полнотекстового поиска.

    Ожидаемый вывод:

    The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.

  2. Поиск по нескольким терминам:

    Вы можете искать по нескольким терминам, разделяя их пробелами. Чтобы найти книги, содержащие как "love" (любовь), так и "society" (общество), выполните следующую SQL-команду:

    SELECT * FROM book_search WHERE book_search MATCH 'love society';

    Эта команда ищет строки, которые содержат как "love", так и "society" в любом из проиндексированных столбцов.

    Ожидаемый вывод:

    Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.

  3. Поиск в определенном столбце:

    Чтобы выполнить поиск в определенном столбце, укажите имя столбца перед оператором MATCH. Например, чтобы найти книги с "Orwell" в столбце author (автор), выполните следующую SQL-команду:

    SELECT * FROM book_search WHERE author MATCH 'Orwell';

    Эта команда ищет термин "Orwell" только в столбце author.

    Ожидаемый вывод:

    1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

Использование расширенных возможностей MATCH

В этом шаге вы изучите расширенные возможности оператора MATCH, включая поиск по префиксу и логические операторы.

  1. Поиск по префиксу (Prefix Search):

    Используйте подстановочный знак * для выполнения поиска по префиксу. Чтобы найти книги, содержащие слова, начинающиеся с "soci", выполните следующую SQL-команду:

    SELECT * FROM book_search WHERE book_search MATCH 'soci*';

    Эта команда ищет термины, которые начинаются с "soci", такие как "society" (общество).

    Ожидаемый вывод:

    Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.

  2. Логические операторы (Boolean Operators):

    FTS5 поддерживает логические операторы, такие как AND (И), OR (ИЛИ) и NOT (НЕ), для создания более сложных поисковых запросов.

    • AND (И): Найти документы, содержащие оба термина. По умолчанию, когда термины разделены пробелами, используется AND.

      SELECT * FROM book_search WHERE book_search MATCH 'love AND society';

      Это эквивалентно SELECT * FROM book_search WHERE book_search MATCH 'love society';

      Ожидаемый вывод:

      Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.

    • OR (ИЛИ): Найти документы, содержащие любой из терминов.

      SELECT * FROM book_search WHERE book_search MATCH 'love OR injustice';

      Эта команда находит книги, содержащие либо "love" (любовь), либо "injustice" (несправедливость) (или оба).

      Ожидаемый вывод:

      Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.

    • NOT (НЕ): Исключить документы, содержащие определенный термин.

      SELECT * FROM book_search WHERE book_search MATCH 'NOT fantasy';

      Эта команда находит книги, которые не содержат слово "fantasy" (фэнтези).

      Ожидаемый вывод:

      Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
The Hitchhiker's Guide to the Galaxy|Douglas Adams|A comedic science fiction series following the misadventures of Arthur Dent.
To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.
1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

Резюме

В этой лабораторной работе вы узнали, как использовать расширение FTS5 (Full-Text Search 5) SQLite для полнотекстового поиска. Вы создали таблицу FTS5, вставили данные, выполнили базовые поиски с использованием оператора MATCH и изучили расширенные функции, такие как поиск по префиксу (prefix searches) и логические операторы (boolean operators). Эти навыки обеспечивают основу для создания мощных поисковых возможностей в ваших приложениях SQLite.

Связанные SQLite Курсы
SQLite для начинающих

SQLite для начинающих

databasesqlite
SQLite: от среднего к продвинутому

SQLite: от среднего к продвинутому

databasesqlite