mysql - MySQL 嵌套 select 的更好解决方案

Question

目前我有两个 MySQL 表

属性

id    name 
1     Grove house
2     howard house
3     sunny side

高级选项

prop_id    name
1          Wifi
1          Enclosed garden
1          Swimming pool
2          Swimming pool

如您所见，表二包含有关属性的特定功能

当我只有最多 3 个选项时，下面的查询工作得很好。 (也许有点慢，但还好)现在事情已经有所扩展，我最多有 12 个可以搜索的选项，这给我带来了一些主要的速度问题。下面的查询有 8 个选项，您可以看到它非常困惑。有没有更好的方法来实现我想要实现的目标？

SELECT * FROM properties WHERE id in (
select prop_id from advanced_options where name = 'Within 2 miles of sea or river' and prop_id in ( 
    select prop_id from advanced_options where name = 'WiFi' and prop_id in ( 
        select prop_id from advanced_options where name = 'Walking distance to pub' and prop_id in ( 
            select prop_id from advanced_options where name = 'Swimming pool' and prop_id in ( 
                select prop_id from advanced_options where name = 'Sea or River views' and prop_id in ( 
                    select prop_id from advanced_options where name = 'Pet friendly' and prop_id in ( 
                        select prop_id from advanced_options where name = 'Open fire, wood burning stove or a real flame fire-place' and prop_id in ( 
                            select prop_id from advanced_options where name='Off road parking') 
                        ) 
                    ) 
                ) 
            ) 
        ) 
    ) 
)

Answer 1

就像迈克·布兰特(Mike Brant)建议的那样，我会考虑将您的数据模型更改为设置的限制，并为您的 properties 表中的每个属性创建一个列。但有时老板会来:“我们还需要‘平板电视’”，然后你必须返回数据库并更新方案和数据访问层。

如果数据库使用按位比较，则可以以某种方式移出此逻辑。这允许您进行简单的查询，但在进行查询之前需要进行一些预处理。

你自己判断。

我已将所有内容放入测试套件中 sqlfiddle

基本思想是表中的每个属性都有一个 2 的幂的 id。如下所示:

INSERT INTO `advanced_options` (id, name)
VALUES
 (1, 'Wifi'),
 (2, 'Enclosing Garden'),
 (8, 'Swimming Pool'),
 (16, 'Grill');

然后，您可以在属性表中存储单个值并添加选项:

Wifi + Swimming Pool = 1 + 8 = 9

如果您想查找所有有 wifi 和游泳池的特性，您可以这样做:

SELECT * FROM `properties` WHERE `advanced_options` & 9 = 9

如果您只想要游泳池，那就是:

SELECT * FROM `properties` WHERE `advanced_options` & 8 = 8

去试试 fiddle