mysql WHERE CLAUSE to exclude rows based on two different values in same column

Question

I'm using MySQL Data Compare to compare a local & remote mysql db.
I'm trying to setup a simple WHERE clause that excludes any rows from the comparison that contain the following values:

%mm or %transient%

The WHERE clause i'm using doesn't seem to be working.

'option_name' NOT LIKE '%_transient_%' OR '%mm%'

The full query that's running is:

SELECT 'option_id', 'option_name', 'option_value', 'autoload' 
FROM 'wp_options WHERE 'option_name' NOT LIKE '%_transient_%' OR '%mm%'

And the resulting output does not exclude the rows that that have mm or _transient_ in the option_name column.

The caveat here is that I'm limited to only using a WHERE clause & I'm not able to edit the SELECT clause leading up to it (as that's all generated by the software).

it should be option_name NOT LIKE '%_transient_%' AND option_name NOT LIKE '%mm%'.also don't use single-quotes for column names as they will be treated as string constants. use backticks (in mysql) or double-quotes. — Vamsi Prabhala
– Vamsi Prabhala, Commented Dec 8, 2016 at 21:07
I don't know if this is a typo or not but the table name isn;t set off by quotes — Wes Palmer
– Wes Palmer, Commented Dec 8, 2016 at 21:47

Bill Karwin · Accepted Answer · 2016-12-08 21:35:48Z

2

There is no form of LIKE for comparing to multiple patterns.

As @vkp mentioned in a comment (I don't know why they don't post an answer), your condition won't do what you intend:

WHERE option_name NOT LIKE '%_transient_%' OR '%mm%'

It's not a syntax error in MySQL, because OR can use any expression as operands. Zero values count as false, nonzero values count as true. So your condition is equivalent to:

WHERE (option_name NOT LIKE '%_transient_%') OR ('%mm%')

Which is logically equivalent to:

WHERE (option_name NOT LIKE '%_transient_%') OR (true)

Which will be true on every row, because a true value OR'd together with any other expression will result in true.

Your condition should be this:

WHERE option_name NOT LIKE '%_transient_%' AND option_name NOT LIKE '%mm%'

answered Dec 8, 2016 at 21:35

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eddie Padin Over a year ago

Excellent explanation. At first, using AND seemed counter-intuitive. But your logical equivalency example makes sense why it'd be AND instead of OR. Thanks!

Bill Karwin Over a year ago

Yeah, it's a good idea to be fluent with boolean algebra. (NOT A) AND (NOT B) is equal to NOT (A OR B).

Collectives™ on Stack Overflow

mysql WHERE CLAUSE to exclude rows based on two different values in same column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related