如何使用SQLiteFTS3匹配表中的多列？-HowdoyoumatchmultiplecolumninatablewithSQLiteFTS3?

25  

I don't think you can use multiple MATCH operators in a single FTS query. Use column_name:target_term to search multiple columns using one full text search.

我不认为你可以在一个FTS查询中使用多个MATCH运算符。使用column_name：target_term可以使用一个全文搜索来搜索多个列。

SELECT * FROM table WHERE table MATCH 'A:cat OR C:cat'

http://www.sqlite.org/fts3.html#termprefix

http://www.sqlite.org/fts3.html#termprefix

7  

Instead of specifying which columns to match to, you can specify the entire table to match to. For example:

您可以指定要匹配的整个表，而不是指定要匹配的列。例如：

-- Example schema
CREATE VIRTUAL TABLE mail USING fts3(subject, body);

-- Example table population
INSERT INTO mail(docid, subject, body) VALUES(1, 'software feedback', 'found it too slow');
INSERT INTO mail(docid, subject, body) VALUES(2, 'software feedback', 'no feedback');
INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order',  'was a software problem');

-- Example queries
SELECT * FROM mail WHERE subject MATCH 'software';    -- Selects rows 1 and 2
SELECT * FROM mail WHERE body    MATCH 'feedback';    -- Selects row 2
SELECT * FROM mail WHERE mail    MATCH 'software';    -- Selects rows 1, 2 and 3
SELECT * FROM mail WHERE mail    MATCH 'slow';        -- Selects rows 1 and 3

3  

For the multiple column condition using MATCH use UNION for the 'OR' and use INTERSECT for the 'AND'

对于使用MATCH的多列条件，使用UNION作为'OR'并使用INTERSECT作为'AND'

SELECT * FROM TBL_VIRTUAL_APPOINTMENT WHERE PATIENT_PREFIX MATCH 'D*' 
UNION 
SELECT * FROM TBL_VIRTUAL_APPOINTMENT WHERE patient_first_name MATCH 'K*'

2  

Using sub-queries as workaround:

使用子查询作为变通方法：

OR equivalent query, like a MATCH 'cat' OR c MATCH 'cat':

OR等效查询，如MATCH'cat'或c MATCH'cat'：

SELECT * FROM table
WHERE ROWID IN (
    SELECT ROWID FROM table WHERE a MATCH 'cat'
UNION
    SELECT ROWID FROM table WHERE b MATCH 'cat'
);

AND equivalent query, like a MATCH 'cat' AND c MATCH 'cat':

AND等效查询，如MATCH'cat'和c MATCH'cat'：

SELECT * FROM table WHERE ROWID IN (
    SELECT ROWID FROM table WHERE a MATCH 'cat'
    AND ROWID IN (
        SELECT ROWID FROM table WHERE b MATCH 'cat'
    )
);

-1  

You can concatenate columns using the || operator:

您可以使用||连接列运营商：

SELECT * FROM table WHERE a || b MATCH 'cat'

I'm not sure about the MATCH function in SQLite though, I think it will usually throw an exception (see http://www.sqlite.org/lang_expr.html#match).

我不确定SQLite中的MATCH函数，我认为它通常会抛出异常（参见http://www.sqlite.org/lang_expr.html#match）。