MySQL返回所有行，其中一列包含集合中的任何关键字，但仅包含关键字

Question

有没有一种方法可以选择其中一列仅包含，但包含任意数量的预定义值的行？

我一直在使用它，但是它返回的行中我的列至少包含一个值（我知道这正是它应该做的）。

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

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

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

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

Product1: chocolate, sugar

Product2: chocolate

Product3: chocolate, sugar, milk, oats, bran

Product4: chocolate, sugar, salt

我的专栏包含用逗号分隔的适用于该产品行的所有关键字的列表。

Answer 1

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

如果将“列表”存储为标准关系集，而将产品的每个关键字存储在表中的单独行中，则返回指定的结果集几乎是微不足道的。

例如，如果我们有此表：

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

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

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'

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”但没有其他关键字相关联的产品，上面的查询与上面的查询相同，但具有附加的联接：

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返回具有至少一个关键字k的product_id的列表，查询返回具有除指定关键字之外的某些关键字的product_id的列表。此处的“技巧”（如果您要称呼它）是反联接模式……进行外部联接以匹配行，并包括没有匹配项的行，以及WHERE子句中的谓词，消除具有匹配项的行，从而保留不具有匹配项的产品中的行集。

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

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

一种执行您指定的检查类型的方法是创建一组所有可能的“匹配项”，然后进行检查。这对于几个关键字是可行的。例如，要获取仅包含关键字'vanilla'和/或的产品列表'chocolate'（即，至少具有这些关键字之一并且没有任何其他关键字）：

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

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

另一种（丑陋的）方法是将转换keyword_list为一个集合，以便我们可以像回答中的第一个查询那样使用查询。但是执行转换的SQL受可以从keyword_list提取的任意最大关键字数限制。

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

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

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

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

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
…

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

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

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