表上的多维搜索

Question

我有下表存储系统中用户的首选项，

UserId | Product   | Brand     | City      |
-------------------------------------------
A      | Soap      | Tide      | NYC       |
A      | Cereal    | Dont-care | NYC       |
B      | Dont-Care | Tide      | Dont-care |
C      | Shampoo   | Dont-care | Dont-Care |

我想根据用户提供的搜索值进行搜索。因此，如果一个人搜索

City: NYC, Brand: Tide

输出应该是：

    A    | Soap      | Tide      | NYC       |
    B    | Dont-Care | Tide      | Dont-care |

就好像他们搜索

Brand: Tide, Product: Soap

结果应该是：

    A    | Soap      | Tide      | NYC       |

当前的解决方案是针对 MySQL 表的以下查询（其中 null 代表“不关心”）：

select *
from user_preferences
where (product is null or product = <user provided value>)
and (brand is null or brand = <user provided value>)
and (city is null or city = <user provided value>)

我 按预期工作，[和+（或）]组合让我认为这不是正确的方法。我也相当确定，一旦数据集增加，查询将无法很好地执行。

存储和检索此类数据的最有效方法是什么？是否有任何 no-sql 类型的方法可以用来提高效率？

更新

经过一番谷歌搜索后，我认为我的方法可能是最安全的选择。我对这种方法仍然感到矛盾的一个因素是添加另一个“可搜索”属性意味着添加一个新列。

这篇关于 EAV 反模式的博客提供了一些关于此类的很好的阅读材料一个计划。另请参阅 friend-feed 如何使用 MySQL 来了解存储变量属性的另一种方式在一个表中。

Answer 1

您可以使用 IFNULL() 函数。

它会是这样的：

select *
from user_preferences
where ifnull(product, <user provided value>) = <user provided value>
and ifnull(brand, <user provided value>) = <user provided value>
and ifnull(city, <user provided value>) = <user provided value>

Answer 2

基于@Pompom6784的回答 -

<块引用>

但逻辑上却有很大不同
条件-

select * from user_preferences 
where product = nvl(<user provided value> , product)
and brand = nvl(<user provided value> ,brand)
and city = nvl(<user provided value> ,city)

我是一个 oracle pl sql 开发人员，所以请检查上面的查询是否有 oracle sql 的语法。
如果在您的数据库中 ifnull 是检查 null 值的函数，请将 nvl() 替换为 ifnull()

@Pompom6784 您必须应用 ifnull上<用户提供的价值>不在列值上..

Answer 3

既然您问如何存储此类数据，那么我可能会这样做：

            USERS
            userid  PK
            name
            etc


            PREFTYPES
            preftypeid  PK
            prefname



           PREFTYPEVALUES
           preftypeid  foreign key references PREFTYPES(preftypeid)
           value

           composite primary key (preftypeid, value)

上面的 PREFTYPEVALUES 表允许您限制 USERPREFS 表中可能的值条目：

            USERPREFS
            userid      foreign key references USERS(userid)
            preftypeid  
            value

           => composite  foreign key(preftypeid, value) references PREFTYPEVALUES(preftypeid, value)

要合并两组用户，第一组是那些喜欢 Tide 的用户他们的肥皂和第二组是那些更喜欢纽约市作为其城市的人，您可以将这两个组联合起来：

             select U.userid, U.name, UP.value
             from USERS U 
             inner join USERPREFS UP on U.userid = UP.userid
             where 
             (UP.preftypeid = 'soap' and UP.value = 'Tide' )


              UNION

             select U.userid, U.name, UP.value
             from USERS U 
             inner join USERPREFS UP on U.userid = UP.userid
             where                  
             (UP.preftypeid = 'city' and UP.value = 'NYC')

但当然，这种结构也允许不使用联合的查询。

             select U.userid, U.name, UP.value
             from USERS U 
             inner join USERPREFS UP on U.userid = UP.userid
             where 
             (UP.preftypeid = 'soap' and UP.value = 'Tide' )
              OR
             (UP.preftypeid = 'city' and UP.value = 'NYC')

我打赌您的下一个问题将是：如何为每个用户获取一行，并并排显示用户的首选项？

              user-A, Tide, NYC

这种非规范化最好留给表示层，尽管可以使用各种不优雅的方法在 SQL 中完成。这里的 SQL 键是复合的 (user,preftype)。