mysql - 具有动态输入的 WHERE 语句

Question

我有两张 table 。第一个(项目)是列出公寓。第二个(功能)是公寓可能具有的功能列表。目前我们列出了大约 25 种不同的功能。

由于每间公寓都可以有一组不同的功能，因此我认为项目和功能表之间建立 1:1 的关系是有意义的。

如果某个功能的功能表中的值为“1”，则表示链接的公寓具有此功能。

+-------------+------------+--------------+-------------+------------+
| table: item |            |              |             |            |
+-------------+------------+--------------+-------------+------------+
| id          | created_by | titel        | description | address    |
+-------------+------------+--------------+-------------+------------+
| 10          | user.id    | Nice Flat    | text        | address.id |
+-------------+------------+--------------+-------------+------------+
| 20          | user.id    | Another Flat | text        | address.id |
+-------------+------------+--------------+-------------+------------+
| 30          | user.id    | Bungalow     | text        | address.id |
+-------------+------------+--------------+-------------+------------+
| 40          | user.id    | Apartment    | text        | address.id |
+-------------+------------+--------------+-------------+------------+

+----------------+---------+--------------+----------------+--------------+------+
| table: feature |         |              |                |              |      |
+----------------+---------+--------------+----------------+--------------+------+
| id             | item_id | key_provided | security_alarm | water_supply | lift |
+----------------+---------+--------------+----------------+--------------+------+
| 1              | 10      | 1            | 0              | 0            | 1    |
+----------------+---------+--------------+----------------+--------------+------+
| 2              | 20      | 0            | 1              | 1            | 0    |
+----------------+---------+--------------+----------------+--------------+------+
| 3              | 30      | 1            | 1              | 0            | 1    |
+----------------+---------+--------------+----------------+--------------+------+
| 4              | 40      | 1            | 1              | 1            | 1    |
+----------------+---------+--------------+----------------+--------------+------+

我想构建一个过滤功能，以便用户可以选择仅显示具有某些功能的公寓。例如:

$key_provided = 1;
$security_alarm = 1;
$water_supply = 0;

这种数据库方法对您来说听起来合理吗？

构建 MySQL 查询以仅检索过滤条件匹配的公寓的最佳方法是什么？请记住， future 功能的数量可能会增加？

Answer 1

更好的方法是有一个features表。就你而言，它们似乎都是二元的——是或否——所以你可以逃脱:

create table item_features (
    item_feature_id int auto_increment primary key,
    item_id int not null,
    feature varchar(255)
    foreign key item_id references items(item_id)
);

数据将具有积极特征，因此第一项将是:

insert into item_features (item_id, feature)
    values (1, 'key_provided'), (1, 'lift');

这使得管理功能变得容易，特别是添加新功能。您可能想使用触发器、检查约束或引用表来验证功能名称本身，但我不想偏离您的问题太远。

然后检查功能有点复杂，但也没有那么复杂。一种方法是针对每个所需/不需要的显式使用 exists 和 not issues:

select i.*
from items i
where exists (select 1
              from item_features itf
              where itf.item_id = i.item_id and
                    itf.feature = 'key_provided'
             ) and
      exists (select 1
              from item_features itf
              where itf.item_id = i.item_id and
                    itf.feature = 'security_alarm'
             ) and
      not exists (select 1
                  from item_features itf
                  where itf.item_id = i.item_id and
                        itf.feature = 'water supply'
                 );