如何在sqlite db中有效地搜索整个单词

I have a table that has a title column. I want to search for whole words like foo. so match " hi foo bye" o "foo", but not "foobar" or "hellofoo". Is there a way without changing the table structure to do this? I currently use 3 like queries, but it is too slow, I have " select * from articles where title like '% foo' or title like 'foo %' or title = 'foo' or title like '% foo %';
There has got to be a better way to do this?

我有一个有标题栏的表。我想搜索像foo这样的整个单词。所以匹配“hi foo bye”o“foo”,但不是“foobar”或“hellofoo”。有没有办法不改变表结构来做到这一点?我目前使用3个相似的查询,但它太慢,我有“选择*来自'%foo'之类的标题或'foo%'或title ='foo'等标题或'%foo%'之类的标题;有必须是一个更好的方法来做到这一点?

3 个解决方案

#1

You might be interested in a search indexer like lucene, ferret, or sphinx. These would run as separate processes that would index your data for fast searching where stemming, etc. can be configured.

您可能对lucene,ferret或sphinx等搜索索引器感兴趣。这些将作为单独的进程运行,可以将数据编入索引,以便快速搜索可以配置词干等。

Alternatively, depending on your data, you could just return all results that contain "foo" in any context and then filter them with regular expressions or such outside of the database. This might be an improvement depending on the characteristics of your data.

或者,根据您的数据,您可以在任何上下文中返回包含“foo”的所有结果,然后使用正则表达式或数据库之外的类型过滤它们。这可能是一项改进,具体取决于数据的特征。

#2

spontaneous answer:

use the regexp operator instead of the like operator.

使用regexp运算符而不是like运算符。

EDIT I just realised that regexp is not always included with SQLite. You might have to compile you own ... in other words, it's not there by default ..

编辑我刚刚意识到reitexp并不总是包含在SQLite中。您可能需要编译自己的...换句话说,它默认不存在..

EDIT2

here's a working Perl sample ..

这是一个有效的Perl样本..

#!/usr/bin/perl -w
use strict;
use Data::Dumper;
use DBI;

# connect to the DB
my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile","","");

# create ugly, pureperl function 'regexp'
# stolen from http://d.hatena.ne.jp/tokuhirom/20090416/1239849298
$dbh->func(   "regexp"
            , 2
            , sub { my ( $pattern, $target ) = @_;
                         utf8::decode($pattern);
                         utf8::decode($target);
                         $target =~ m{$pattern} ? 1 : 0;
              }
            , "create_function" );
# drop table, if it exists
$dbh->do('drop table if exists foobar');
$dbh->do('create table foobar (foo varchar not null)');
my $sth=$dbh->prepare('insert into foobar (foo) values (?)');
while (<DATA>) { chop;$sth->execute($_); }
#query using regexp
my $a= $dbh->selectall_arrayref( 'select foo '
                                .'from foobar '
                                .'where foo regexp "^foo$|^foo\s+.*|.*\W+foo\W+.*|.*\W+foo$"'
                               );
print join("\n", map {$_->[0];} @{$a})
__DATA__
foo
foo
barfoo
foobarfolo
sdasdssds bar dasdsdsad
dasdsdasdsadsads foo! dasdasdasdsa

#3

There are various regexp libraries that you can include in your iPhone application by linking to them in your build.

您可以通过在构建中链接到iPhone应用程序中包含各种正则表达式库。

See this * question for further info.

有关详细信息,请参阅此*问题。

#1