Mysql fulltext search with boolean operators

Question

I am trying to search for values with OR and AND

here is a example for OR

SELECT * FROM contacts_items WHERE MATCH (item_id) AGAINST ('(10 | 1)' IN BOOLEAN MODE);

This works but if I wanted to do AND

SELECT * FROM contacts_items WHERE MATCH (item_id) AGAINST ('(10 & 1)' IN BOOLEAN MODE);

This will attempt to find contacts with 10 and 1 in the same row but I want to search for a contacts who have 10 and 1 as an item_id but over multiple rows

eg

contact_id    item_id
    1             1
    1             10
    2             10

The AND search would return contact_id - 1

I think this isn't possible with one search but thought I would ask before making the code more complex then it needs be.

Thanks Tim

Answer 1

First of all, you are misusing the AND and OR fulltext search operators. | and & are reserved for future use; use + or - as stated in the documentation.

Another aspect I don't seem to understand from your example is why are you using fulltext search if you are using integers. A simple WHERE item_id IN (1, 10) would suffice.

However, if you need fulltext search I would recommend using Sphinx.