1

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).

Barmar
  • 741,623
  • 53
  • 500
  • 612
Eddie Padin
  • 645
  • 1
  • 8
  • 13
  • 3
    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 Dec 08 '16 at 21:07
  • See http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks – Barmar Dec 08 '16 at 21:13
  • I don't know if this is a typo or not but the table name isn;t set off by quotes – Wes Palmer Dec 08 '16 at 21:47

1 Answers1

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%'
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 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! – Eddie Padin Dec 09 '16 at 00:42
  • Yeah, it's a good idea to be fluent with boolean algebra. `(NOT A) AND (NOT B)` is equal to `NOT (A OR B)`. – Bill Karwin Dec 09 '16 at 01:12