If a customer searches for two or more keywords, I need to return two sets of MySQL results:
1) Primary results: The intersect of those keywords (items only matching all keywords)
2) Secondary results: Items matching the symmetric difference/disjunctive union of the keywords (items matching some but not all keywords)
I can perform query #1 using BOOLEAN mode with a
+ sign before each keyword. I can perform a query that includes both #1 and #2 by leaving the
+ sign off, but I don’t know how to get the desired results described in #2. Any suggestions?
For #2 You just need a combination of:
WHERE MATCH(words) AGAINST('keyword1 keyword2 ... keywordN' IN BOOLEAN MODE) AND NOT MATCH(words) AGAINST('+keyword1 +keyword2 ... +keywordN IN BOOLEAN MODE)
This will return rows that have at least one keyword but not all. Or have I misunderstood your question?
Schema (MySQL v8.0)
create table test ( id int not null auto_increment primary key, words text not null, FULLTEXT idx (words) ); insert into test(words) values('Arc Book Cow Dog'), ('Book Cow Fox'), ('Arc Book Cow Fox'), ('Book Cow');
SELECT * FROM test WHERE MATCH(words) AGAINST('Arc Fox' IN BOOLEAN MODE) AND NOT MATCH(words) AGAINST('+ARC +Fox' IN BOOLEAN MODE) ORDER BY id; | id | words | | --- | ---------------- | | 1 | Arc Book Cow Dog | | 2 | Book Cow Fox |