由于您将列表存储为包含逗号分隔列表的字符串,而不是作为一组存储,因此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表。