mysql - MySQL数据库设计建议-使用联接

我正在建立一个类似于AJAX的搜索页面，该页面允许客户选择一个数字过滤器以缩小搜索范围。例如，用户选择了“ iPhone 5”，并具有用于容量（32GB，64GB）和颜色（黑色，白色..）的其他过滤器。

用户只能为每个类别选择一个单选框（因此他们可以选择32GB和黑色）。但是他们不能选择（32GB，64GB和黑色，因为其中两个属于“容量”类别）。

我在sqlfiddle上的这里添加了架构（请忽略以下事实：我已经删除了它们在适当的应用程序中存在的主键，它们刚刚与其他字段/数据一起被删除，以最小化sqlfiddle）

http://sqlfiddle.com/#!2/964425

任何人都可以建议创建查询的最佳方法来执行以下操作：

Get all the prices for device_id '2939' (iPhone 5) which has the 'attributes' of '32GB' AND 'Black'

我目前有这个-但这仅在选择单个属性时有效：

// search for device with '64GB' & 'Black' attributes (this currently doesn't return any rows)
SELECT `prices`.*
FROM (`prices`)
LEFT JOIN `prices_attributes` ON `prices_attributes`.`price_id` = `prices`.`id`
WHERE `prices`.`device_id` =  '2939'
AND `attribute_option_id` =  '19'
AND `attribute_option_id` =  '47';

// search for device with '64GB' attribute only (this currently DOES return a row)
SELECT `prices`.*
FROM (`prices`)
LEFT JOIN `prices_attributes` ON `prices_attributes`.`price_id` = `prices`.`id`
WHERE `prices`.`device_id` =  '2939'
AND `attribute_option_id` =  '19';

关于数据库设计的任何建议也将不胜感激

注意：我当时想在'prices'表中有一个新列并序列化匹配的attribute_ids-但是这对优化没有好处（例如会比当前方法慢）

最佳答案

由于attribute_option_id是原子值，因此同一行不能有两个不同的值。因此，您的WHERE子句不能匹配任何记录：

SELECT `prices`.*
FROM (`prices`)
LEFT JOIN `prices_attributes` ON `prices_attributes`.`price_id` = `prices`.`id`
WHERE `prices`.`device_id` =  '2939'
AND `attribute_option_id` =  '19'   # Here for one row, attribute_option_id is either 19
AND `attribute_option_id` =  '47';  # of '47'. Cannot be the both

如果您觉得可读性更高，可以尝试使用子查询来代替JOIN。我认为MySQL允许该语法：

SELECT `prices`.*
FROM `prices`
WHERE `prices`.`device_id` =  '2939'
AND EXISTS (SELECT *
               FROM prices_attributes
               WHERE price_id = `prices`.`id`
               AND attribute_option_id IN ('19', '47') )

我不知道MySQL如何优化上述解决方案。一种替代方法是：

SELECT `prices`.*
FROM `prices`
WHERE `prices`.`id` IN (
   SELECT DISTINCT `price_id`
       FROM prices_attributes
       WHERE attribute_option_id IN ('19', '47')
)