如何在SQL中实现过滤系统？

现在我打算在我的网站上添加一个过滤系统.

例子:

(ID=apple, COLOR=red, TASTE=sweet, ORIGIN=US)
(ID=mango, COLOR=yellow, TASTE=sweet, ORIGIN=MEXICO)
(ID=banana, COLOR=yellow, TASTE=bitter-sweet, ORIGIN=US)

所以现在我有兴趣做以下事情:SELECT ID FROM thisTable WHERE COLOR ='yellow'AND TASTE ='SWEET'

但我的问题是我在我的网站中为多个类别执行此操作,并且列不一致.(如果桌子是用于手机,那么它将是品牌,3G-ENABLED,价格,颜色,波长等)

我怎么能设计一个允许这个的通用模式？

现在我正计划做:

table(ID, KEY, VALUE)

这允许任意数量的列,但对于查询,我使用SELECT ID FROM表WHERE(KEY = X1 AND VALUE = V1)AND(KEY = X2 AND VALUE = V2),..返回空集.

有人可以推荐一个很好的解决方案吗？请注意,列数将定期更改

您建议的实体属性值模型可适用于此方案.

关于过滤查询,你必须明白,使用EAV模型你会牺牲大量的查询能力,所以这会变得非常棘手.然而,这是解决问题的一种方法:

SELECT    stuff.id 
FROM      stuff 
JOIN      (SELECT    COUNT(*) matches
           FROM      table
           WHERE     (`key` = X1 AND `value` = V1) OR 
                     (`key` = X2 AND `value` = V2) 
           GROUP BY  id
          ) sub_t ON (sub_t.matches = 2 AND sub_t.id = stuff.id)
GROUP BY  stuff.id;

此方法的一个不优雅的特性是您需要指定您希望匹配的属性/值对的数量sub_t.matches = 2.如果我们有三个条件,我们必须指定sub_t.matches = 3,依此类推.

让我们构建一个测试用例:

CREATE TABLE stuff (`id` varchar(20), `key` varchar(20), `value` varchar(20));

INSERT INTO stuff VALUES ('apple',  'color',  'red');
INSERT INTO stuff VALUES ('mango',  'color',  'yellow');
INSERT INTO stuff VALUES ('banana', 'color',  'yellow');

INSERT INTO stuff VALUES ('apple',  'taste',  'sweet');
INSERT INTO stuff VALUES ('mango',  'taste',  'sweet');
INSERT INTO stuff VALUES ('banana', 'taste',  'bitter-sweet');

INSERT INTO stuff VALUES ('apple',  'origin',  'US');
INSERT INTO stuff VALUES ('mango',  'origin',  'MEXICO');
INSERT INTO stuff VALUES ('banana', 'origin',  'US');

查询:

SELECT    stuff.id 
FROM      stuff 
JOIN      (SELECT    COUNT(*) matches, id
           FROM      stuff
           WHERE     (`key` = 'color' AND `value` = 'yellow') OR 
                     (`key` = 'taste' AND `value` = 'sweet')
           GROUP BY  id
          ) sub_t ON (sub_t.matches = 2 AND sub_t.id = stuff.id)
GROUP BY  stuff.id;

结果:

+-------+
| id    |
+-------+
| mango |
+-------+
1 row in set (0.02 sec)

现在让我们用color=yellow和插入另一个水果taste=sweet:

INSERT INTO stuff VALUES ('pear', 'color', 'yellow');
INSERT INTO stuff VALUES ('pear', 'taste', 'sweet');
INSERT INTO stuff VALUES ('pear', 'origin', 'somewhere');

相同的查询将返回:

+-------+
| id    |
+-------+
| mango |
| pear  |
+-------+
2 rows in set (0.00 sec)

如果我们想要将此结果限制为实体origin=MEXICO,我们将不得不添加另一个OR条件并检查sub_t.matches = 3而不是2.

SELECT    stuff.id 
FROM      stuff 
JOIN      (SELECT    COUNT(*) matches, id
           FROM      stuff
           WHERE     (`key` = 'color' AND `value` = 'yellow') OR 
                     (`key` = 'taste' AND `value` = 'sweet') OR 
                     (`key` = 'origin' AND `value` = 'MEXICO')
           GROUP BY  id
          ) sub_t ON (sub_t.matches = 3 AND sub_t.id = stuff.id)
GROUP BY  stuff.id;

结果:

+-------+
| id    |
+-------+
| mango |
+-------+
1 row in set (0.00 sec)

与每种方法一样,使用EAV模型时存在某些优点和缺点.确保在应用程序的上下文中广泛研究该主题.您甚至可能想要考虑其他关系数据库,例如Cassandra,CouchDB,MongoDB,Voldemort,HBase,SimpleDB或其他键值存储.