Summary: in this tutorial, you will learn how to use the SQLite full-text search feature by using the FTS5 virtual table module.
Introduction to SQLite full-text search
A virtual table is a custom extension to SQLite. A virtual table is like a normal table. The difference between a virtual table and a normal table is where the data come from i.e., when you process a normal table, SQLite accesses the database file to retrieve data. However, when you access a virtual table, SQLite calls the custom code to get the data. The custom code can have specified logic to handle certain tasks such as getting data from multiple data sources.
To use full-text search in SQLite, you use FTS5 virtual table module.
The following CREATE VIRTUAL TABLE statement creates an FTS5 table with two columns:
CREATE VIRTUAL TABLE table_name
USING FTS5(column1,column2...);
Code language: SQL (Structured Query Language) (sql)Notice that you cannot add types, constraints, or PRIMARY KEY declaration in the CREATE VIRTUAL TABLE statement for creating an FTS5 table. If you do so, SQLite will issue an error.
Like creating a normal table without specifying the primary key column, SQLite adds an implicit rowid column to the FTS5 table.
The following example creates an FTS5 table named posts with two columns title and body.
CREATE VIRTUAL TABLE posts
USING FTS5(title, body);
Code language: SQL (Structured Query Language) (sql)Similar to a normal table, you can insert data into the posts table as follows:
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');
Code language: SQL (Structured Query Language) (sql)And query data against it:
SELECT * FROM posts;
Code language: SQL (Structured Query Language) (sql)