MySQL返回所有行，其中一列包含集合中的任何关键字，但仅包含关键字 [英] MySQL return all rows where a column contains any but only keywords from a set

问题描述

是否可以选择其中一个列仅包含，但不包含任何数量的预定义值的行?

Is there a way to select rows where one of the column contains only , but any number of, predefined values?

我一直在使用它，但是它返回任何行，其中我的列至少包含一个值(我知道这正是应该做的事情).

I've been using this, but it returns any rows where my column contains at least one of the values (which is exactly what it's suppose to do, I know).

但是我正在寻找一种方法，仅选择在关键字列中仅包含我的关键字的行.

But I'm looking for a way to only select rows that have ONLY my keywords in the keyword column.

SELECT * 
FROM 
    `products`.`product` 
WHERE 
    keywords LIKE '%chocolate%' 
AND keyword LIKE '%vanilla%';

示例关键字:chocolate, sugar, milk, oats

使用上述关键字，我希望返回前两个结果，而不是后两个:

Using the above keywords, I would want the first two results returned, but not the last two:

Product1: chocolate, sugar 

Product2: chocolate 

Product3: chocolate, sugar, milk, oats, bran 

Product4: chocolate, sugar, salt

我的列包含一个逗号分隔的列表，列出了适用于该产品行的所有关键字.

My column contains a comma separated list of all keywords applicable to that product row.

推荐答案

由于您将列表存储为包含逗号分隔列表的字符串，而不是作为一组存储，因此MySQL在解决问题上将无济于事那.当将其插入数据库时​​，MySQL将其视为单个字符串.从数据库中检索它时，MySQL会将其视为单个字符串.当我们在查询中引用它时，MySQL会将其视为单个字符串.

Since you are storing the list as a string containing a comma separated list, rather than as a set, MySQL isn't going to be able to help much with that. When it was inserted into the database, MySQL saw it as a single string. When it's retrieved from the database, MySQL sees it as a single string. When we refer to it in a query, MySQL sees it as a single string.

如果列表"存储为标准关系集，并且产品的每个关键字都存储在表中的单独行中，则返回指定的结果集几乎是微不足道的.

If the "list" was stored as a standard relational set, with each keyword for a product stored as a separate row in the table, then returning the result set you specified is almost trivial.

例如，如果我们有此表:

For example, if we had this table:

CREATE TABLE product_keyword 
product_id      BIGINT UNSIGNED COMMENT 'FK ref products.id'
keyword         VARCHAR(20)

将与特定产品相关联的每个关键字放在单独的行中:

With each keyword associated to a particular product as a separate row:

product_id keyword
---------- ---------
         1 chocolate
         1 sugar
         2 chocolate
         3 bran
         3 chocolate
         3 milk
         3 oats
         3 sugar
         4 chocolate
         4 salt
         4 sugar

然后在product中查找所有具有'chocolate'或'vanilla'

Then to find all rows in product that have a keyword other than 'chocolate' or 'vanilla'

SELECT p.id
  FROM product p
  JOIN product_keyword k
 WHERE k.product_id = p.id
    ON k.keyword NOT IN ('chocolate','vanilla')
 GROUP BY p.id

-或-

SELECT p.id
  FROM product p
  LEFT
  JOIN ( SELECT j.id
           FROM product_keyword j
          WHERE j.keyword NOT IN ('chocolate','vanilla')
         GROUP BY j.id
       ) k
    ON k.id = p.id 
 WHERE k.id IS NULL

要获得具有至少一个关键字"chocolate"和"vanilla"但没有其他关键字相关联的产品，则与上面的查询相同，但具有附加的联接:

To get products that have at least one of the keywords 'chocolate' and 'vanilla', but that have no other keywords associated, it's the same query above, but with an additional join:

SELECT p.id
  FROM product p
  JOIN ( SELECT g.id
           FROM product_keyword g
          WHERE g.keyword IN ('chocolate','vanilla')
         GROUP BY g.id
       ) h
    ON h.id = p.id 
  LEFT
  JOIN ( SELECT j.id
           FROM product_keyword j
          WHERE j.keyword NOT IN ('chocolate','vanilla')
         GROUP BY j.id
       ) k
    ON k.id = p.id 
 WHERE k.id IS NULL

我们可以解压缩那些查询，它们并不难.查询h返回具有至少一个关键字的product_id列表，查询k返回具有除指定关键字之外的某些关键字的product_id列表.此处的技巧"(如果您要称呼它)是反联接模式……进行外部联接以匹配行，并包括没有匹配项的行，以及WHERE子句中的谓词，删除具有匹配项的行，从而使产品中没有匹配项的行集消失.

We can unpack those queries, they aren't difficult. Query h returns a list of product_id that have at least one of the keywords, query k returns a list of product_id that have some keyword other than those specified. The "trick" there (if you want to call it that) is the anti-join pattern... doing an outer join to match rows, and include rows that didn't have a match, and a predicate in the WHERE clause that eliminates rows that had a match, leaving the set of rows from product that didn't have a match.

但是将集合存储为单个字符列中的逗号分隔列表"时，我们失去了关系代数的所有优势；没有简单的方法将关键字列表作为集合"进行处理.

But with the set stored as a "comma separated list" in a single character column, we lose all the advantages of relational algebra; there isn't any easy way to process the list of keywords as a "set".

