是否有方法选择其中一列只包含但不包含任何数量的预定义值的行？
我一直在使用它，但它返回的任何行中，我的列至少包含一个值（这正是它应该做的，我知道）。
但是我正在寻找一种方法来只选择在关键字列中只有我的关键字的行。

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

Product1: chocolate, sugar 

Product2: chocolate 

Product3: chocolate, sugar, milk, oats, bran 

Product4: chocolate, sugar, salt

最佳答案

因为您将列表存储为包含逗号分隔列表的字符串，而不是作为一个集合，所以mysql将无法在这方面提供太多帮助。当它被插入数据库时，mysql将其视为单个字符串。从数据库中检索时，mysql将其视为单个字符串。当我们在查询中引用它时，mysql将其视为单个字符串。
如果“list”作为标准关系集存储，而产品的每个关键字作为单独的行存储在表中，那么返回指定的结果集几乎是微不足道的。
例如，如果我们有这个表：

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

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

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

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

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

关于mysql - MySQL返回列中包含任何但仅包含集合中的关键字的所有行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/22848046/