MySQL'选择WHERE就像x或y'一行的多个结果

Question

Alright, I tried to word the title as well as possible. Here's what I'm looking for...let's say I've got a row with an ID of 3 in a table called 'table' with a 'col1' value of "apple,potato,carrot,squash" that I want to search.

I want to be able to do a search something like this:

SELECT * FROM table WHERE col1 LIKE '%potato%' OR col1 LIKE '%apple%';

...and I want it to result in two separate results for the row with the ID of 3.

I could parse out the results with PHP obviously, but it seems a lot more efficient to just get the results exactly as I want them directly from MySQL. Is there a way to do this?

(Note that this is not a homework assignment or anything, I'm just trying to be as generic as possible for the sake of the example)

Answer 1

You're nullifying the use of indexes with the LIKE '%substring%' query. Using UNION ALL with multiple queries would work, and it's simple. However, one drawback to that method is that MySQL will have to scan all the rows in the database for each subquery.

So, for a query like the following, assuming 1000 records:

SELECT * FROM table WHERE col1 LIKE '%potato%' 
UNION ALL
SELECT * FROM table WHERE col1 LIKE '%apple%'

MySQL will have to scan through 2000 records (1000 * 2). Then, you have to process the results, when really, you just want a count. For three search types, it's 3000, etc. It doesn't scale well.

Instead, both for performance, and for simplicity (in processing the results), you can have MySQL do the work all at once with the CASE and SUM statements:

SELECT SUM(CASE 
             WHEN t.col1 LIKE '%potato%'  THEN 1
             ELSE 0
           END) AS numPotatoes,
       SUM(CASE 
             WHEN t.col1 LIKE '%apple%' THEN 1
             ELSE 0
           END) AS numApples
  FROM table t

This allows MySQL to scan through all the records just once and return your actual counts.