将整个列表存储为单个字符串，我们得到了一些可怕的SQL来获得指定的结果.

With the entire list stored as a single string, we've got some horrendous SQL to get the specified result.

进行指定检查的一种方法是创建一组所有可能的匹配项"，然后进行检查.这对于几个关键字是可行的.例如，要获取仅包含关键字'vanilla'和/或'chocolate'的产品列表(即，具有至少一个这些关键字并且没有任何其他关键字的产品):

One approach to doing the kind of check you specify would be to create a set of all possible "matches", and check those. This is workable for a couple of keywords. For example, to get a list of products that have ONLY the keywords 'vanilla' and/or 'chocolate', (that is, that have at least one of those keywords and does not have any other keyword):

SELECT p.id
  FROM product 
 WHERE keyword_list = 'chocolate'
    OR keyword_list = 'vanilla'
    OR keyword_list = 'chocolate,vanilla'
    OR keyword_list = 'vanilla,chocolate'

但是将其迅速扩展到三个，四个或五个关键字变得很笨拙(除非确保保证关键字以特定的顺序出现.而且要检查四个关键字中的三个关键字非常困难.

But extending that to three, four or five keywords quickly becomes unwieldy (unless the keywords are guaranteed to appear in a particular order. And it's very difficult to check for three out of four keywords.

另一种(丑陋的)方法是将keyword_list转换为集合，以便我们可以使用答案中的第一个查询之类的查询.但是执行转换的SQL受到可以从keyword_list提取的任意最大关键字数的限制.

Another (ugly) approach is to transform the keyword_list into a set, so that we can use queries like the first ones in my answer. But the SQL to do the transformation is limited by an arbitrary maximum number of keywords that can be extracted from the keyword_list.

使用一些简单的SQL字符串函数从逗号分隔的列表中提取第n个元素非常容易，例如，从逗号分隔的列表中提取前五个元素:

It's fairly easy to extract the nth element from a comma separated list, using some simple SQL string functions, for example, to extract the first five elements from a comma separated list:

SET @l := 'chocolate,sugar,bran,oats'
SELECT NULLIF(SUBSTRING_INDEX(CONCAT(@l,','),',',1),'')                         AS kw1
     , NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(@l,','),',',2),',',-1),'') AS kw2
     , NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(@l,','),',',3),',',-1),'') AS kw3
     , NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(@l,','),',',4),',',-1),'') AS kw4
     , NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(@l,','),',',5),',',-1),'') AS kw5

但是这些仍然在同一行.如果要对它们进行检查，则需要做一些比较，我们需要检查每个对象以查看是否在指定列表中.

But those are still on the same row. If we want to do checks on those, we'd have a bit of comparing to do, we'd need to check each one of those to see if it was in the specified list.

如果我们可以将这些关键字在一行上转换为一组行，并且每一行上都有一个关键字，那么我们可以在我的答案中使用类似于第一个查询的查询.例如:

If we can get those keywords, on the one row, transformed into a set of rows with one keyword on each row, then we could use queries like the first ones in my answer. As an example:

SELECT t.product_id
     , NULLIF(CASE n.i
       WHEN 1 THEN SUBSTRING_INDEX(CONCAT(t.l,','),',',1)
       WHEN 2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(t.l,','),',',2),',',-1)
       WHEN 3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(t.l,','),',',3),',',-1)
       WHEN 4 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(t.l,','),',',4),',',-1)
       WHEN 5 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(t.l,','),',',5),',',-1)
       END,'') AS kw
  FROM ( SELECT 4 AS product_id,'fee,fi,fo,fum' AS l  
          UNION ALL 
         SELECT 5, 'coffee,sugar,milk'
        ) t
 CROSS
  JOIN ( SELECT 1 AS i
         UNION ALL SELECT 2
         UNION ALL SELECT 3
         UNION ALL SELECT 4
         UNION ALL SELECT 5
       ) n
HAVING kw IS NOT NULL
ORDER BY t.product_id, n.i

这使我们获得了单独的行，但前5个关键字中的每个关键字都限于一行.很容易看出将如何扩展(n返回6,7,8，...)并扩展CASE中的WHEN条件以处理6,7,8 ...

That gets us individual rows, but it's limited to a row for each of the first 5 keywords. It's easy to see how that would be extended (having n return 6,7,8,...) and extending the WHEN conditions in the CASE to handle 6,7,8...

但是会有一些任意限制. (作为演示，我使用了别名为t的内联视图来返回两个示例"行.该内联视图可以替换为对包含product_id和keyword_list列的表的引用.)

But there is going to be some arbitrary limit. (I've used an inline view, aliased as t, to return two "example" rows, as a demonstration. That inline view could be replaced with a reference to the table containing the product_id and keyword_list columns.)

因此，该查询为我们提供了一个行集，就像从上面作为示例给出的product_keyword表中返回的一样.

So, that query gets us a rowset like would be returned from the product_keyword table I gave as an example above.

在示例查询中，对product_keyword表的引用可以用该查询替换.但这是很多丑陋的SQL，而且效率极低，它在每次运行查询时都会创建并填充临时MyISAM表.

In the example queries, references to the product_keyword table could be replaced with this query. But that's a whole lot of ugly SQL, and its horrendously inefficient, creating and populating temporary MyISAM tables anytime a query is run.

这篇关于MySQL返回所有行，其中一列包含集合中的任何关键字，但仅包含关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！