Shulou(Shulou.com)05/31 Report--

This article mainly shows you "SQLite3 how to achieve database full-text search", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "SQLite3 how to achieve database full-text search" this article.

For application software developers, there are many solutions to solve this problem. For example, local execution using stand-alone software such as MySQL and PostgreSQL or Sphinx and Lucene. However, these are either tricky to use or excessive.

Fortunately, Google contributes some resources to SQLite to help. Full-text retrieval is implemented for the first time in version 3.3.8. This version provides the ability to create a virtual table that depends on external extensions: here, the full-text search algorithm can be used for text columns in any virtual table. In PHP 5.3.0, the corresponding support is only activated by the default PDO and SQLite3. Earlier versions of PHP can use PECL's SQLite3 extension library.

And install the latest version of SQLite

# wget http://www.sqlite.org/sqlite-amalgamation-3.6.22.tar.gz

# tar-zxvf sqlite-amalgamation-3.6.22.tar.gz

# cd sqlite-3.6.22/

# CFLAGS= "- DSQLITE_ENABLE_FTS3=1". / configure

# make

# make install

Complete

View version

# sqlite3-version

It is common to create a search index as follows:

Decompose the text into symbols.

Convert to lowercase letters.

Determine the root word.

Build an index.

By default, SQLite provides two basic word splitters, Simple and Porter. They can control how words are separated. Simple decomposes the text into different tokens based on spaces and punctuation. Porter is designed for the use of English, it can be a large number of text expansion into the basic form. For example, words like condolidate,consolidated and consolidating are transformed into consolid.

Unfortunately, SQLite has not yet removed the stop words. So common words, such as the,of and to, are still in the index. This greatly expands the scope of the index and slows down the search. The easiest solution is to manually remove stop words before pressing to confirm retrieval.

Next, I'll show you some code that shows you how to create your first full-text index. SQLite does this because it creates a virtual table by using the FTS3 extension. Only if the text column is within this virtual table can it be searched, and the last column is used to identify the type of word splitter used.

CREATE VIRTUAL TABLE example

USING FTS3 (title TEXT, TOKENIZE SIMPLE)

After you create a table, you can query the table using the SELECT, INSERT,UPDATE, and DELETE statements. It is important to note here that no further indexes can be built on the table, so a simple query will result in a scan of the whole table.

Once you type in some data, you can try.

For the rest of this article, I will use all the titles from the English version of Wikipedia.

Mine has 5453838 rows, compared to 146MB without an index. If you use a full-text index, the size is 233MB.

Search

The search index is done by the match operation. A query can contain multiple aspects, in which case it is returned only if the text line contains all the conditions. There are also queries that support "OR", but this query excludes conditions, precise phrase matching, and prefix retrieval.

SELECT rowid, title FROM example WHERE title MATCH tea bag

SELECT rowid, title FROM example WHERE title MATCH tea OR bag

SELECT rowid, title FROM example WHERE title MATCH tea-bag

SELECT rowid, title FROM example WHERE title MATCH "tea bag"

SELECT rowid, title FROM example WHERE title MATCH tea*

Note that OR is case-sensitive and allows only one MATCH operation in a query.

Create a clip

To provide context to matching search results, you can use the snippet () function. This feature will highlight the keywords for any text column in the search results.

SELECT title, snippet (example)

FROM example

WHERE

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.