由于您将列表存储为包含逗号分隔列表的字符串,而不是作为集合,因此 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'
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
--or--
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
我们可以解压这些查询,它们并不困难。询问h
返回至少具有一个关键字的product_id列表,查询k
返回包含指定关键字之外的某些关键字的product_id 列表。那里的“技巧”(如果你想这样称呼它)是反连接模式...执行外连接来匹配行,并包含没有匹配的行,以及 WHERE 子句中的谓词消除具有匹配项的行,留下产品中不具有匹配项的行集。
但是,如果将集合存储为单个字符列中的“逗号分隔列表”,我们就失去了关系代数的所有优点;没有任何简单的方法可以将关键字列表处理为“集合”。
由于整个列表存储为单个字符串,我们需要使用一些可怕的 SQL 来获取指定的结果。
执行您指定的检查类型的一种方法是创建一组所有可能的“匹配项”,然后检查它们。这对于几个关键字是可行的。例如,要获取仅包含关键字的产品列表'vanilla'
and/or '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 受到可以从 keywords_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 表。