'LIKE ('%this%' OR '%that%') a

问题:

I have a select query where I am trying to search strings for multiple patterns

LIKE ('%this%' or '%that%' ) and something=else

Returns zero results

However

LIKE '%this%' and something=else

returns results and

LIKE '%that%' and something=else

returns result

Is it possible to get all my results into one query? If a string matches both, how will it handle that?

回答1:

It would be nice if you could, but you can't use that syntax in SQL.

Try this:

(column1 LIKE '%this%' OR column1 LIKE '%that%') AND something = else

Note the use of brackets! You need them around the OR expression.
Without brackets, it will be parsed as A OR (B AND C),which won't give you the results you expect.

回答2:

Instead of using `LIKE` use `REGEXP`.

For example:

    REGEXP 'THIS|THAT'

For example:

    REGEXP 'THIS|THAT'
mysql> SELECT 'pi' REGEXP 'pi|apa';                     -> 1
mysql> SELECT 'axe' REGEXP 'pi|apa';                    -> 0
mysql> SELECT 'apa' REGEXP 'pi|apa';                    -> 1
mysql> SELECT 'apa' REGEXP '^(pi|apa)$';                -> 1
mysql> SELECT 'pi' REGEXP '^(pi|apa)$';                 -> 1
mysql> SELECT 'pix' REGEXP '^(pi|apa)$';                -> 0

Refer:
http://dev.mysql.com/doc/refman/5.1/en/regexp.html

回答3:

Try something like:

WHERE (column LIKE '%this%' OR column LIKE '%that%') AND something = else

回答4:

Break out the LIKE clauses into 2 separate statements, i.e.:

(fieldname1 LIKE '%this%' or fieldname1 LIKE '%that%' ) and something=else

回答5:

Do you have something against splitting it up?

...FROM <blah> 
   WHERE 
     (fieldA LIKE '%THIS%' OR fieldA LIKE '%THAT%') 
     AND something = else

回答6:

Have you tried:

(column LIKE '%this%' and something=else) or (column LIKE '%that%' and something=else)

回答7:

I know it's a bit old question but still people try to find efficient solution so instead you should use FULLTEXT index (it's available from MySQL 5.6.4).

Query on table with +35mil records by triple like in where block took ~2.5s but after adding index on these fields and using BOOLEAN MODE inside match ... against ... it took only 0.05s.