SQLite Full-Text Search (FTS5)
SQLite Full-Text Search (FTS5) is a SQL statement in the SQLite Specific category. SQLite FTS5 provides powerful full-text search with ranking, prefix queries, and column-specific searches. The syntax is CREATE VIRTUAL TABLE search USING fts5(title, body);. It returns fTS search results. A typical example: CREATE VIRTUAL TABLE articles_fts USING fts5( title, body, content='articles', content_rowid='id' ); -- Populate through triggers or manually: INSERT INTO articles_fts SELECT id, title, body FROM articles; -- Search with ranking: SELECT title, rank FROM articles_fts WHERE articles_fts MATCH 'database optimization' ORDER BY rank; -- Prefix search: SELECT * FROM articles_fts WHERE articles_fts MATCH 'optim*' -- Column-specific search: SELECT * FROM articles_fts WHERE articles_fts MATCH 'title:SQL' -- Phrase search: SELECT *… A close relative is AUTOINCREMENT (SQLite), which sQLite: unlike AUTO_INCREMENT in MySQL, only INTEGER PRIMARY KEY columns auto-increment. AUTOINCREMENT guarantees unique increasing IDs. A close relative is ROWID / _ROWID_, which sQLite: every row has an implicit 64-bit ROWID. INTEGER PRIMARY KEY becomes an alias for ROWID.